Post reply

Maintenance problem

  • November 23, 2004 at 6:38 am

    #161946

    Hi all.

     

    I have a wierd problem.

    I have maintenance plan that take normally 1 minute 18 second to run on a small database. The maintenance plan start at 4:00am and after that, I have a Full database backup at 5:30am and log backup at every hours, except between 3:59am and 5:00am.

    My maintenance plan consist to Rebuilt the index and check database integrity. The rebuilt index start at 4:00am and integrity at 4:30am.

    But today, the job that rebuilt my index in the maintenance plan takes 3 hours to run. Why ?

    So my transaction log backup failed and my full database backup ran 3 hours later.

    Here is the error message in my maintenance plan log:

    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3023: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed.

    [Microsoft][ODBC SQL Server Driver][SQL Server]Index (ID = 1) is being rebuilt.

    I consider this error message normal due to the transaction log backup over my maintenance plan.

    Do you have some idea why, today, the rebuilt takes 3 hours when normaly it takes 1 minute.

    Thanks.

     

    PS: My manager saw that two lock exists on the server before I came to work.

  • November 23, 2004 at 7:38 am

    #531213

    They error message is pretty self-describing. Index ID=1 means the clustered index is rebuild. That in turn could mean that all non-clustered indexes are also internally rebuild. Depending on how many row there are in the table, this might take a moment.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 24, 2004 at 4:13 pm

    #531486

    Thanks for the reply.

     

    Is it possible that a lock on a table or a row by a user cause the difference between 1 minute to rebuilt and 3 hours.

    Because 3 hours later, the user come back at the office and killed the application that stop responding. Before the user kill his application a lock exists under his connection on SQL Server.

    That mean that when the user release the lock by killing his application, SQL Server be able to rebuilt the index on that table.

    I'm right or wrong ?

     

Viewing 3 posts - 1 through 2 (of 2 total)

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