Below is what is collected
- Shows SQL Servers information
- Shows top 5 high cpu used statemants
- Shows who so logged in
- Shows long running cursors
- Shows idle sessions that have open transactions
- Shows free space in tempdb database
- Shows total disk allocated to tempdb database
- Show active jobs
- Shows clients connected
- Shows running batch
- Shows currently blocked requests
- Shows last backup dates
- Shows jobs that are still executing
- Shows failed MS SQL jobs report
- Shows disabled jobs
- Shows avail free DB space
- Shows total DB size (.MDF+.LDF)
- Show hard drive space available
USE master
GO
-- This stored procedure will give you infomation on the SQL server in question.-- Connect with DAC and then execute this stored procedure located in the master database
CREATE PROC sp_dba_DAC
AS
SELECT ' *** Start of DAC Report *** '
SELECT ' -- Shows SQL Servers information '
EXEC ( ' USE MASTER ' )
SELECT CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' MachineName ' )) AS ' MACHINE NAME '
, CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ServerName ' )) AS ' SQL SERVER NAME '
,( CASE WHEN CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' InstanceName ' )) IS NULL
THEN ' Default Instance '
ELSE CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' InstanceName ' ))
END ) AS ' INSTANCE NAME '
, CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' EDITION ' )) AS EDITION
, CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ProductVersion ' )) AS ' PRODUCT VERSION '
, CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ProductLevel ' )) AS ' PRODUCT LEVL '
,( CASE WHEN CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ISClustered ' )) = 1
THEN ' Clustered '
WHEN CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ISClustered ' )) = 0
THEN ' NOT Clustered '
ELSE ' INVALID INPUT/ERROR '
END ) AS ' FAILOVER CLUSTERED '
,( CASE WHEN CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ISIntegratedSecurityOnly ' )) = 1
THEN ' Integrated Security '
WHEN CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ISIntegratedSecurityOnly ' )) = 0
THEN ' SQL Server Security '
ELSE ' INVALID INPUT/ERROR '
END ) AS ' SECURITY '
,( CASE WHEN CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ISSingleUser ' )) = 1
THEN ' Single User '
WHEN CONVERT ( CHAR ( 20 ), SERVERPROPERTY( ' ISSingleUser ' )) = 0
THEN ' Multi User '
ELSE ' INVALID INPUT/ERROR '
END ) AS ' USER MODE '
, CONVERT ( CHAR ( 30 ), SERVERPROPERTY( ' COLLATION ' )) AS COLLATION
SELECT ' -- Shows top 5 high cpu used statemants '
SELECT TOP 5
total_worker_time / execution_count AS [ Avg CPU Time ]
, SUBSTRING (st. text , ( qs.statement_start_offset / 2 ) + 1 ,
( ( CASE qs.statement_end_offset
WHEN - 1 THEN DATALENGTH (st. text )
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2 ) + 1 ) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time / execution_count DESC ;
SELECT ' -- Shows who so logged in '
SELECT login_name
, COUNT (session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name ;
SELECT ' -- Shows long running cursors '
EXEC ( ' USE master ' )
SELECT creation_time
,cursor_id
,name
,c.session_id
,login_name
FROM sys.dm_exec_cursors ( 0 ) AS c
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE DATEDIFF (mi, c.creation_time, GETDATE ()) > 5 ;
SELECT ' -- Shows idle sessions that have open transactions '
SELECT s. *
FROM sys.dm_exec_sessions AS s
WHERE EXISTS ( SELECT *
FROM sys.dm_tran_session_transactions AS t
WHERE t.session_id = s.session_id )
AND NOT EXISTS ( SELECT *
FROM sys.dm_exec_requests AS r
WHERE r.session_id = s.session_id ) ;
SELECT ' -- Shows free space in tempdb database '
SELECT SUM (unallocated_extent_page_count) AS [ free pages ]
,( SUM (unallocated_extent_page_count) * 1.0 / 128 ) AS [ free space in MB ]
FROM sys.dm_db_file_space_usage ;
SELECT ' -- Shows total disk allocated to tempdb database '
SELECT SUM (size) * 1.0 / 128 AS [ size in MB ]
FROM tempdb.sys.database_files
SELECT ' -- Show active jobs '
SELECT DB_NAME (database_id) AS [ Database ]
, COUNT ( * ) AS [ Active Async Jobs ]
FROM sys.dm_exec_background_job_queue
WHERE in_progress = 1
GROUP BY database_id ;
SELECT ' --Shows clients connected '
SELECT session_id
,client_net_address
,client_tcp_port
FROM sys.dm_exec_connections ;
SELECT ' --Shows running batch '
SELECT *
FROM sys.dm_exec_requests ;
SELECT ' --Shows currently blocked requests '
SELECT session_id
,status
,blocking_session_id
,wait_type
,wait_time
,wait_resource
,transaction_id
FROM sys.dm_exec_requests
WHERE status = N ' suspended '
SELECT ' --Shows last backup dates ' AS ' '
SELECT B.name AS Database_Name
, ISNULL ( STR ( ABS ( DATEDIFF ( day , GETDATE (), MAX (Backup_finish_date)))),
' NEVER ' ) AS DaysSinceLastBackup
, ISNULL ( CONVERT ( CHAR ( 10 ), MAX (backup_finish_date), 101 ), ' NEVER ' ) AS LastBackupDate
FROM master.dbo.sysdatabases B
LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name
AND A.type = ' D '
GROUP BY B.Name
ORDER BY B.name
SELECT ' --Shows jobs that are still executing ' AS ' '
EXEC msdb.dbo.sp_get_composite_job_info NULL , NULL , NULL , NULL , NULL , NULL ,
1 , NULL , NULL
SELECT ' --Shows failed MS SQL jobs report ' AS ' '
SELECT name
FROM msdb.dbo.sysjobs A
,msdb.dbo.sysjobservers B
WHERE A.job_id = B.job_id
AND B.last_run_outcome = 0
SELECT ' --Shows disabled jobs ' AS ' '
SELECT name
FROM msdb.dbo.sysjobs
WHERE enabled = 0
ORDER BY name
SELECT ' --Shows avail free DB space ' AS ' '
EXEC sp_MSForEachDB ' Use ? SELECT name AS '' Name of File '' , size/128.0 -CAST(FILEPROPERTY(name, '' SpaceUsed '' ) AS int)/128.0 AS '' Available Space In MB '' FROM .SYSFILES '
SELECT ' --Shows total DB size (.MDF+.LDF) ' AS ' '
SET nocount ON
DECLARE @name SYSNAME
DECLARE @SQL NVARCHAR ( 600 ) -- Use temporary table to sum up database size w/o using group by
CREATE TABLE #databases
(
DATABASE_NAME SYSNAME NOT NULL
,size INT NOT NULL
)
DECLARE c1 CURSOR FOR SELECT name FROM master.dbo.sysdatabases
-- where has_dbaccess(name) = 1
-- Only look at databases to which we have access
OPEN c1
FETCH c1 INTO @name
WHILE @@fetch_status >= 0
BEGIN
SELECT @SQL = ' insert into #databases select N ''' + @name
+ ''' , sum(size) from ' + QUOTENAME ( @name )
+ ' .dbo.sysfiles ' -- Insert row for each database
EXECUTE ( @SQL )
FETCH c1 INTO @name
END
DEALLOCATE c1
SELECT DATABASE_NAME
,DATABASE_SIZE_MB = size * 8 / 1000 -- Convert from 8192 byte pages to K and then convert to MB
FROM #databases
ORDER BY 1
SELECT SUM (size * 8 / 1000 ) AS ' --Shows disk space used - ALL DBs - MB '
FROM #databases ;
DROP TABLE #databases ;
SELECT ' --Show hard drive space available ' AS ' ' ;
EXEC master..xp_fixeddrives ;
SELECT ' *** End of Report **** ' ;
GO
