none
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
    Tuesday, July 13, 2010 9:28 PM

Answers

All replies

  • 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
    Wednesday, July 14, 2010 4:06 AM
    Moderator
  • 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
    Wednesday, July 14, 2010 1:11 PM
  • 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
    Friday, July 16, 2010 9:59 AM
    Moderator
  • So what is the resoluition to this issue?
    Thursday, July 22, 2010 2:22 PM
  • I haven't had an opportunity to follow-up on Ruiz's suggestion yet. Hopefully this week.
    Bob Hardister
    Thursday, July 22, 2010 2:48 PM
  • 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
    Friday, July 23, 2010 10:02 AM
    Moderator
  • 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

    Friday, July 23, 2010 2:30 PM
  • Looking forward to the new/fixed reports! Thanks. We were thinking of calling PSS to get this resolved.
    Monday, July 26, 2010 4:41 PM
  • Are the updaed reports available?
    Thursday, July 29, 2010 8:48 PM
  • 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

     

     

    Friday, July 30, 2010 4:19 PM
  • Thanks Grant! All looks good now.
    Bob Hardister
    Friday, July 30, 2010 4:41 PM