The Multifunctioning DBA

Sep 24 2012   3:14PM GMT

Bug more detail

Colin Smith Colin Smith Profile: Colin Smith

I wanted to shed a bit more light on the bug that I talked about it my last post. So this is something that should really never happen. When you add a data file to SQL Server, it gets a file_id and that id is stored in sys.master_files. So if you add a bunch of data files to a database, you might have id’s 1 -primary, 2 – log, 3 – secondary, 4- secondary, 5 – secondary. This is the situation that ┬ámy database was in at some point. Like shown below.

SQL to create the database, add the files, and look at the files in sys.master_files

SQL to create the database, add the files, and look at the files in sys.master_files

 

 

 

 

 

 

 

 

 

 

sys.master_device results

sys.master_device results

 

 

 

 

 

 

So you can see that I have file_id 1..5 making up my database. But now if I drop file ID 4. That will leave a gap.

Results after dropping file_id4

Results after dropping file_id4

 

 

 

 

 

 

This ID also has an entry in the backup file, so when you backup and restore the database, the file_id’s will match up. So the issue was that the virtualization software just assumes that the files will be sequential and no gaps will be in the order. Since this was not the case the VDB was not able to come online. In my last post, I talked about a hacky way to resolve that by modifying sys.master_files. (NOT RECOMMENDED).

The other way is to add as many data files back to the source database as you have gaps in your ID sequence. SQL Server will give these files the ID to fill in the gap. Show below.

Results after adding file file_id6

Results after adding file file_id6

 

 

Notice that the file_id 4 is back but the file name is actually file_id6. So once you add these files now you will not run into the bug.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: