question

Sandeep77 avatar image
Sandeep77 asked

Need to change the dbid of the database

HI. I have a requirement to change the dbid of the database and increase the priority of the start up of the database.
db
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks. Reason why i asked was a heated discussion i had with my colleague as he was of the view that the dbid can be changed in sybase , for which i was opposed that this is not possible in SQL thanks for the clarification.
0 Likes 0 ·
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
sjimmo avatar image
sjimmo answered
Sandeep, Why would you have to change the DBID of a database? My understanding would be to make the change at the sysdatabases table, and then you may have to restart the SQL Server in order to push the changes through the sysprocesses table. But there may be other tables involved such as sys.changes or the sys.master_files. It also goes by database_id in these tables. In other words, it would be a considerable undertaking, and I know of no tool to help here.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Additionally, what version of SQL Server are you running? Beginning with I believe 2008 you are unable to modify system tables so you will probably not be allowed to change the dbid.
0 Likes 0 ·
JohnM avatar image
JohnM answered
Altering any system tables is a bad idea for any purpose. I wouldn't recommend it without the explicit blessing of Microsoft Support. Doing so without them, IMHO, is bad mojo. One option would be move the database to another instance that have less databases thus allowing it to come online as quickly as possible. Maybe by itself if it is that critical in nature. Another option would be to tightly manage your transaction logs to ensure that any recovery is as minimal as possible as the database comes online.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

perrywhittle avatar image
perrywhittle answered
do not start modifying the system tables, you're asking for trouble. The database id is populated throughout the system. One way to shift the IDs would be as follows (try in a test system first); 1. find database that occupies ID 5 and take backup then drop it. 2. backup your database to shift then drop it 3. restore the database you want to shift to ID 5 4. restore the database that ID 5 originally which will now get a higher system ID
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That's weird. I wouldn't have expected that behaviour at all! A little bit of test code to illustrate the point: CREATE DATABASE T1 CREATE DATABASE T2 CREATE DATABASE T3 CREATE DATABASE T4 SELECT * FROM sys.databases -- shows a gap in the sequence DROP DATABASE T3 CREATE DATABASE T5 SELECT * FROM sys.databases -- shows T5 occupying that gap. (This was run in SQL 2014...)
1 Like 1 ·
I ran mine on 2012 and when creating a database it grabs the first available database id, which is exactly why a database involved in replication should not be dropped or detached as the id will get re used if another database is created\restored Regards Perry
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.