Post reply

Latch time out in SQL Server 2012

  • April 24, 2015 at 2:50 am

    #398369

    Morning everyone,

    For the last couple of days my index optimise job has been failing, the error reported in the log is:-

    A time-out occurred while waiting for buffer latch -- type 4, bp 0000001CDF432100, page 4:491294, stat 0x9, database id: 30, allocation unit Id: 72057594198753280, task 0x00000000023ABC38 : 4, waittime 300 seconds, flags 0x1000001018, owning task 0x0000000000000000. Not continuing to wait.

    DBCC CHECKDB reports no issues in the database.

    I cannot find any record for that page on file_id 4 in sys.dm_db_database_page_allocations.

    DBCC PAGE (running with option 1 as option 3 reports invalid metadata) reports:-

    Has anyone else encountered issues with unallocated pages (if that is what this is) causing issues with index reorgs/rebuilds?

    Or am I barking up the wrong tree?

    Any help or advice would be most appreciated, thank you.

    Andrew

  • April 24, 2015 at 7:42 am

    #1793262

    Is your instance up to date service pack wise? Not the same conditions but similar error described in the following url

    https://support.microsoft.com/en-us/kb/2545989

  • April 24, 2015 at 8:23 am

    #1793286

    Sorry I should have said that the instance is SQL Server 2012 SP2 CU2.

    The link you send is the issue but only applies to SQL Server 2008 (R2).

  • April 24, 2015 at 9:13 am

    #1793308

    Is it always the same index? is it reorg or rebuild?

  • April 24, 2015 at 9:15 am

    #1793309

    It's always the same error, reporting the same page. The job is failing on the same index, trying to do a reorg.

  • April 24, 2015 at 9:27 am

    #1793312

    have you tried a rebuild on that particular index? is it clustered or non clustered? what edition of SQL is it?

  • April 24, 2015 at 9:30 am

    #1793314

    It's a nonclustered index. Haven't tried a rebuild yet (I'm restoring a copy on my development instance to test).

    Would a rebuild help? The page in question is no longer allocated.

  • April 24, 2015 at 9:32 am

    #1793315

    definitely worth a go with the rebuild, trial and error really!

  • April 24, 2015 at 10:52 am

    #1793350

    Running the rebuild now on my dev instance. Hopefully I'll be able to replicate the issue

  • April 25, 2015 at 2:55 am

    #1793457

    I'm not able to replicate on my dev instance and the job has again failed today with the same error even though I excluded the database.

    I tried to drop and recreate the index but it failed with a warning of:-

    A time-out occurred while waiting for buffer latch -- type 4, bp 0000001D0FD512C0, page 4:513288, stat 0x9, database id: 30, allocation unit Id: 72057594198753280, task 0x00000000023B4CF8 : 4, waittime 300 seconds, flags 0x1000001018, owning task 0x0000000000000000. Not continuing to wait.

    Same warning but this time a different page. I re-ran the create index statement and it succeeded.

    I'm running a CHECKDB now but it'll probably come back fine.

  • April 27, 2015 at 2:04 am

    #1793584

    I dropped and re-created the index on Saturday but excluded the database from any index maintenance until today. Have just re-tried running the maintenance job but had to stop it as it was causing a lot of blocking (and would have eventually failed with the same error would be my guess).

    Interestingly the job started blocking on a different index.

  • April 30, 2015 at 9:56 am

    #1794763

    Further update on this (if anyone's watching it).

    I added a new filegroup to the database and migrated all the nonclustered indexes to it. I was worried I'd get the same problem with the latch time-out but it all went OK. However when I went to drop the old file (the one that is causing the issues) it failed due to a latch time-out, even though the file is now empty.

    Next stage will be to failover the instance (it's clustered) and then retry the drop.

Viewing 12 posts - 1 through 11 (of 11 total)

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