I walked into the office this morning and got asked a strange question: “Does a database you detach and re-attach have the same database id before and after the detach?”. While it may appear that it does in a simple test, it actually doesn’t – it just takes the next available id. If you detach database id 5 and then re-attach it, it will retake id 5, and this will mostly work – unless other databases have been removed from the server at some point in the past and there are ‘holes’ in the list of available id’s. So…”How do you force a database to have a specific database id?”
First of all, you can get the current database_ID of all your databases by running:
select name, database_id from sys.databases
What you will likely notice is that the numbers go from 1(master) to roughly the number of databases on your instance. SQL Server assigns the next available database id. What that means is that if you have a clean install database id’s 1-4 will be taken by the system databases and the first database id you create will be 5 and the second 6 and so on. The exception is when you delete\detach a database – this creates a hole which is the next assigned number. So if you have created databases with ID’s 5-10 and detach the database with id 7, the next database you attach or create will take database_id 7.
The upshot of all this is that if you ever want to assign a specific database id to a new database you need to do the following:
- If all your database id’s are sequential and the next available number is the id you want – just create the database.
- If all your database_id’s are sequential and the database_id is already assigned, detach the database that is currently using it, create the database you want to take that ID and then re-attach the database you dropped.
- If you want to assign a number that is greater than the currently available database_ids, simply create databases until the next available id is the number you are after, then create the database you want to take that id, then remove all the filler databases you created to get there.
I’m still unsure what the usecase for this is – but now you know how to do it.
Hi!
Good stuff!!
I got a use case for you 🙂 Making sure the database with heaviest Availability Group redo workload gets parallel redo rather than serial redo in SQL Server 2016++.
“When the host server has 32 or more CPU cores, each database will occupy 16 parallel redo worker threads and one helper worker thread. It means that all databases starting with the 7th database (ordered by database id ascending) that has joined availability group it will be in single thread redo or serial redo irrespective which database has actual redo workload. If a SQL Server Instance has a number of databases, and it is desired for a particular database to run under parallel redo model, the database creation order needs to be considered.”
SQL Server 2016/2017: Availability group secondary replica redo model and performance
https://blogs.msdn.microsoft.com/sql_server_team/sql-server-20162017-availability-group-secondary-replica-redo-model-and-performance/
I’m looking at such a case now, trying to figure out step by step the most efficient maintenance window plan to force the “lighter load” databases to have a higher database ID than the heaviest load database in order to get parallel redo for heaviest load database 🙂
A use case for this is when using query store in an AG. Forced plans will not persist from the primary to the secondary if their database ids don’t match after failover. Good post. Thanks for sharing.