Wednesday, July 22, 2015

Remove Log Shipping When Secondary Server Not Available

The other day, I came accross a scenario where I need to remove log shipping from a database on the primary server. However, we have already shutdown/decommissioned the secondary server. If we try to disable the log shipping from the database properties window, we would most like get the following:

Database Properties

Error Deleting Log Shipping Configuration

image

We would need to remove it manually using the following T-SQL script:

USE [master]
GO

EXEC sp_delete_log_shipping_primary_secondary @primary_database = '[Database_Name]', @secondary_server = '[Secondary_Server_Name]', @secondary_database = '[Database_Name]';
GO

EXEC sp_delete_log_shipping_primary_database @database = '[Database_Name]'
GO

References:


Monday, July 20, 2015

Reflection on Steve Stedman's Database Corruption Challenge

Over the past few months, Steve Stedman (blog | twitter) has been conducting database corruption challenge. It is a challenge where Steve would post corrupt SQL Server databases and the participants would try to fix the corruption to the best of their ability. The participants would need to avoid any data loss when trying to fix the database corruption.

It has been an interesting challenge. It is a good way for us to practice dealing with different scenario of database corruptions. Each week, Steve was able to come out with different set of database corruption problems to solve. For example, in one week, we are able to recover corrupt data by simply querying the data against the non-clustered index. In another week, we would need to get the missing data from SQL Server data page.

After each challenge, Steve would post a couple of correct solutions sent by the participants. Often times, by reading those solutions, I learned different methods of resolving database corruption (or retrieving corrupt data) than the one that I have. The old saying of "There's more than one way to skin a cat" definitely holds true in many cases.

In a perfect world, we would not have any corruption in any of our database. However, we don't live in a perfect world. By working thru the database corruption challenge, I become more prepared in troubleshooting database corruption. I would like to thank Steve for making contribution to the SQL community by conducting the Database Corruption Challenge. Hopefully he would do more of this challenges in the future.

Additional Resources

While working on the database corruption challenge, some of the resources that I found useful and/or interesting are:

  • Paul Randal's "Corruption Demo Databases and Scripts". Paul Randal is one of the expert subject matter when it comes to database corruption. He has several databases corruption demo and scripts that we can download. It can be found here.
  • Mark Rasmussen article on "Deciphering SQL Server Data Page". It explains in some details on how to read the output of DBCC Page. The article can be found here.
  • Steve Stedman's presentation of "When Database Corruption Strikes" to the PASS DBA Virtual Chapter. It can be found here.
  • Kendra Little's "Corrupting Databases for Dummies-Hex Editor Editor". It provides some information on using Hex Editor to corrupt database. The article can be found here.