Causes
SQL Server database may go
to suspect mode due to
1.
Unavailability or Corruption of one or more
database files.
2.
Database server\service might not be shut
down properly.
3.
Denial of access to a database resource by
operating system.
Being in suspect mode, we
can’t perform any transactions until we repair the database.
Step-1
Reset the status of the
suspected database. sp_resetstatus turns off the suspect flag on a database.
This procedure updates the mode and status columns of the named database in
sys.databases.
Step-2
Change the suspected
database state to EMERGENCY which gives READ_ONLY access to database and access
is limited to members of sysadmin server role. EMERGENCY State is mainly used
for troubleshooting purpose.
Step-3
Check the logical and
physical integrity of all the objects in the specified database. This is performed
through DBCC CHECKDB command.
DBCC CHECKDB command
internally runs DBCC CHECKALLOC, DBCC CHECKTABLE and DBCC CHECKCATALOG. It also
validates the contents of indexed views, link level consistency between table
metadata and file system directories and files when storing VARBINARY (MAX)
data in the file system using FILESTREAM and service broker data in the
database.
Step-4
Change the database state
to SINGLE_USER mode
Step-5
Run REPAIR_ALLOW_DATA_LOSS to ensure the
database is returned to a structurally and transitionally consistent state. And
please keep in mind that emergency mode repair is a one-way operation.
Anything it does cannot be rolled back or undone. If this is an issue, then
make a copy of the damaged database before we run emergency mode repair.
Step-6
Change the database state
to MULTI_USER mode.
Consolidated
Script
EXEC sp_resetstatus <<Database Name>>;
ALTER DATABASE <<Database Name>>
SET EMERGENCY
DBCC checkdb('<<Database
Name>>')
ALTER DATABASE <<Database Name>>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('<<Database Name>>', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE <<Database Name>>
SET MULTI_USER
No comments:
Post a Comment