How to recover a Database in suspect mode

Step 1:
Clear the suspect mode of the database using sp_resetstatus DatabaseName. This will clear the suspect flag and make the database available online

Step 2:
Change the database status to Emergency using the following command. Emergency mode allows you to access the databases as normal but with no consistency guarantee. This option also allows us to export the table data so that we can minimize the damage.
ALTER DATABASE DatabaseName SET EMERGENCY;

Step 3:
Restrict database to single user by changing the access mode as mentioned below
ALTER DATABASE DatabaseName SET SINGLE_USER;

Step 4:
Run the CHECKDB command with “REPAIR_ALLOW_DATA_LOSS” option. This option should be tried as last option as it always behaves the way it is named. We are not sure of what data it removes.
DBCC CHECKDB (DatabaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

There are some best (simple) practices which prevents us from such failures. Below are some of them

* Backup your data frequently ( daily or once in two days)
* Have multiple backups. Move the backups to external drives or tapes frequently
* Validate that your backups are good by doing trial restores to alternate server
* Run CHECKDB regularly

Automatically Reindex All Tables in a SQL Server Database

ECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = ‘base table’

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Calculate Average Resources Usage Numbers For Stored Procedures That Have Metrics On My System:

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName

,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]

,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]

,cached_time

,last_execution_time

,execution_count

,total_worker_time / execution_count AS AVG_CPU

,total_elapsed_time / execution_count AS AVG_ELAPSED

,total_logical_reads / execution_count AS AVG_LOGICAL_READS

,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES

,total_physical_reads / execution_count AS AVG_PHYSICAL_READS

FROM sys.dm_exec_procedure_stats

ORDER BY AVG_LOGICAL_READS DESC

Find Top 10 Most Expensive Queries In SQL Server

SELECT TOP 10 SUBSTRING(qt.TEXT,(qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC