The Multifunctioning DBA

Sep 20 2012   8:32PM GMT

Virtulization BUG

Colin Smith Colin Smith Profile: Colin Smith

So in my testing of the Database Virtulization product I ran into a most interesting bug that really caused me some pain to figure out. When I provisioned the VDB, the database showed up on the target instance but was in a restoring state. Checking the error log I found that the database was unable to start do to a file_id in the file not matching what SQL Server Expected due to what was in sys.master_files. The ID of one of the data files was incorrect in sys.master_files. To resolve this I had to update the sys.master_files catalog directly. I do not recommend doing this and I did it only because I knew that if something catastrophic happened… Well I just did not care about this database at all. First I went and found Paul Randal talking about how to update system catalogs. Please read and you will notice that Paul is also cautioning you against doing this.

http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Using-the-SQL-2005-Dedicated-Admin-Connection-to-fix-Msg-8992-corrupt-system-tables.aspx

Here are the steps that I took.

  1. Bring the SQL Server Instance offline.
  2. Restart the SQL Server Instance in Single user mode.
    1. Sqlsevr –m
  3. Connect to the instance using the DAC
    1. Sqlcmd /A
  4. Allow ad hoc updates to system catalogs.
    1. Sp_configure ‘allow updates’, 1
    2. Go
    3. Reconfigure with override
    4. Go
  5. Update the catalog with the correct file id
    1. Update sys.master_files
    2. Set file_id = 6
    3. Where database_id = 8
    4. And file_id = 5
  6. Shutdown the SQL Server Instance
  7. Restart the SQL Server Instance in regular mode.

And just like that my VDB was back.

 

More on the virtulization product to come.

 

And we notified the vendor and they are working on a fix as what I did just is not acceptable for a fully baked product.

 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: