How to check the integrity of the SQL Database on a DigitalSENTRY System.

NOTICE

POTENTIAL FOR DATA LOSS.
The steps detailed in the resolution of this article may result in a loss of critical data if not performed properly. Before beginning these steps, make sure all important data is backed up in the event of data loss. If you are unsure, please contact Product Support Services prior to attempting the procedure below.

Issue

  • Running the DBCC CHECKDB command to check the integrity of SQL Database on a DigitalSENTRY System.
  • DS Database Check
  • Check the integrity of a Table .

Product Line

Pelco Video Management

Environment

  • DigitalSentry
  • DSSRV / DSSRV2
  • DS NVs
  • SQL Server Management Studio

Cause

  • Checking for possible database corruption in the DigitalSentry SQL Database.
  • Having an issue with a specific Table (redudant error message in Event Viewer pertaining to SQL).

Resolution

Run the "DBCC CHECKDB" query in Microsoft SQL Server Management Studio

  1. Start > All Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio
  2. When the Connect to Server Dialog Box comes up, click "Connect" to open up SQL.
  3. Click on the New Query option.
  4. Type "DBCC CHECKDB" in the New Query dialog.
  5. Click on the "Execute" button on the toolbar to run the query.
  6. When and if the query completes successfully, you will get a status on the bottom of the query dialog.
  7. Verify the results report 0 allocation errors and 0 consistency errors, if both show 0 then the database is healthy and the query/SSMS can be closed. 
    If errors were found continue on with Steps 8 - 10.

    Example when no errors are found:

    CHECKDB found 0 allocation errors and 0 consistency errors in database 'DigitalSENTRY'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.




     
  8. If errors are found, a recommendation of what minimum level of repair will be listed at the bottom message summary.

    Example message with errors and repair recommendation:

    CHECKDB found 0 allocation errors and 4 consistency errors in database 'DigitalSENTRY'.
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DigitalSENTRY).
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


     
  9. Stop the DigitalSENTRY VideoServer Service and DigitalSENTRY Storage services.  The DigitalSENTRY or FLLog database (database with problem found) must be in single user mode before repair options can be run.
     
  10. Run the following Transact-SQL commands to attempt a repair (Step 4 in above instructions).

    Note:  Replace "REPAIR_ALLOW_DATA_LOSS" in second line with repair option recommended from dbcc checkdb, if for example "REPAIR_REBUILD" is recommended and run but does not fix issue, may then need to run again with the "REPAIR_ALLOW_DATA_LOSS" option.
    • ALTER DATABASE DigitalSENTRY SET SINGLE_USER WITH ROLLBACK IMMEDIATE
      GO

       
    • DBCC CheckDB ('DigitalSENTRY', REPAIR_ALLOW_DATA_LOSS)
      GO
       
    • ALTER DATABASE DigitalSENTRY SET MULTI_USER

       
  11. If the repair options do not successfully repair the database corruption, a database backup will need to be used to recover or a system recovery/reinstall.




Run the "DBCC CHECKTABLE" query in Microsoft SQL Server Management Studio

  1. Click on the New Query option.
  2. Type DBCC CHECKTABLE('TableName')   example DBCC CHECKTABLE('dbo.VideoFile') - Execute query
  3. If allocation errors or consistency errors are found, run below commands
    1. Stop all DigitalSentry Services
    2. ALTER DATABASE DigitalSENTRY SET SINGLE_USER - Execute query
    3. DBCC CHECKTABLE('TableName',REPAIR_REBUILD)   example DBCC CHECKTABLE('dbo.VideoFile',REPAIR_REBUILD) - Execute query
    4. ALTER DATABASE DigitalSENTRY SET MULTI_USER - Execute query
    5. Type DBCC CHECKTABLE('TableName') and verify there are no more errors
  4. Start DigitalSentry services and verify system is working properly

 

Repair option definitions:

database_name | database_id | 0
            Is the name or ID of the database for which to run integrity checks. If not specified, or if 0 is specified, the current database is used. Database names must comply with the rules for identifiers.

NOINDEX
             Specifies that intensive checks of nonclustered indexes for user tables should not be performed. This decreases the overall execution time. NOINDEX does not affect system tables because integrity checks are always performed on system table indexes.

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
              Specifies that DBCC CHECKDB repair the found errors. The specified database must be in single-user mode to use one of the following repair options.

REPAIR_ALLOW_DATA_LOSS
              Tries to repair all reported errors. These repairs can cause some data loss.

REPAIR_FAST
              Maintains syntax for backward compatibility only. No repair actions are performed.

REPAIR_REBUILD
               Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.

               REPAIR_REBUILD does not repair errors involving FILESTREAM data.


Important:
Use the REPAIR options only as a last resort. To repair errors, we recommend restoring from a backup. Repair operations do not consider any of the constraints that may exist on or between tables. If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.

             Source: http://msdn.microsoft.com/en-us/library/ms176064.aspx