Incident:
Yesterday I got a mail from database alert (Automated mail alert)
informing me that one of our databases located on the test server went into Suspect
Mode. The version used was SQL Server 2005 Service Pack 3.
Solution:
The first step was to identify why this incident occurred and
after investigation it was found that it was due to the corruption of the transnational log file of the database.
I connected to SSMS using
the sa login credentials
and located the SUSPECT database:
Other Possible Causes
- The database could have become corrupted.
- The database cannot be opened due to inaccessible files
or insufficient memory or disk space.
- There was an unexpected SQL Server Shutdown, power
failure or a hardware failure.
After
some RND, I found a set of queries to get database back in normal mode.
EXEC sp_resetstatus 'DBname';
ALTER DATABASE yourDBname SET
EMERGENCY
DBCC checkdb('DBname')
ALTER DATABASE yourDBname SET
SINGLE_USER WITH
ROLLBACK IMMEDIATE
DBCC CheckDB ('DBname',
REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET
MULTI_USER
Below script will help you to get email notification for 'Suspected
Database:
All
you have to do is schedules this script in SQL Agent or maintenance plan.
if(select count(*) from sys.databases where state=4)>0
Begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SGDBA',--replace with your
profine name
@recipients =
'admi@spluspl.com',
--Replace with your email for group email
@query = ' set nocount off
select name, state from sys.databases where
state = 4',
@subject = 'Suspected Database',
@Body = 'Body' ,
@importance='high',
@attach_query_result_as_file = 0 ;
END
Else Print 'no suspect databases'
Reference taken from codeproject and support managed.
