SQL: Configuration Settings Info Listing - The Big One, v1.4
Author: jason
Date: 2014-04-04 17:42:46
Category: Musing

I've compiled a gigantic script of configuration settings, database info, performance details. I got tired of hunting around my hard drive and the interwebs for a script to show this, and a script to show that. So I put them all into 1 big script. I've tested this on SQL 2012, and it should work on SQL 2008 R2. I've put TRY CATCH blocks around the individual scripts, so it should run no matter what. I've listed below the types of information this script is gathering and outputting.

Here is an example of the output




Script Version 1.4
SQL Server Level Info / Config


21-50 - Database Level Info / Config


Database Level Usage / Performance


Code

--Version 1.4
--Updated 4/22/2014
/* Label Flags (Int)
1-20 - SQL Server Level Info / Config
1 = SQL Version
2 = DBCC Trace
3 = SQL Server Config
4 = Server Total RAM
5 = Disk Free Space
6 = SQL Agent Jobs
7 = SQL Sys Admins
17 = Drive Level Latency (Read)
18 = Drive Level Latency (Write)
19 = Drive Level Latency (Overall)
20 = Page Life Expectancy

21-50 - Database Level Info / Config
21 = Database Compatibility Level
22 = Database User Access
23 = Database State
24 = Database Recovery Mode
25 = Last Database Restore
26 = Last Database Full Backup
27 = Last Database Full Backup Duration
28 = Last Database Transaction Log Backup
29 = DB File Location
30 = DB File Info
31 = DB File Size (MB)
32 = Last CheckDB --4/14/2014 & 4/22/2014

51-100 - Database Level Usage / Performance
51 = Transaction Log VLF Counts
52 = Database CPU Usage %
53 = Database IO (MB)
54 = Database IO (%)
55 = Database Buffer Cache Size (MB)
56 = Database Buffer Cache Size (%)
57 = DB File Average Stalls (Read)
58 = DB File Average Stalls (Write)
59 = DB File Average Stalls (Overall)
60 = DB File Number of Reads
61 = DB File Number of Bytes Read
62 = DB File Number of Writes
63 = DB File Number of Bytes Written
64 = Logspace Usage --4/7/2014
TOP XX Large Tables / Indexes
TOP XX Accessed Tables / Indexes
TOP XX Fragmented Tables / Indexes
TOP XX In Buffer (Indexes / Queries)
*/

--#################################
--Start our Prep

DECLARE @Error_Message NVARCHAR(MAX);
DECLARE @Server NVARCHAR(255);
SET @Server = @@SERVERNAME; --Added 4/11 for SQL 2005
DECLARE @CheckDate SMALLDATETIME;
SET @CheckDate = GetDate(); --Added 4/11 for SQL 2005
DECLARE @Label NVARCHAR(255)
SET @Label = NULL; --Added 4/11 for SQL 2005
DECLARE @LabelInt INT;
SET @LabelInt = NULL; --Added 4/11 for SQL 2005
DECLARE @Results
AS TABLE (
CheckDate SMALLDATETIME
, [Server] NVARCHAR(255)
, [Database] NVARCHAR(255)
, LabelInt INT
, Label NVARCHAR(255)
, SubLabel NVARCHAR(255)
, [Value] VARCHAR(MAX)
);


--#################################
--Start Category: 1-20 - SQL Server Level Info / Config

