Wednesday, 15 July 2015 00:00

How do I repair a Microsoft SQL Server database using Microsoft SQL Server Management Studio Express?

Rate this item
(0 votes)

Introduction

In order to repair tables in a Microsoft SQL Server database, the following commands will need to be issued through the query editor in Microsoft SQL Server Management Studio Express.

Ensure that no Webtrends services are started while the database repair is underway.

Next Steps

1. Download and install MS SQL Server Management Studio Express for the version of the MS SQL database installed with the product.

Webtrends Analytics 8.5 and 8.5a use MS SQL Server Express 2005 by default:
Microsoft SQL Server Management Studio Express

Webtrends Analytics 9.2x and 8.7d use MS SQL Server Express 2008 by default:
Microsoft SQL Server 2008 Management Studio Express

Note: Installation of Management Studio Express 2008 requires installation of the Microsoft Web Platform first, after which Management Studio Express 2008 will follow.

2. After installation, open the application from Start > Programs > Microsoft SQL Server 2005/2008 > SQL Server Management Studio Express.

3. Log into the database using Windows Authentication

4. Expand Databases, then right-click on wt_sched and choose "Properties."

5. Select "Options" and scroll to the bottom in the pane on the right.

6. Change "Restrict Access" to "SINGLE_USER."

7. Select OK and repeat the above steps for the wtMaster section of the database.

8. Select "New Query" from the toolbar, and in the query pane that opens, paste in the following commands:

dbcc checkdb('wtMaster',REPAIR_REBUILD)
dbcc checkdb('wt_sched',REPAIR_REBUILD)
   
9. Select the "Execute" button to repair the database.
   
10. Perform the steps outlined in 4-6 above and change the databases back to "MULTI_USER" from "SINGLE_USER."

Note: Changing the database back to MULTI_USER mode may require logging out and logging back in using SQL Server Management Studio Express in order to change both parts back to their original settings. Attempting to make these changes may result in an error saying there are too many connections. Closing Management Studio and then re-opening it again will allow these changes to take effect.

More Information

To run these queries from the command line, refer to the Article How to run queries from a command prompt.

Additionally, while the 'scan' (no parameters passed) can be run while in the standard multi-user mode, performing repairs requires changing the database to single-user mode.

DBCC CHECKTABLE ('table_name') - In order to check an entire database in Microsoft SQL Server the following command will need to be used via command line or query manager.

DBCC CHECKDB ('database_name') - Both commands have the following repair options:

REPAIR_ALLOW_DATA_LOSS - Performs all repairs done by REPAIR_REBUILD and includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects.
These repairs can result in some data loss. The repair may be done under a user transaction to allow the user to roll back the changes made.
If repairs are rolled back, the database will still contain errors and should be restored from a backup.
If a repair for an error has been skipped due to the provided repair level, any repairs that depend on the repair are also skipped.
After repairs are completed, back up the database.

REPAIR_FAST - Performs minor, non-time-consuming repair actions such as repairing extra keys in non-clustered indexes. These repairs can be done quickly and without risk of data loss.
For a complete list of syntax and examples for checking tables and databases refer to the following Microsoft Knowledge Base articles:

For a complete list of syntax and examples for checking tables and databases refer to the following Microsoft Knowledge Base articles:

DBCC CHECKTABLE - http://msdn2.microsoft.com/en-us/library/Aa258646
DBCC CHECKDB - http://msdn2.microsoft.com/en-us/library/Aa258278
Changing database to/from single-user mode - http://msdn2.microsoft.com/en-us/library/ms345598.aspx


 
Last modified on Tuesday, 22 September 2015 13:06
Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

Get Help Now

Thank you for contacting us.
Your Private Investigator will call you shortly.