posts from database land

 

 

 

 

 

 

Today’s installment of #TSQL2sday is brought to you by Chris Yates (b/t); the subject is ‘Something New Learned’.

My first #TSQL2sday!

Our production environment includes a couple SQL Server 2008 R2 n+1 clusters, to which we recently applied SP3. The upgrade failed on one of the instances due to a primary key violation. My coworker uninstalled the patch, restored msdb from backup, and was able to get the production instance running again (and some much-needed sleep).

When I arrived to work in the morning, I resolved to have a look.

I found the error in the SQL Server log: a pk violation against dbo.syscategories. When I compared the syscategories data on this instance against other instances in the cluster, I found one the category names in the 1 – 99 range on the other instances matched one of the categories above 99 matched one of the names above 99 on this instance. I then checked the script the patch failed on. The patch script first delets all categories having a category_id < 100, then re-adds them, leaving everything >= 100 as-is; the primary key is category_type and name. SQL Server expects that all records having category_id <= 99 are system created, and that no one will ever alter one.

To recover, I first verified the user-added category was not used. I then setup a test environment using a restored copy of original msdb from the problem-instance. I then used sp_delete_category to remove the same-named category, and verified that SP3 installed correctly.

I compared the Categories data shown in the GUI to the data in the table; the value causing the pk violation showed up twice in the GUI. Microsoft uses constants to represent system-entered values.

As a result of this, we altered our process to always take a backup of the system databases before applying a patch. We were lucky; the backup had run automatically not long before the maintenance window started. Not really something new; more a reminder to follow best-practice :)