--Grab the SQL Version
SET @Label = 'SQL Version';
SET @LabelInt = 1;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, NULL, @@VERSION
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Grab the DBCC Trace Flags
SET @Label = 'SQL Trace Flags';
SET @LabelInt = 2;
BEGIN TRY;
DECLARE @DBCCTrace AS TABLE (TraceFlag INT, Status INT, Global INT, Session INT);
INSERT INTO @DBCCTrace
EXEC('DBCC TRACESTATUS(-1) WITH NO_INFOMSGS;');
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, NULL,
(SELECT --Put it all into a big String, consider keeping the rows though, hmm...
COALESCE(
STUFF(
(SELECT ',' + CAST(TraceFlag AS VARCHAR(10)) +
',' + CAST(Status AS VARCHAR(10)) +
',' + CAST(Global AS VARCHAR(10)) +
',' + CAST(Session AS VARCHAR(10))
AS [text()]
FROM @DBCCTrace
FOR XML PATH(''))
, 1, 1, '')
, '') AS MyString
);
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--SQL Server Config
SET @Label = 'SQL Config';
SET @LabelInt = 3;
BEGIN TRY;
DECLARE @SQLConfig AS TABLE (Name VARCHAR(255), Minimum BIGINT, Maximum BIGINT, Config_Value INT, Run_Value INT)
EXEC('EXEC sp_configure ''show advanced options'', 1;');
EXEC('RECONFIGURE;');
INSERT INTO @SQLConfig
EXEC ('EXEC sp_configure;');
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label
, Name
--',' + CAST(Minimum AS VARCHAR(255)) +
--',' + CAST(Maximum AS VARCHAR(255)) +
, CAST(Config_Value AS VARCHAR(255)) +
',' + CAST(Run_Value AS VARCHAR(255))
FROM @SQLConfig;
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Physical Server RAM -- Changed 4/11 for SQL 2005
SET @Label = 'Server Total RAM (MB)';
SET @LabelInt = 4;
IF @@VERSION LIKE 'Microsoft SQL Server 2005%'
OR @@VERSION LIKE 'Microsoft SQL Server 2000%' BEGIN
PRINT @@VERSION;
END ELSE BEGIN
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, NULL,
total_physical_memory_kb / 1024 FROM sys.dm_os_sys_memory;
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH
END

--Disk Free Space, sys.dm_os_volume_stats is only available >= 2008 R2 SP1
SET @Label = 'Disk Free Space (MB)';
SET @LabelInt = 5;
BEGIN TRY;
DECLARE @DiskFree AS TABLE (Drive VARCHAR(10), MBFree INT)
INSERT INTO @DiskFree
EXEC('xp_fixeddrives');
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label,
Drive + ':\ ', CAST(MBFree AS VARCHAR(50)) FROM @DiskFree;
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--SQL Agent Jobs
SET @Label = 'SQL Agent Job';
SET @LabelInt = 6;
BEGIN TRY;
WITH CTE AS (
SELECT instance_id, job_id, ROW_NUMBER() OVER (PARTITION BY
job_id ORDER BY job_id, instance_id DESC) RowNum
FROM msdb.dbo.sysjobhistory WITH(NOLOCK)
)
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, j.name,
--SELECT
--j.name JobName
--'Enabled: ' + e.Enabled + ', ' +
--'Last Run: ' + ISNULL(t.RunStatus, 'Never Run')
'Enabled: ' + (CASE WHEN j.enabled = 1 THEN 'Yes' ELSE 'No' END) + ', ' +
'Last Run: ' + (CASE WHEN h.run_status = 0 THEN 'Failed'
WHEN h.run_status = 1 THEN 'Succeeded'
WHEN h.run_status = 2 THEN 'Retry'
WHEN h.run_status = 3 THEN 'Cancelled'
WHEN h.run_status = 4 THEN 'Running'
ELSE 'Never Run' END)
--, h.message JobMessage
--, h.run_date RunDate
--, h.run_time RunTime
--, ISNULL(h.run_duration,0) RunDuration
--, a.next_scheduled_run_date NextRunDate
FROM msdb.dbo.sysjobs j WITH(NOLOCK)
LEFT JOIN CTE c ON c.job_id = j.job_id
LEFT JOIN msdb.dbo.sysjobhistory h WITH(NOLOCK)
ON h.instance_id = c.instance_id AND h.job_id = c.job_id
LEFT JOIN msdb.dbo.sysjobactivity a WITH(NOLOCK)
ON a.job_history_id = c.instance_id AND a.job_id = c.job_id
--LEFT JOIN (VALUES (1, 'Yes'), (0, 'No')) AS e(EnabledID,Enabled)
-- ON e.EnabledID = j.enabled
--LEFT JOIN (VALUES
-- (0, 'Failed'),(1, 'Succeeded'),(2, 'Retry'),(3, 'Cancelled'),(4, 'Running')
-- ) AS t(RunStatusID,RunStatus)
-- ON t.RunStatusID = h.run_status
WHERE ISNULL(c.RowNum,1) = 1
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--SQL Sys Admins
SET @Label = 'SQL Sys Admins';
SET @LabelInt = 7;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, NULL,
p.name --+ ', Disabled: ' + (CASE WHEN r.is_disabled = 0 THEN 'No' ELSE 'Yes' END)
FROM sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON p.principal_id = m.member_principal_id
WHERE r.type = 'R' and r.name = N'sysadmin'
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Drive Level Latency (Read)
SET @Label = 'Drive Level Latency (Read)';
SET @LabelInt = 17;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, [Drive] + '\',
CASE
WHEN num_of_reads = 0 THEN 0
ELSE (io_stall_read_ms/num_of_reads)
END AS [Read Latency]
FROM (SELECT LEFT(mf.physical_name, 2) AS Drive, SUM(num_of_reads) AS num_of_reads,
SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
GROUP BY LEFT(mf.physical_name, 2)) AS tab
OPTION (RECOMPILE);
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Drive Level Latency (Write)
SET @Label = 'Drive Level Latency (Write)';
SET @LabelInt = 18;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, [Drive] + '\',
CASE
WHEN io_stall_write_ms = 0 THEN 0
ELSE (io_stall_write_ms/num_of_writes)
END AS [Write Latency]
FROM (SELECT LEFT(mf.physical_name, 2) AS Drive, SUM(num_of_reads) AS num_of_reads,
SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
GROUP BY LEFT(mf.physical_name, 2)) AS tab
OPTION (RECOMPILE);
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Drive Level Latency (Overall)
SET @Label = 'Drive Level Latency (Overall)';
SET @LabelInt = 19;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, [Drive] + '\',
/* CASE
WHEN num_of_reads = 0 THEN 0
ELSE (io_stall_read_ms/num_of_reads)
END AS [Read Latency]
CASE
WHEN io_stall_write_ms = 0 THEN 0
ELSE (io_stall_write_ms/num_of_writes)
END AS [Write Latency],
*/
CASE
WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0
ELSE (io_stall/(num_of_reads + num_of_writes))
END AS [Overall Latency]
/* CASE
WHEN num_of_reads = 0 THEN 0
ELSE (num_of_bytes_read/num_of_reads)
END AS [Avg Bytes/Read],
CASE
WHEN io_stall_write_ms = 0 THEN 0
ELSE (num_of_bytes_written/num_of_writes)
END AS [Avg Bytes/Write],
CASE
WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0
ELSE ((num_of_bytes_read + num_of_bytes_written)/(num_of_reads + num_of_writes))
END AS [Avg Bytes/Transfer]
*/
FROM (SELECT LEFT(mf.physical_name, 2) AS Drive, SUM(num_of_reads) AS num_of_reads,
SUM(io_stall_read_ms) AS io_stall_read_ms, SUM(num_of_writes) AS num_of_writes,
SUM(io_stall_write_ms) AS io_stall_write_ms, SUM(num_of_bytes_read) AS num_of_bytes_read,
SUM(num_of_bytes_written) AS num_of_bytes_written, SUM(io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
GROUP BY LEFT(mf.physical_name, 2)) AS tab
OPTION (RECOMPILE);
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Page File Expectancy
SET @Label = 'Page Life Expectancy (sec)';
SET @LabelInt = 20;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, NULL,
AVG(cntr_value) FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
AND counter_name = N'Page life expectancy';
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--#################################
--Start Category: 21-50 - Database Level Info / Config


--Database Compatibility Level
SET @Label = 'Database Compatibility';
SET @LabelInt = 21;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, name, @LabelInt, @Label, NULL,
compatibility_level FROM master.sys.databases;
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Database User Access
SET @Label = 'Database User Access';
SET @LabelInt = 22;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, name, @LabelInt, @Label, NULL,
user_access_desc FROM master.sys.databases;
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Database State
SET @Label = 'Database State';
SET @LabelInt = 23;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, name, @LabelInt, @Label, NULL,
state_desc FROM master.sys.databases;
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Database Recovery Mode
SET @Label = 'Database Recovery Mode';
SET @LabelInt = 24;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, name, @LabelInt, @Label, NULL,
recovery_model_desc FROM master.sys.databases;
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--is_auto_create_stats_on FROM master.sys.databases
--is_auto_update_stats_on FROM master.sys.databases
--is_auto_update_stats_async_on FROM master.sys.databases
--is_auto_shrink_on FROM master.sys.databases


--Last Database Restore
SET @Label = 'Last Restore';
SET @LabelInt = 25;
BEGIN TRY;
WITH RestoreInfo AS (
SELECT d.name,
restore_date, '' AS [length], 'Restore' AS [type],
ROW_NUMBER() OVER (PARTITION BY d.name
ORDER BY restore_date DESC) AS RestoreNum
FROM master.dbo.sysdatabases d
LEFT JOIN msdb.dbo.restorehistory r on r.destination_database_name = d.name AND restore_type IN ('D')
)
INSERT INTO @Results
SELECT @CheckDate, @Server, name, @LabelInt, @Label, NULL,
restore_date FROM RestoreInfo WHERE RestoreNum = 1;
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Last Database Full Backup
SET @Label = 'Last Full Backup';
SET @LabelInt = 26;
BEGIN TRY;
WITH BackupInfo AS (
SELECT
d.name, backup_finish_date
,CAST(DATEDIFF(MI,backup_start_date,backup_finish_date) AS VARCHAR(255)) + ' minutes' AS [length]
,'Backup Full' AS [type]
, ROW_NUMBER() OVER (PARTITION BY d.name ORDER BY backup_finish_date DESC) AS BackupNum
FROM master.dbo.sysdatabases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name AND [TYPE] = 'D'
)
INSERT INTO @Results
SELECT @CheckDate, @Server, name, @LabelInt, @Label, NULL,
backup_finish_date FROM BackupInfo WHERE BackupNum = 1;
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Last Database Full Backup Duration
SET @Label = 'Last Full Backup Duration';
SET @LabelInt = 27;
BEGIN TRY;
WITH BackupInfo AS (
SELECT
d.name, backup_finish_date
,CAST(DATEDIFF(MI,backup_start_date,backup_finish_date) AS VARCHAR(255)) + ' minutes' AS [length]
,'Backup Full' AS [type]
, ROW_NUMBER() OVER (PARTITION BY d.name ORDER BY backup_finish_date DESC) AS BackupNum
FROM master.dbo.sysdatabases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name AND [TYPE] = 'D'
)
INSERT INTO @Results
SELECT @CheckDate, @Server, name, @LabelInt, @Label, NULL,
[length] FROM BackupInfo WHERE BackupNum = 1;
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Last Database Transaction Log Backup
SET @Label = 'Last Transaction Log Backup';
SET @LabelInt = 28;
BEGIN TRY;
WITH BackupInfo AS (
SELECT
@Server AS [server],d.name,backup_finish_date
,CAST(DATEDIFF(MI,backup_start_date,backup_finish_date) AS VARCHAR(255)) + ' minutes' AS [length]
,'Backup Log' AS [type]
, ROW_NUMBER() OVER (PARTITION BY d.name ORDER BY backup_finish_date DESC) AS BackupNum
FROM master.dbo.sysdatabases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name AND [TYPE] = 'L'
)
INSERT INTO @Results
SELECT @CheckDate, @Server, name, @LabelInt, @Label, NULL,
backup_finish_date FROM BackupInfo WHERE BackupNum = 1;
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--DB File Location
SET @Label = 'File Location';
SET @LabelInt = 29;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, DB_NAME([database_id]), @LabelInt, @Label
, name LogicalName --SubLabel
, physical_name PhysicalName --Value
--, type_desc TypeDesc
--, state_desc StateDesc
--, (CASE WHEN is_percent_growth = 1 THEN 'Yes' ELSE 'No' END) PercentGrowth
--, growth
--,CONVERT( bigint, size/128.0) AS [TotalSize(MB)]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] <> 32767
ORDER BY [database_id], [file_id];
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--DB File Info
SET @Label = 'File Info';
SET @LabelInt = 30;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, DB_NAME([database_id]), @LabelInt, @Label
, name LogicalName --SubLabel
, 'TypeDesc: ' + type_desc + ', ' +
'StateDesc: ' + state_desc + ', ' +
'PercentGrowth: ' + (CASE WHEN is_percent_growth = 1 THEN 'Yes' ELSE 'No' END) + ', ' +
'Growth: ' + CAST(growth AS VARCHAR(50))
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] <> 32767
ORDER BY [database_id], [file_id];
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--DB File Size (MB)
SET @Label = 'File Size (MB)';
SET @LabelInt = 31;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, DB_NAME([database_id]), @LabelInt, @Label
, name LogicalName --SubLabel
, CONVERT( bigint, size/128.0)
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] <> 32767
ORDER BY [database_id], [file_id];
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Last CheckDB
SET @Label = 'Last CheckDB';
SET @LabelInt = 32;
BEGIN TRY;
CREATE TABLE #CheckDB1 (ParentObject VARCHAR(255)
, [Object] VARCHAR(255)
, [Field] VARCHAR(255)
, [Value] VARCHAR(255))
CREATE TABLE #CheckDB2 ([Database] VARCHAR(255), LastValue DATETIME)

