2

I have a testing environment where I have scheduled jobs running restore tests as well as DBCC checks. These jobs run to:
1) copy database back up from the prod.
2) DROP all existing databases except dba_utility
3) restore the backup
4) run dbcc checkdb

I frequently get:

Time-out occurred while waiting for buffer latch type 2 for page (1:3564879), database ID 7.

The weird thing is, there is no database with an Id of 7.
Maximum Id in sys.databases catalog view is 6. (Remember, the job DROP all databases before the restore operation except dba_utility whose Id=5. The newly restored database is assigned the Id of 6.)

Prod environment is 2008 R2, test environment is 2012 SP1.

What might be causing this?

5

run dbcc checkdb

CHECKDB creates a database snapshot behind the scenes. This is your DBID 7.

As for the timeout, perhaps KB919155 Error message when you run the DBCC CHECKDB statement on a database that contains one or more very large tables in SQL Server: "Timeout occurred while waiting for latch". "This behavior is by design.". Is page 3564879 an IAM chain page?

  • Thanks. +1 for db snapshot Id. How can I know if page 3564879 is an IAM chain page? – Stackoverflowuser Oct 13 '15 at 12:05
  • See the link in the response we can see that there’s an IAM page: a page with type 10 – Remus Rusanu Oct 13 '15 at 12:06
  • No. It is not a IAM chain page. It is m_type = 16 which corresponds to diff map page. – Stackoverflowuser Oct 13 '15 at 12:21
  • 1
    Best avenue is to contact CSS – Remus Rusanu Oct 13 '15 at 12:43
  • If I change the recovery model of the db to 'Simple' and run checkdb again, I get the same error message but this time for a different page (1:3944) which is of type 2 (index page). – Stackoverflowuser Oct 13 '15 at 14:40
2

The problem seems to be resolved after enabling Instant File Initialization

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.