Repair a suspect database In MS SQL

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.