Gaps in Database ID sequence – one reason why this can happen

I spoke with a DBA today about the following scenario… When running DBCC CHECKDB in SQL Server 2005, he noticed that there were no entries for the hidden database snapshot in sys.databases; however he did see that a database_id value was shown when querying sys.dm_exec_requests (for the request running the DBCC command - WHERE command = 'DBCC TABLE CHECK'). For that request, the database_id had a value of “9” – which was N+1 of the maximum database value (where N = the maximum database_id in sys.databases).

His question to me then was – what happens if he creates a new database while the DBCC CHECKDB is still executing? Will it try to use the hidden value of “9”? Will it error out? Will it use a higher number?

I ran a quick test against AdventureWorks2008 and confirmed that indeed – the hidden snapshot database is not visible in sys.databases. I also confirmed that sys.dm_exec_requests shows a higher value for database_id based on the maximum database_id currently in use (so N+1).

Lastly, I created a new database concurrently with a DBCC CHECKDB, and sure enough – the resulting database_id was N+2 (or N+1 if you count the hidden snapshot). Specifically, my maximum database id was for AdventureWorks2008, and had a database_id value of “8”. A value of “9” was used for the DBCC CHECKDB hidden snapshot. A value of “10” was then used for my new database.

So if you ever see gaps in the sequence of database ids, this is one reason why this could happen. As a side note – he also asked if this could cause problems if an application depended on a specific database id. My reply was that an application should not be written to depend on a specific id. Such hard-coding and dependencies will surely cause issues down the road (for example, if you ever plan on migrating your database to a new SQL Server instance with a differing number of databases and IDs).