EXEC sp_MSforeachdb 'Use [?];
INSERT INTO #CheckDB1
EXEC(''DBCC DBINFO([?]) WITH TABLERESULTS'');
INSERT INTO #CheckDB2
SELECT DISTINCT DB_NAME(), MAX(Value)
FROM #CheckDB1
WHERE [Field] = ''dbi_dbccLastKnownGood''
AND DB_NAME() NOT IN (SELECT [Database] FROM #CheckDB2)
GROUP BY [Field]
TRUNCATE TABLE #CheckDB1
';

INSERT INTO @Results
SELECT @CheckDate, @Server, [Database], @LabelInt, @Label
, NULL --SubLabel
, LastValue --Value
FROM #CheckDB2
DROP TABLE #CheckDB1
DROP TABLE #CheckDB2

END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH

--#################################
--Start Category: 51-100 - Database Level Usage / Performance


--Transaction Log VLF Counts
SET @Label = 'VLF Counts';
SET @LabelInt = 51;
IF @@VERSION LIKE 'Microsoft SQL Server 2012%' BEGIN
BEGIN TRY;
CREATE TABLE #LogInfo2 (
ResourceUnitId BIGINT, --COMMENT THIS LINE OUT IF <2012 Server
FileID INT, FileSize BIGINT, StartOffset BIGINT
, FSeqNo BIGINT, Status BIGINT, Parity BIGINT, CreateLSN NUMERIC(38))
CREATE TABLE #VLFCounts2 ([Database] VARCHAR(255), VLFCount INT)

