sqlshots: Working with a Suspect Database
Recently I faced a problem with a mobile device (subscriber) which had a suspect database. I tried running the following command to check the database:
DBCC CHECKDB ('databasename')
but it did not work. I wanted to try a few other options, but the need for the device out weighed pin pointing the root problem. So I had to reinitialize the device and send it on it's way. In the meantime I research a bit and ended up talking with a fellow DBA by the name of David Underwood (LinkedIn Profile). The good guy provided some very useful links which illustrated some measures to take. Here is the Code Project Link: http://bit.ly/72ar6x
The Code Project Site basically states to use the following statements.
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
If I get the opportunity I will certainly attempt it. Since I was not working on the actual device I had to look for another route.
So I decided (since I had to expedite my troubleshooting efforts and get the device back in the rotation ASAP) to stop the SQL service and copy the actual problematic database files from the data directory. With the files in reach I felt that I could attach them to a Virtual Instance and troubleshoot the problem further. So here are the steps I took.
1. Opened up SQL Server Management Studio (SSMS)
2. Right clicked on the SQL Instance and selected Attach
3. Click the Add button
4. Select the database file
5. Clicked Ok
6. Received the Attachment Error
7. Click on the Hyperlink to see the Details
8. Decided I should remove the LOG file
9. Removed the physical LDF file from the Data directory (I had a backup of both)
10. Clicked Ok
11. The Database attached fine
I wonder if I had taken the same approach would it have resolved my issue? I guess I'll never know until it happens again. Who am I kidding... you can't!
Comments [0]