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?

Improve this question
1
  • Please make sure both SQL Server versions are patched to Latest Service pack Commented Oct 13, 2015 at 13:44

2 Answers 2

Reset to default
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?

Improve this answer
6
  • Thanks. +1 for db snapshot Id. How can I know if page 3564879 is an IAM chain page? Commented Oct 13, 2015 at 12:05
  • See the link in the response we can see that there’s an IAM page: a page with type 10 Commented Oct 13, 2015 at 12:06
  • No. It is not a IAM chain page. It is m_type = 16 which corresponds to diff map page. Commented Oct 13, 2015 at 12:21
  • 1
    Best avenue is to contact CSS Commented Oct 13, 2015 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). Commented Oct 13, 2015 at 14:40
2

The problem seems to be resolved after enabling Instant File Initialization

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.