Problem: Making a suspect
database to online in SQL SERVER.
Solution:
Sometimes SQL server database mark as suspect due to error in its physical
files like log file. It is difficult to make database online when made as
suspect database. There are several ways we can identify that a database is in
suspect mode
select DATABASEPROPERTYEX ( 'database_name' , 'status')
or
SELECT * FROM sys.databases WHERE state = 4;
Or
You can also check yellow ! sign from SSMS.
You can recover suspect db using 2 ways
1-
Restore latest database backup with
transactional backup if you have
2-
If there is any log error recorded in sql server
error log. You can recover error to check that or by replacing log file.
3-
Use following command to change stats but this will
loss uncommitted data from log file.
ALTER DATABASE
DBName SET EMERGENCY
GO
ALTER DATABASE
DBName SET
SINGLE_USER
GO
This command will set database in emergency condition so
that single sign on user can work on it.
DBCC CheckDB (DBName , REPAIR_ALLOW_DATA_LOSS)
GO
You will receive folloiwng output.
DBCC results for 'dbNAME'.
Service Broker Msg 9675,
State 1: Message Types analyzed: 14.
Service Broker Msg 9676,
State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667,
State 1: Services analyzed: 3.
Service Broker Msg 9668,
State 1: Service Queues analyzed: 3.
Service Broker Msg 9669,
State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674,
State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670,
State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605,
State 1: Conversation Priorities analyzed: 0.
DBCC results for
'sys.sysrscols'.
There are 632 rows in 7 pages
for object "sys.sysrscols".
DBCC results for
'sys.sysrowsets'.
There are 91 rows in 1 pages
for object "sys.sysrowsets".
DBCC results for
'sys.sysallocunits'.
There are 103 rows in 2 pages
for object "sys.sysallocunits".
DBCC results for
'sys.sysfiles1'.
There are 2 rows in 1 pages
for object "sys.sysfiles1".
DBCC results for
'sys.syspriorities'.
There are 0 rows in 0 pages for
object "sys.syspriorities".
DBCC results for
'sys.sysfgfrag'.
There are 2 rows in 1 pages
for object "sys.sysfgfrag".
DBCC results for
'sys.sysphfg'.
There are 1 rows in 1 pages
for object "sys.sysphfg".
DBCC results for
'sys.sysprufiles'.
There are 2 rows in 1 pages
for object "sys.sysprufiles".
DBCC results for
'sys.sysftinds'.
There are 0 rows in 0 pages
for object "sys.sysftinds".
DBCC results for
'sys.sysowners'.
There are 14 rows in 1 pages
for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 130 rows in 1 pages
for object "sys.sysprivs".
DBCC results for
'sys.sysschobjs'.
There are 53 rows in 1 pages
for object "sys.sysschobjs".
DBCC results for
'sys.syscolpars'.
There are 483 rows in 8 pages
for object "sys.syscolpars".
DBCC results for
'sys.sysnsobjs'.
There are 1 rows in 1 pages
for object "sys.sysnsobjs".
DBCC results for
'sys.syscerts'.
There are 0 rows in 0 pages
for object "sys.syscerts".
DBCC results for
'sys.sysxprops'.
There are 0 rows in 0 pages
for object "sys.sysxprops".
DBCC results for
'sys.sysscalartypes'.
There are 34 rows in 1 pages
for object "sys.sysscalartypes".
DBCC results for
'sys.systypedsubobjs'.
There are 0 rows in 0 pages
for object "sys.systypedsubobjs".
DBCC results for
'sys.sysidxstats'.
There are 107 rows in 1 pages
for object "sys.sysidxstats".
DBCC results for
'sys.sysiscols'.
There are 259 rows in 1 pages
for object "sys.sysiscols".
DBCC results for
'sys.sysbinobjs'.
There are 23 rows in 1 pages
for object "sys.sysbinobjs".
DBCC results for
'sys.sysaudacts'.
There are 0 rows in 0 pages
for object "sys.sysaudacts".
DBCC results for
'sys.sysobjvalues'.
There are 109 rows in 14
pages for object "sys.sysobjvalues".
DBCC results for
'sys.sysclsobjs'.
There are 16 rows in 1 pages
for object "sys.sysclsobjs".
DBCC results for
'sys.sysrowsetrefs'.
There are 0 rows in 0 pages
for object "sys.sysrowsetrefs".
DBCC results for
'sys.sysremsvcbinds'.
There are 0 rows in 0 pages
for object "sys.sysremsvcbinds".
DBCC results for
'sys.sysxmitqueue'.
There are 0 rows in 0 pages
for object "sys.sysxmitqueue".
DBCC results for
'sys.sysrts'.
There are 1 rows in 1 pages
for object "sys.sysrts".
DBCC results for
'sys.sysconvgroup'.
There are 0 rows in 0 pages
for object "sys.sysconvgroup".
DBCC results for
'sys.sysdesend'.
There are 0 rows in 0 pages
for object "sys.sysdesend".
DBCC results for
'sys.sysdercv'.
There are 0 rows in 0 pages
for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 146 rows in 1 pages
for object "sys.syssingleobjrefs".
DBCC results for
'sys.sysmultiobjrefs'.
There are 106 rows in 1 pages
for object "sys.sysmultiobjrefs".
DBCC results for
'sys.sysguidrefs'.
There are 0 rows in 0 pages for
object "sys.sysguidrefs".
DBCC results for
'sys.syscompfragments'.
There are 0 rows in 0 pages
for object "sys.syscompfragments".
DBCC results for
'sys.sysftstops'.
There are 0 rows in 0 pages
for object "sys.sysftstops".
DBCC results for
'sys.sysqnames'.
There are 97 rows in 1 pages
for object "sys.sysqnames".
DBCC results for
'sys.sysxmlcomponent'.
There are 99 rows in 1 pages
for object "sys.sysxmlcomponent".
DBCC results for
'sys.sysxmlfacet'.
There are 112 rows in 1 pages
for object "sys.sysxmlfacet".
DBCC results for
'sys.sysxmlplacement'.
There are 18 rows in 1 pages
for object "sys.sysxmlplacement".
DBCC results for
'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages
for object "sys.sysobjkeycrypts".
DBCC results for
'sys.sysasymkeys'.
There are 0 rows in 0 pages
for object "sys.sysasymkeys".
DBCC results for
'sys.syssqlguides'.
There are 0 rows in 0 pages
for object "sys.syssqlguides".
DBCC results for
'sys.sysbinsubobjs'.
There are 3 rows in 1 pages
for object "sys.sysbinsubobjs".
DBCC results for
'sys.syssoftobjrefs'.
There are 0 rows in 0 pages
for object "sys.syssoftobjrefs".
DBCC results for
'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages
for object "sys.queue_messages_1977058079".
DBCC results for
'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages
for object "sys.queue_messages_2009058193".
DBCC results for
'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages
for object "sys.queue_messages_2041058307".
DBCC results for
'sys.filestream_tombstone_2073058421'.
There are 0 rows in 0 pages
for object "sys.filestream_tombstone_2073058421".
DBCC results for
'sys.syscommittab'.
There are 0 rows in 0 pages
for object "sys.syscommittab".
CHECKDB found 0 allocation
errors and 0 consistency errors in database 'dbNAME'.
DBCC execution completed. If
DBCC printed error messages, contact your system administrator.
ALTER DATABASE
DBName SET
MULTI_USER
GO
This command will change single user status to
multi user and your database is now online again.
No comments:
Post a Comment