EXEC sp_MSforeachdb 'Use [?];
INSERT INTO #LogInfo2
EXEC(''DBCC LOGINFO([?]) WITH NO_INFOMSGS'');
INSERT INTO #VLFCounts2
SELECT DB_NAME(), @@ROWCOUNT;
';
INSERT INTO @Results
SELECT @CheckDate, @Server, [Database], @LabelInt, @Label, NULL, [VLFCount]
FROM #VLFCounts2 ORDER BY VLFCount DESC;
DROP TABLE #VLFCounts2;
DROP TABLE #LogInfo2;
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH
END ELSE BEGIN
BEGIN TRY;
CREATE TABLE #LogInfo (
--ResourceUnitId BIGINT, --COMMENT THIS LINE OUT IF <2012 Server
FileID INT, FileSize BIGINT, StartOffset BIGINT
, FSeqNo BIGINT, Status BIGINT, Parity BIGINT, CreateLSN NUMERIC(38))
CREATE TABLE #VLFCounts ([Database] VARCHAR(255), VLFCount INT)

EXEC sp_MSforeachdb 'Use [?];
INSERT INTO #LogInfo
EXEC(''DBCC LOGINFO([?]) WITH NO_INFOMSGS'');
INSERT INTO #VLFCounts
SELECT DB_NAME(), @@ROWCOUNT;
';
INSERT INTO @Results
SELECT @CheckDate, @Server, [Database], @LabelInt, @Label, NULL, [VLFCount]
FROM #VLFCounts ORDER BY VLFCount DESC;
DROP TABLE #VLFCounts;
DROP TABLE #LogInfo;
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH
END


