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.

Rebuilding all table indexes

Index fragmentation can occur when any large data modification operations happen (INSERT, UPDATE, or DELETE statements) on tables.

For DBAs to maintain multiple databases and monitoring the disk fragmentation and rebuilding the indexes for individual database is quite time consuming task. Rebuilding an index drops and re-creates the index. This removes fragmentation and reorders the index rows in contiguous pages.

So we can use DBCC DBREINDEX statement to rebuild all the indexes on all the tables in database.

DBCC DBREINDEX is efficient over dropping and recreating indexes.

This script can be used in a maintenance plan and can be executed in a scheduled job.

--Option 1

USE YourDBName

GO

EXEC sp_MSforeachtable@command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

GO

EXEC sp_updatestats

GO

--Option 2

USE YourDBName

GO

CREATE PROCEDURE ReIndexDB_N_UpdateStats

AS

DECLARE @Table VARCHAR(255)

DECLARE IXCursor

CURSOR FOR

SELECT table_name

FROM information_schema.tables

WHERE table_type = 'base table'

OPEN IXCursor

FETCH NEXT

FROM IXCursor INTO @MyTable

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'Reindexing Table:  ' + @MyTable

DBCC DBREINDEX(@Table, '', 80)

FETCH NEXT

FROM IXCursor INTO @Table

END

CLOSE IXCursor

DEALLOCATE IXCursor

EXEC sp_updatestats

GO

Execution of Stored Procedure sp_updatestats at the end of the Indexes process ensures updating stats of the database.

Click Here for more details