My Cube Processing - Optimize Databases is failing

Question

Hi,

The Optimize Databases job runs and fails every night at 1 AM. All I can see from the report is that is fails. 

• How do I find out more information on what the error/problem is?

 

 

---

Bob Hardister

Answer 1

Hi Bob,

This job is uesd to optinize warehouse. Where did you see the reports? Could you find errors in EventLog?

---

Best regards,
Ruiz
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

Answer 2

Hi Ruiz,

It is the Cube Status.rdl report from Grant Holiday's Administrative Report Pack for Team Foundation Server 2010 (http://blogs.msdn.com/b/granth/archive/2010/07/12/administrative-report-pack-for-team-foundation-server-2010.aspx?wa=wsignin1.0)

I cannot see any error related to the failure of the Optimize Databases job in the event log.  I don't know where the "job" itself is.

 

---

Bob Hardister

Answer 3

Hi,

I think this is an issue about the report itself.

The report has a function

Public Function ProcessingType(ByVal result As Integer, ByVal resultMessage As String) As String
        If result = 8 Then
            Return "Skipped"
        ElseIf result = 2 Then
            Return "Error"
        End If

        Try
            If String.IsNullOrEmpty(resultMessage) Then
                Return "Unknown"
            End If
            Dim parts As String() = resultMessage.Split(New String() {"--->"}, StringSplitOptions.None)
            If parts.Length < 3 Then
                Return "Error"
            End If
            If parts(2).Contains("Exception") Then
                Return "Error"
            End If

            Dim typeParts As String() = parts(1).Split(",")
            Dim results As String() = typeParts(0).Split("=")

            ProcessingType = results(1).Trim.Trim(".")
        Catch ex As Exception
            Return "Report Error"
        End Try
    End Function

The result Message of Optimize Databases  is always [Optimize Databases]:, so this function will return "Error".

You can run following query in your Data Tier to see the Result Message

SELECT * FROM
(
 select h.StartTime, h.EndTime, h.Result, h.ResultMessage,
  DATEDIFF(s, h.StartTime, h.EndTime) as Seconds,
   HistoryId, JobName
   from tbl_JobHistory h inner join tbl_JobDefinition d on d.JobId = h.JobId
   where h.StartTime >= '20100701'
   and h.StartTime < '20100702'
   and d.JobName in ('Incremental Analysis Database Sync', 'Full Analysis Database Sync', 'Optimize Databases')

 UNION

 SELECT  ExecutionStartTime as StartTime,
   GETUTCDATE() AS EndTime,
   0 AS Result,
   d.JobName AS ResultMessage,
   DATEDIFF(SECOND, QueueTime, GETUTCDATE()) AS Seconds,
   0 AS HistoryID,
   'Current' AS JobName
 FROM    tbl_jobqueue q
 JOIN  tbl_JobDefinition d ON d.JobId=q.JobId
 LEFT JOIN tbl_ServiceHostProcess shp ON q.AgentId=shp.ProcessId
 WHERE JobState = 1
   AND d.JobName IN ('Full Analysis Database Sync', 'Incremental Analysis Database Sync', 'Optimize Databases')
) a
ORDER BY a.StartTime

Please contact the author for more detail.

 

---

Best regards,
Ruiz
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

Answer 4

So what is the resoluition to this issue?

Answer 5

I haven't had an opportunity to follow-up on Ruiz's suggestion yet. Hopefully this week.

---

Bob Hardister

Answer 6

Hi Allen and Bob,

I think we can add a IF to this function. (Sorry that I am not familiar with VB)

Public Function ProcessingType(ByVal result As Integer, ByVal resultMessage As String) As String
        If result = 8 Then
            Return "Skipped"
        ElseIf result = 2 Then
            Return "Error"
        End If

 If  resultMessage= "[Optimize Databases]:"

    Return ""

End If

        Try
            If String.IsNullOrEmpty(resultMessage) Then
                Return "Unknown"
            End If
            Dim parts As String() = resultMessage.Split(New String() {"---&gt;"}, StringSplitOptions.None)
            If parts.Length &lt; 3 Then
                Return "Error"
            End If
            If parts(2).Contains("Exception") Then
                Return "Error"
            End If

            Dim typeParts As String() = parts(1).Split(",")
            Dim results As String() = typeParts(0).Split("=")

            ProcessingType = results(1).Trim.Trim(".")
        Catch ex As Exception
            Return "Report Error"
        End Try
    End Function

---

Best regards,
Ruiz
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

Answer 7

Ruiz is correct: it is an issue with the report, I have the fix to include the job along with Cube processing jobs.

I will post the updated version of reports shortly.

 

Thanks,

Elena

Answer 8

Looking forward to the new/fixed reports! Thanks. We were thinking of calling PSS to get this resolved.

Answer 9

Are the updaed reports available?

Answer 10

The download link has been updated with a fixed report that includes the 'Optimize Databases' job.  Please follow the link from the original post: http://blogs.msdn.com/b/granth/archive/2010/07/12/administrative-report-pack-for-team-foundation-server-2010.aspx

 

 

Answer 11

Thanks Grant! All looks good now.

---

Bob Hardister