--Database CPU Usage %
SET @Label = 'Database CPU %';
SET @LabelInt = 52;
BEGIN TRY;
WITH DBCPU
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DBName], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID)
INSERT INTO @Results
SELECT @CheckDate, @Server, DBName, @LabelInt, @Label, NULL,
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2))
FROM DBCPU
WHERE DatabaseID <> 32767 OPTION (RECOMPILE);
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Database IO (MB)
SET @Label = 'Database IO (MB)';
SET @LabelInt = 53;
BEGIN TRY;
WITH IO_Stats
AS
(SELECT DB_NAME(database_id) AS [DBName],
CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY database_id)
INSERT INTO @Results
SELECT @CheckDate, @Server, DBName, @LabelInt, @Label, NULL,
io_in_mb AS [Total I/O (MB)]
--CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
FROM IO_Stats OPTION (RECOMPILE);
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Database IO %
SET @Label = 'Database IO %';
SET @LabelInt = 54;
BEGIN TRY;
WITH IO_Stats
AS
(SELECT DB_NAME(database_id) AS [DBName],
CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY database_id)
INSERT INTO @Results
SELECT @CheckDate, @Server, DBName, @LabelInt, @Label, NULL,
--io_in_mb AS [Total I/O (MB)]
CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
FROM IO_Stats OPTION (RECOMPILE);
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Database Buffer Cache Size (MB)
SET @Label = 'Buffer Cache Size (MB)';
SET @LabelInt = 55;
BEGIN TRY;
WITH BufferPoolUsage
AS
(SELECT DB_NAME(database_id) AS [DBName],
CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2)) AS [CachedSize]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id))
INSERT INTO @Results
SELECT @CheckDate, @Server, DBName, @LabelInt, @Label, NULL,
CachedSize AS [Cached Size (MB)]
--CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS [Buffer Pool Percent]
FROM BufferPoolUsage OPTION (RECOMPILE);
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--Database Buffer Cache Size (%)
SET @Label = 'Buffer Cache Size (%)';
SET @LabelInt = 56;
BEGIN TRY;
WITH BufferPoolUsage
AS
(SELECT DB_NAME(database_id) AS [DBName],
CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2)) AS [CachedSize]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id))
INSERT INTO @Results
SELECT @CheckDate, @Server, DBName, @LabelInt, @Label, NULL,
--CachedSize AS [Cached Size (MB)]
CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS [Buffer Pool Percent]
FROM BufferPoolUsage OPTION (RECOMPILE);
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--DB File Average Stalls (Read)
SET @Label = 'File Average Stalls (Read)';
SET @LabelInt = 57;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, DB_NAME(fs.database_id), @LabelInt, @Label, mf.name
,CAST(fs.io_stall_read_ms/(1.0 + fs.num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms]
--,CAST(fs.io_stall_write_ms/(1.0 + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms]
--,CAST((fs.io_stall_read_ms + fs.io_stall_write_ms)/(1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]
--, fs.io_stall_read_ms
--, fs.num_of_reads
--, fs.io_stall_write_ms
--, fs.num_of_writes
--, fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls]
--, fs.num_of_reads + fs.num_of_writes AS [total_io]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--DB File Average Stalls (Write)
SET @Label = 'File Average Stalls (Write)';
SET @LabelInt = 58;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, DB_NAME(fs.database_id), @LabelInt, @Label, mf.name
--,CAST(fs.io_stall_read_ms/(1.0 + fs.num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms]
,CAST(fs.io_stall_write_ms/(1.0 + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms]
--,CAST((fs.io_stall_read_ms + fs.io_stall_write_ms)/(1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]
--, fs.io_stall_read_ms
--, fs.num_of_reads
--, fs.io_stall_write_ms
--, fs.num_of_writes
--, fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls]
--, fs.num_of_reads + fs.num_of_writes AS [total_io]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--DB File Average Stalls (Overall)
SET @Label = 'File Average Stalls (Overall)';
SET @LabelInt = 59;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, DB_NAME(fs.database_id), @LabelInt, @Label, mf.name
--,CAST(fs.io_stall_read_ms/(1.0 + fs.num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms]
--,CAST(fs.io_stall_write_ms/(1.0 + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms]
,CAST((fs.io_stall_read_ms + fs.io_stall_write_ms)/(1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]
--, fs.io_stall_read_ms
--, fs.num_of_reads
--, fs.io_stall_write_ms
--, fs.num_of_writes
--, fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls]
--, fs.num_of_reads + fs.num_of_writes AS [total_io]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--DB File Number of Reads
SET @Label = 'File Number of Reads';
SET @LabelInt = 60;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, db_name(mf.database_id), @LabelInt, @Label, mf.name
--mf.database_id [DBID]
--, mf.file_id FileID
--, db_name(mf.database_id) as DBName
--, mf.name LogicalName
--, RIGHT(mf.physical_name, (PATINDEX('%\%', REVERSE(mf.physical_name)))-1) PhysicalName
--, LEFT(mf.physical_name, 1) DriveLetter
--, mf.type_desc
--, vfs.num_of_writes
--, vfs.num_of_bytes_written
--, vfs.io_stall_write_ms
, vfs.num_of_reads
--, vfs.num_of_bytes_read
--, vfs.io_stall_read_ms
--, vfs.io_stall
--, vfs.size_on_disk_bytes
FROM sys.master_files mf
INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) vfs
ON mf.database_id=vfs.database_id and mf.file_id=vfs.file_id
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


--DB File Number of Bytes Read
SET @Label = 'File Number of Bytes Read';
SET @LabelInt = 61;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, db_name(mf.database_id), @LabelInt, @Label, mf.name
--mf.database_id [DBID]
--, mf.file_id FileID
--, db_name(mf.database_id) as DBName
--, mf.name LogicalName
--, RIGHT(mf.physical_name, (PATINDEX('%\%', REVERSE(mf.physical_name)))-1) PhysicalName
--, LEFT(mf.physical_name, 1) DriveLetter
--, mf.type_desc
--, vfs.num_of_writes
--, vfs.num_of_bytes_written
--, vfs.io_stall_write_ms
--, vfs.num_of_reads
, vfs.num_of_bytes_read
--, vfs.io_stall_read_ms
--, vfs.io_stall
--, vfs.size_on_disk_bytes
FROM sys.master_files mf
INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) vfs
ON mf.database_id=vfs.database_id and mf.file_id=vfs.file_id
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


-- DB File Number of Writes
SET @Label = 'File Number of Writes';
SET @LabelInt = 62;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, db_name(mf.database_id), @LabelInt, @Label, mf.name
--mf.database_id [DBID]
--, mf.file_id FileID
--, db_name(mf.database_id) as DBName
--, mf.name LogicalName
--, RIGHT(mf.physical_name, (PATINDEX('%\%', REVERSE(mf.physical_name)))-1) PhysicalName
--, LEFT(mf.physical_name, 1) DriveLetter
--, mf.type_desc
, vfs.num_of_writes
--, vfs.num_of_bytes_written
--, vfs.io_stall_write_ms
--, vfs.num_of_reads
--, vfs.num_of_bytes_read
--, vfs.io_stall_read_ms
--, vfs.io_stall
--, vfs.size_on_disk_bytes
FROM sys.master_files mf
INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) vfs
ON mf.database_id=vfs.database_id and mf.file_id=vfs.file_id
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


-- DB File Number of Bytes Written
SET @Label = 'File Number of Bytes Written';
SET @LabelInt = 63;
BEGIN TRY;
INSERT INTO @Results
SELECT @CheckDate, @Server, db_name(mf.database_id), @LabelInt, @Label, mf.name
--mf.database_id [DBID]
--, mf.file_id FileID
--, db_name(mf.database_id) as DBName
--, mf.name LogicalName
--, RIGHT(mf.physical_name, (PATINDEX('%\%', REVERSE(mf.physical_name)))-1) PhysicalName
--, LEFT(mf.physical_name, 1) DriveLetter
--, mf.type_desc
--, vfs.num_of_writes
, vfs.num_of_bytes_written
--, vfs.io_stall_write_ms
--, vfs.num_of_reads
--, vfs.num_of_bytes_read
--, vfs.io_stall_read_ms
--, vfs.io_stall
--, vfs.size_on_disk_bytes
FROM sys.master_files mf
INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) vfs
ON mf.database_id=vfs.database_id and mf.file_id=vfs.file_id
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH


-- Logspace Usage
SET @Label = 'Log Space Usage %';
SET @LabelInt = 64;
BEGIN TRY;
DECLARE @LogSpace TABLE (DBName VARCHAR(255), LogSizeMB DECIMAL(18,2), LogUsedPercent DECIMAL(18,2), Status INT)
INSERT INTO @LogSpace
EXEC('DBCC SQLPERF(LOGSPACE);');
INSERT INTO @Results
SELECT @CheckDate, @Server, DBName, @LabelInt, @Label, NULL, LogUsedPercent
FROM @LogSpace;
END TRY
BEGIN CATCH
SET @Error_Message = ERROR_MESSAGE();
INSERT INTO @Results
SELECT @CheckDate, @Server, NULL, @LabelInt, @Label, 'Error', LEFT(@Error_Message, 255)
END CATCH



--#################################
--List out the Results:
SELECT * FROM @Results ORDER BY LabelInt;



/* Show Recovery mode FULL with no recent Transaction Log Backup
SELECT R1.CheckDate, R1.[Server], R1.[Database], R1.Label, R1.Value RecoveryMode, R2.Value LastTrnBackup
FROM @Results R1
LEFT JOIN @Results R2 ON R1.CheckDate = R2.CheckDate
AND R1.[Server] = R2.[Server]
AND R1.[Database] = R2.[Database]
AND R2.LabelInt = 13 --Last Transaction Log Backup
WHERE R1.LabelInt = 9 --Recovery Mode
AND R1.Value = 'Full'
AND ISNULL(CAST(R2.Value AS SMALLDATETIME), @CheckDate-1) < DATEADD(hh, -1, @CheckDate)
*/




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009