Share via

SQL Troubleshooting: Repair Suspected / offline SQL DB after Improper shutdown

  • Article

Following script is used to repair and make up/online suspected / offline sql (content) databases for SharePoint.

  1. Login into SLQ instance where corrupted db is, with SA or SysAdmin rights.
  2. Open new query window and select "master" database
  3. Copy paste following SQL script
01.-- sp_resetstatus turns off the "suspect" flag on a database
02.EXEC sp_resetstatus [DatabaseName] 
03. 
04.-- Marking database READ_ONLY, disable logging, 
05.-- and limiting access only to members of the sysadmin fixed server role 
06.ALTER DATABASE  [DatabaseName] SET  EMERGENCY 
07. 
08.-- Checks the logical and physical integrity of all the objects in the specified database
09.DBCC checkdb([DatabaseName]) 
10. 
11.-- This query will rollback any transaction which is running on that database 
12.-- and bring SQL Server database in a "single user" mode
13.ALTER DATABASE  [DatabaseName] SET  SINGLE_USER WITH  ROLLBACK IMMEDIATE 
14. 
15.DBCC CheckDB ([DatabaseName], REPAIR_ALLOW_DATA_LOSS) 
16. 
17.-- Set database accessibility to it's original state, allowing all logins
18.ALTER DATABASE  [DatabaseName] SET  MULTI_USER

    4. Keep braces '[' and ']' but replace "DatabaseName" with your suspected db name.
    5. Run the script.