How To Repair A Suspect Database In MSSQL

Possible Causes

The database could have become corrupted.
There is not enough space available for the SQL Server to recover the database during startup.
The database cannot be opened due to inaccessible files or insufficient memory or disk space.
The database files are being held by operating system, third party backup software etc.
There was an unexpected SQL Server Shutdown, power failure or a hardware failure.

Solution: 

EXEC SP_resetstatus databaseName
ALTER DATABASE databaseName SET EMERGENCY
DBCC checkdb databaseName
ALTER DATABASE databaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE databaseName SET MULTI_USER

Continue reading

Hi,

very useful procedure help of  sys.dm_exec_procedure_stats.

SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
    d.cached_time, d.last_execution_time, d.total_elapsed_time,
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
    d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;
--------------------------------------------------------------------------------------------

Step 1:

SELECT DB_ID(N'yourdatabaseName') AS [Database ID];

Step 2:

SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
    d.cached_time, d.last_execution_time, d.total_elapsed_time,
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
    d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
where database_id= DB_ID(N'yourdatabaseName')
ORDER BY [total_worker_time] DESC;


more help :-
https://msdn.microsoft.com/en-us/library/cc280701.aspx

Continue reading