Post reply

SQL SERVER index rebuild job fails periodically at update stats.

  • March 18, 2015 at 8:32 am

    #295263

    All,

    I have a job that runs nightly, rebuild index. job runs fine every night but every few weeks it fails. please see the job below.

    USE msdb

    GO

    EXECUTE dbo.IndexOptimize

    @databases = 'USER_DATABASES',

    @FragmentationLow = NULL,

    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

    @FragmentationLevel1 = 5,

    @FragmentationLevel2 = 30,

    @SortInTempdb = 'Y',

    @UpdateStatistics = 'ALL',

    @OnlyModifiedStatistics = 'Y'

    This calls the Sp that does the Reindex. It fails at the update statistics with a very generic message. like " Command: UPDATE STATISTICS [xxxx_DB].[dbo].[xxxx_xxx] [_WA_Sys_00000007_49C3F6B7] [SQ... The step failed."

    I suspect it has more error but this is all it is showing me when I right click on the job history. therefore, I updated the job step in the advance tab with log to a txt file. Am I on the right track or there is another way to see error some where else.

    I looked at the logs but they didn't show any thing.

    Many Thanks.

    B

  • March 18, 2015 at 8:37 am

    #1784792

    qur7 (3/18/2015)

    I updated the job step in the advance tab with log to a txt file.

    Yup, that should do it. Run the job again, if it fails look in the text file and the entire message should be there.

    (emphasis 'should')

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 18, 2015 at 8:48 am

    #1784797

    Ola Hallengren is very responsive to emails if you find an error message in your text output file that could be a bug or opportunity for improvement in his maintenance suite.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • March 18, 2015 at 9:02 am

    #1784803

    Gail,

    Thanks for the quick response, would like to make sure if I followed it correctly. the job will run tonight and will let you know how the logging went.

    To update the logging

    open the job.

    click on the step.

    click on the advance tab.

    click on the browse tab next to output file. pick a drive and folder, pick the name of the file "indexrebuildlogs" without any extension. clicked on the append to the existing file ( this writes all the failures of the job on the same file - I suppose).

    Click ok.

    Please advice.

    B

  • March 18, 2015 at 9:32 am

    #1784810

    Thanks.

    will contact Ola, once i get the failure in the txt file.

  • March 18, 2015 at 11:15 am

    #1784853

    qur7 (3/18/2015)

    Thanks.

    will contact Ola, once i get the failure in the txt file.

    Don't contact him first. Try to resolve the issue yourself first. Most likely it is something you can address.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • March 19, 2015 at 12:45 pm

    #1785197

    All,

    Just thought to give every one a update. the job ran successfully tonight and the log was written to the file. I am going to wait for the next failed to see what the issue is and hopefully can figure it out.

    Thanks Kevin and Gail.

    Regards,

    B

  • March 19, 2015 at 10:56 pm

    #1785269

    Maybe your log drive filled. Did you check the ERRORLOG?

  • March 20, 2015 at 7:24 am

    #1785337

    Just an update, the job history log is been written on the logfile, the check mark " append to the existing file" on the advance tab on job step property, writes nightly job run log in the same file.

    I will wait for the next failed when it will happen.Ii have checked it, the database log file was not full, nothing in the error logs either.

  • March 24, 2015 at 12:00 pm

    #1786064

    Check to make sure that you are not rebuilding indexes and updating stats within the same time period with 2 separate jobs.

    May be that you intended to do update stats during the week and by mistake also are updating stats when you do a full index rebuild on Sundays.

    In that case, the update stats will be chosen as the deadlock victim if the update stats is contending with an index rebuild for the same index.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • March 24, 2015 at 1:45 pm

    #1786091

    What edition of SQL server are you using?

    I run the same job on a standard edition using Ola's scripts and the job often fails but because I'm rebuilding offline and with a lower priority for the rebuild process. I don't mind it because I've increased the retry attempts and on the second try it passes successfully.

    Try retry.

    Igor Micev,My blog: www.igormicev.com

  • March 31, 2015 at 7:05 am

    #1787683

    Hi All,

    so after few days, the job failed again and since I was logging this time I was able to find the error.

    Date and time: 2015-03-31 02:08:21 Command: ALTER INDEX [xxxxxxxxx] ON [databasename].[dbo].[xxxx_xxxxxx] REORGANIZE WITH (LOB_COMPACTION = ON) Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: Yes, PageCount: 3639, Fragmentation: 5.41358 [SQLSTATE 01000]

    Msg 50000, Sev 16, State 1, Line 153 : Msg 1205, Transaction (Process ID 125) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 42000]

    Outcome: Failed Duration: 00:03:33 Date and time: 2015-03-31 02:11:54 [SQLSTATE 01000]

    so I know its is get chosen as a victim of deadlock. how Can I avoid it. don't really know what was the other process that was involved in the deadlock.

    how can I resolve it.. if I change the timing a little bit will this help?

    Thanks in advance

  • March 31, 2015 at 7:06 am

    #1787686

    When I will do retry, will it start the whole process again , or just starts form the failed index?

    With Regards,

    Ahmed

  • March 31, 2015 at 7:17 am

    #1787694

    Refer following Microsoft link which will help you to narrow down your deadlock issue:

    https://technet.microsoft.com/en-us/library/ms178104%28v=sql.105%29.aspx

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • March 31, 2015 at 7:40 am

    #1787717

    Ok , so what I did to resolved the issue, not really sure it will work .. but will monitor it. at 2:11 when this deadlock occur, there was a app connection from 10:00 pm with last batch on 2:11 that was blocking this alter job.

    so I changed the job to to 2:30. and hopefully the app connection will be finished by than .

    Regards,

    Note - any other input is welcome too.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply