Monday, January 14, 2013

Checking and fixing SQL Database

In the previous post I showed how to recreate a Windows SharePoint Search Database that had become unusable. There is of course some troubleshooting you can do before recreating an SQL database. This is generally to check and repair the database using the SQL command DBCC CHECKDB like so.

 

image

 

For arguments sake, let’s say that the database WSS_SEARCH_SERVERF is corrupt. Firstly login to the Microsoft SQL Server Management Studio Express and expand the Database folder so you can see each database.

 

The first step will be to put the database into single user mode so that only SQL can work on it exclusively while it is being repaired. Single user mode means that only a single program will be able to use the database. If another process is using the database now and we put the database into single user mode that process will typically grab the database and not allow us to work on it. Thus, you should stop anything else that is currently access the database so SQL Management can gain exclusive access.

 

Because this is a SharePoint Search database we know that the only thing that should be accessing this is the SharePoint Search Service.

 

image

 

Therefore go into the Windows Services console and locate the appropriate SharePoint Search Service. In this case it is Windows SharePoint Search Service since the source system is WSS v3. Right mouse click on the service and select Stop from the menu that appears. You may also want to actually Disable the service to prevent it from re-running itself. If you are checking a SharePoint Search Database then this would be recommended as SharePoint Search will reactive on a regular basis and grab the database.

 

After a few moments the SharePoint Search Service will show nothing in the Status column indicating it is not running.

 

image

 

Return to the SQL Management console, right mouse click on the database in question and select Properties from the menu that appears.

 

In the dialog that appears select Options on the left and then scroll down on the right until you locate the State section at the bottom. There you will see a line Restrict Access. Change this to SINGLE_USER and press the Ok button to continue.

 

You will be prompted about closing connections to this database. Press OK to proceed.

 

image

 

When you are returned to the main SQL Management console you should see a small person icon over the database and the name of the database should have (Single User) displayed after it.

 

image

 

If you now select the New Query button (just under the File menu in the top left of the screen), it will open a blank area on the right. Into here type:

 

dbcc checkdb (‘<databasename>’);

 

in this case:

 

dbcc checkdb (‘wss_search_serverf’);

 

then press the Execute button just above. This will simply examine the database for issues and report back the results.

 

The window below will show the output from this check process. Examine the log to determine the issues that may be evident.

 

Prior to actually completing a repair of the suspect database it is recommended that you have some form of backup.

 

To proceed with the rebuild and repair change the query to:

 

dbcc checkdb (‘<databasename>’, repair_rebuild);

 

in this case:

 

dbcc checkdb (‘wss_search_serverf’, repair_rebuild’);

 

You may need to run this command a few time to complete all the repairs. Check the output of the repair for the results.

 

Hopefully, the database has successfully been repaired and now you can put it back to MULTI_USER mode by reversing the process detailed earlier via Properties | Options | Restrict Access.

 

image

 

With the database now back in multi-user mode you should be able to start the dependent services and everything should now work as expected. Don’t forget to change the mode of any dependent services from disabled in the Windows Services management console if you disabled them earlier.