Products
Webtrends Analytics 8.5+
Webtrends Analytics 9.x
Cause
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.
Ensure that no Webtrends services are started while the database repair is underway.
Resolution
1. Download and install the latest version of MS SQL Server Management Studio (SSMS).
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
2. After installation, open the application from Start > Programs > Microsoft SQL Server Tools > Microsoft SQL Server Management Studio .
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