Run a health check report
/*
EXEC master.dbo.sp_Send_SQLHealthCheck_Report
@MailProfile = 'Your_Database_Mail_Profile_Name',
@Recipients = 'dba-team@company.com';
*/
Azure administration info collected from different sources.
/*
EXEC master.dbo.sp_Send_SQLHealthCheck_Report
@MailProfile = 'Your_Database_Mail_Profile_Name',
@Recipients = 'dba-team@company.com';
*/
Here is the script.
/*
USE master;
GO
IF OBJECT_ID('dbo.sp_Send_SQLHealthCheck_Report', 'P') IS NOT NULL
DROP PROCEDURE dbo.sp_Send_SQLHealthCheck_Report;
GO
CREATE PROCEDURE dbo.sp_Send_SQLHealthCheck_Report
@MailProfile NVARCHAR(128) = 'your_mail_profile', -- Replace with your DB Mail Profile
@Recipients NVARCHAR(MAX) = 'dba-team@company.com' -- Replace with target email address
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--------------------------------------------------------------------------------
-- 1. REUSABLE INLINE CSS STYLES
--------------------------------------------------------------------------------
DECLARE @TableStyle VARCHAR(250) = 'width: 100%; border-collapse: collapse; margin-bottom: 25px; font-family: Segoe UI, Arial, sans-serif; font-size: 12px; text-align: left;';
DECLARE @ThStyle VARCHAR(200) = 'background-color: #0f172a; color: #ffffff; padding: 9px 10px; border: 1px solid #0f172a; font-weight: 600;';
DECLARE @TdStyle VARCHAR(200) = 'padding: 8px 10px; border-bottom: 1px solid #e2e8f0; color: #333333;';
DECLARE @TdRightStyle VARCHAR(200)= 'padding: 8px 10px; border-bottom: 1px solid #e2e8f0; color: #333333; text-align: right;';
DECLARE @BadgeGreen VARCHAR(200) = 'background-color: #d1fae5; color: #065f46; font-weight: bold; border-radius: 12px; padding: 3px 10px; text-align: center; display: inline-block;';
DECLARE @BadgeYellow VARCHAR(200)= 'background-color: #fef3c7; color: #92400e; font-weight: bold; border-radius: 12px; padding: 3px 10px; text-align: center; display: inline-block;';
DECLARE @BadgeRed VARCHAR(200) = 'background-color: #fee2e2; color: #991b1b; font-weight: bold; border-radius: 12px; padding: 3px 10px; text-align: center; display: inline-block;';
DECLARE @GetDate DATETIME = GETDATE();
DECLARE @Subject NVARCHAR(256) = 'SQL Server Health Check Report - ' + @@SERVERNAME;
--------------------------------------------------------------------------------
-- 2. DATA COLLECTION: SERVER DETAILS
--------------------------------------------------------------------------------
DECLARE @Hostname NVARCHAR(128) = CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(128));
DECLARE @InstanceName NVARCHAR(128) = @@SERVERNAME;
DECLARE @Edition NVARCHAR(128) = CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128));
DECLARE @BuildNumber NVARCHAR(128) = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
DECLARE @IsCluster INT = CAST(SERVERPROPERTY('IsClustered') AS INT);
DECLARE @CurrentNode NVARCHAR(128) = ISNULL(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(128)), 'N/A');
DECLARE @SQLRestart DATETIME;
DECLARE @UptimeHours INT;
SELECT @SQLRestart = sqlserver_start_time FROM sys.dm_os_sys_info;
SET @UptimeHours = DATEDIFF(HOUR, @SQLRestart, @GetDate);
DECLARE @ServerBadgeStyle VARCHAR(200) = CASE WHEN @UptimeHours < 24 THEN @BadgeRed ELSE @BadgeGreen END;
DECLARE @ServerStatusText VARCHAR(50) = CASE WHEN @UptimeHours < 24 THEN 'CheckNow' ELSE 'Relax' END;
--------------------------------------------------------------------------------
-- 3. DATA COLLECTION: DATABASES & BACKUPS
--------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#BackupInfo') IS NOT NULL DROP TABLE #BackupInfo;
SELECT
d.name AS DBName,
d.recovery_model_desc,
d.state_desc,
MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFull,
DATEDIFF(DAY, MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END), GETDATE()) AS FullAgeDays,
MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiff,
DATEDIFF(HOUR, MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END), GETDATE()) AS DiffAgeHours,
MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLog,
DATEDIFF(MINUTE, MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END), GETDATE()) AS LogAgeMinutes
INTO #BackupInfo
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE d.name NOT IN ('tempdb')
GROUP BY d.name, d.recovery_model_desc, d.state_desc;
--------------------------------------------------------------------------------
-- 4. DATA COLLECTION: DISKS
--------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#DiskInfo') IS NOT NULL DROP TABLE #DiskInfo;
CREATE TABLE #DiskInfo (
VolumeName VARCHAR(128),
TotalGB DECIMAL(10,2),
UsedGB DECIMAL(10,2),
FreeGB DECIMAL(10,2),
FreePct DECIMAL(5,2)
);
INSERT INTO #DiskInfo
SELECT DISTINCT
volume_mount_point,
CAST(total_bytes / 1073741824.0 AS DECIMAL(10,2)),
CAST((total_bytes - available_bytes) / 1073741824.0 AS DECIMAL(10,2)),
CAST(available_bytes / 1073741824.0 AS DECIMAL(10,2)),
CAST((available_bytes * 100.0 / total_bytes) AS DECIMAL(5,2))
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);
--------------------------------------------------------------------------------
-- 5. CALCULATE EXECUTIVE KPI COUNTS
--------------------------------------------------------------------------------
DECLARE @OfflineOrRestoringCount INT = 0;
DECLARE @BackupAlertCount INT = 0;
DECLARE @DiskAlertCount INT = 0;
DECLARE @LowestDiskDrive VARCHAR(128) = '';
DECLARE @LowestDiskPct DECIMAL(5,2) = 100.0;
SELECT @OfflineOrRestoringCount = COUNT(*) FROM sys.databases WHERE state_desc <> 'ONLINE' AND name NOT IN ('tempdb');
SELECT @BackupAlertCount = COUNT(*)
FROM #BackupInfo
WHERE FullAgeDays > 7 OR FullAgeDays IS NULL
OR DiffAgeHours > 24
OR (recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL));
SELECT @DiskAlertCount = COUNT(*) FROM #DiskInfo WHERE FreePct < 25;
SELECT TOP 1 @LowestDiskDrive = VolumeName, @LowestDiskPct = FreePct
FROM #DiskInfo
ORDER BY FreePct ASC;
--------------------------------------------------------------------------------
-- 6. BUILD HTML: EXECUTIVE SUMMARY CARDS (TOP SECTION)
--------------------------------------------------------------------------------
DECLARE @SummaryHTML NVARCHAR(MAX) = N'
<div style="display: flex; gap: 12px; margin-bottom: 25px;">
<div style="flex: 1; border: 1px solid ' + CASE WHEN @UptimeHours < 24 THEN '#fecaca' ELSE '#bbf7d0' END + '; background-color: ' + CASE WHEN @UptimeHours < 24 THEN '#fef2f2' ELSE '#f0fdf4' END + '; border-radius: 8px; padding: 12px 15px; display: flex; align-items: center; gap: 12px;">
<div style="font-size: 22px;">' + CASE WHEN @UptimeHours < 24 THEN '⚠️' ELSE '✅' END + '</div>
<div>
<div style="font-size: 11px; text-transform: uppercase; font-weight: 700; color: #475569;">SQL Service</div>
<div style="font-size: 13px; font-weight: 700; color: ' + CASE WHEN @UptimeHours < 24 THEN '#dc2626' ELSE '#15803d' END + ';">' + CASE WHEN @UptimeHours < 24 THEN 'Rebooted < 24h' ELSE 'Running Normal' END + '</div>
</div>
</div>
<div style="flex: 1; border: 1px solid ' + CASE WHEN @OfflineOrRestoringCount > 0 THEN '#fde68a' ELSE '#bbf7d0' END + '; background-color: ' + CASE WHEN @OfflineOrRestoringCount > 0 THEN '#fffbeb' ELSE '#f0fdf4' END + '; border-radius: 8px; padding: 12px 15px; display: flex; align-items: center; gap: 12px;">
<div style="font-size: 22px;">' + CASE WHEN @OfflineOrRestoringCount > 0 THEN '⚠️' ELSE '✅' END + '</div>
<div>
<div style="font-size: 11px; text-transform: uppercase; font-weight: 700; color: #475569;">Database State</div>
<div style="font-size: 13px; font-weight: 700; color: ' + CASE WHEN @OfflineOrRestoringCount > 0 THEN '#b45309' ELSE '#15803d' END + ';">' + CASE WHEN @OfflineOrRestoringCount > 0 THEN CAST(@OfflineOrRestoringCount AS VARCHAR(5)) + ' DB Not Online' ELSE 'All Online' END + '</div>
</div>
</div>
<div style="flex: 1; border: 1px solid ' + CASE WHEN @BackupAlertCount > 0 THEN '#fecaca' ELSE '#bbf7d0' END + '; background-color: ' + CASE WHEN @BackupAlertCount > 0 THEN '#fef2f2' ELSE '#f0fdf4' END + '; border-radius: 8px; padding: 12px 15px; display: flex; align-items: center; gap: 12px;">
<div style="font-size: 22px;">' + CASE WHEN @BackupAlertCount > 0 THEN '🚨' ELSE '✅' END + '</div>
<div>
<div style="font-size: 11px; text-transform: uppercase; font-weight: 700; color: #475569;">Backup Status</div>
<div style="font-size: 13px; font-weight: 700; color: ' + CASE WHEN @BackupAlertCount > 0 THEN '#dc2626' ELSE '#15803d' END + ';">' + CASE WHEN @BackupAlertCount > 0 THEN CAST(@BackupAlertCount AS VARCHAR(5)) + ' Overdue' ELSE 'All Backups OK' END + '</div>
</div>
</div>
<div style="flex: 1; border: 1px solid ' + CASE WHEN @LowestDiskPct < 15 THEN '#fecaca' WHEN @LowestDiskPct < 25 THEN '#fde68a' ELSE '#bbf7d0' END + '; background-color: ' + CASE WHEN @LowestDiskPct < 15 THEN '#fef2f2' WHEN @LowestDiskPct < 25 THEN '#fffbeb' ELSE '#f0fdf4' END + '; border-radius: 8px; padding: 12px 15px; display: flex; align-items: center; gap: 12px;">
<div style="font-size: 22px;">💾</div>
<div>
<div style="font-size: 11px; text-transform: uppercase; font-weight: 700; color: #475569;">Disk Space</div>
<div style="font-size: 13px; font-weight: 700; color: ' + CASE WHEN @LowestDiskPct < 15 THEN '#dc2626' WHEN @LowestDiskPct < 25 THEN '#b45309' ELSE '#15803d' END + ';">' + @LowestDiskDrive + ' (' + CAST(@LowestDiskPct AS VARCHAR(10)) + '% Free)</div>
</div>
</div>
</div>';
--------------------------------------------------------------------------------
-- 7. BUILD HTML SECTIONS (1. SERVER, 2. DB, 3. BACKUPS, 4. DISK)
--------------------------------------------------------------------------------
-- 1. Server Details
DECLARE @ServerHTML NVARCHAR(MAX) = N'
<div style="font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #0284c7; padding-left: 8px; margin-bottom: 10px;">1. Server Details</div>
<table style="' + @TableStyle + '">
<thead>
<tr>
<th style="' + @ThStyle + '">GetDate</th>
<th style="' + @ThStyle + '">Hostname</th>
<th style="' + @ThStyle + '">SQL Instance</th>
<th style="' + @ThStyle + '">Edition</th>
<th style="' + @ThStyle + '">Build Number</th>
<th style="' + @ThStyle + '">Is Cluster</th>
<th style="' + @ThStyle + '">Current Node</th>
<th style="' + @ThStyle + '">SQL Restart</th>
<th style="' + @ThStyle + ' text-align: right;">Uptime (Hrs)</th>
<th style="' + @ThStyle + ' text-align: center;">Status</th>
</tr>
</thead>
<tbody>
<tr>
<td style="' + @TdStyle + '">' + CONVERT(VARCHAR(16), @GetDate, 120) + '</td>
<td style="' + @TdStyle + '">' + @Hostname + '</td>
<td style="' + @TdStyle + ' font-weight: 600;">' + @InstanceName + '</td>
<td style="' + @TdStyle + '">' + @Edition + '</td>
<td style="' + @TdStyle + '">' + @BuildNumber + '</td>
<td style="' + @TdStyle + '">' + CASE WHEN @IsCluster = 1 THEN 'Yes' ELSE 'No' END + '</td>
<td style="' + @TdStyle + '">' + @CurrentNode + '</td>
<td style="' + @TdStyle + '">' + CONVERT(VARCHAR(16), @SQLRestart, 120) + '</td>
<td style="' + @TdRightStyle + '">' + CAST(@UptimeHours AS VARCHAR(10)) + '</td>
<td style="' + @TdStyle + ' text-align: center;"><span style="' + @ServerBadgeStyle + '">' + @ServerStatusText + '</span></td>
</tr>
</tbody>
</table>';
-- 2. Database Details
DECLARE @DbHTML NVARCHAR(MAX) = N'
<div style="font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #0284c7; padding-left: 8px; margin-bottom: 10px;">2. Database Details</div>
<table style="' + @TableStyle + '">
<thead>
<tr>
<th style="' + @ThStyle + '">Database</th>
<th style="' + @ThStyle + '">Recovery Model</th>
<th style="' + @ThStyle + '">State</th>
<th style="' + @ThStyle + ' text-align: center;">Status</th>
</tr>
</thead>
<tbody>';
SELECT @DbHTML = @DbHTML +
'<tr style="' + CASE WHEN state_desc <> 'ONLINE' THEN 'background-color: #fef2f2; border-bottom: 1px solid #fca5a5;' ELSE '' END + '">
<td style="' + @TdStyle + ' font-weight: 600;">' + name + '</td>
<td style="' + @TdStyle + '">' + recovery_model_desc + '</td>
<td style="' + @TdStyle + ' font-weight: ' + CASE WHEN state_desc <> 'ONLINE' THEN '700; color: #dc2626;' ELSE '400;' END + '">' + state_desc + '</td>
<td style="' + @TdStyle + ' text-align: center;"><span style="' +
CASE WHEN state_desc = 'ONLINE' THEN @BadgeGreen ELSE @BadgeRed END + '">' +
CASE WHEN state_desc = 'ONLINE' THEN 'Relax' ELSE 'CheckNow' END + '</span></td>
</tr>'
FROM sys.databases
WHERE name NOT IN ('tempdb')
ORDER BY name;
SET @DbHTML = @DbHTML + '</tbody></table>';
-- 3. Database Backup Details
DECLARE @BackupHTML NVARCHAR(MAX) = N'
<div style="font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #0284c7; padding-left: 8px; margin-bottom: 10px;">3. Database Backup Details</div>
<table style="' + @TableStyle + '">
<thead>
<tr>
<th style="' + @ThStyle + '">Database</th>
<th style="' + @ThStyle + '">Last Full</th>
<th style="' + @ThStyle + ' text-align: right;">Full Age</th>
<th style="' + @ThStyle + '">Last Diff</th>
<th style="' + @ThStyle + ' text-align: right;">Diff Age</th>
<th style="' + @ThStyle + '">Last Log</th>
<th style="' + @ThStyle + ' text-align: right;">Log Age</th>
<th style="' + @ThStyle + ' text-align: center;">Status</th>
</tr>
</thead>
<tbody>';
SELECT @BackupHTML = @BackupHTML +
'<tr style="' + CASE WHEN FullAgeDays > 7 OR FullAgeDays IS NULL OR DiffAgeHours > 24 OR (recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL)) THEN 'background-color: #fef2f2; border-bottom: 1px solid #fca5a5;' ELSE '' END + '">
<td style="' + @TdStyle + ' font-weight: 600;">' + DBName + '</td>
<td style="' + @TdStyle + '">' + ISNULL(CONVERT(VARCHAR(16), LastFull, 120), '<span style="color: #94a3b8;">Never</span>') + '</td>
<td style="' + @TdRightStyle + '">' + ISNULL(CAST(FullAgeDays AS VARCHAR(10)) + ' Days', 'N/A') + '</td>
<td style="' + @TdStyle + '">' + ISNULL(CONVERT(VARCHAR(16), LastDiff, 120), '<span style="color: #94a3b8;">Never</span>') + '</td>
<td style="' + @TdRightStyle + '">' + ISNULL(CAST(DiffAgeHours AS VARCHAR(10)) + ' Hrs', 'N/A') + '</td>
<td style="' + @TdStyle + '">' + ISNULL(CONVERT(VARCHAR(16), LastLog, 120), '<span style="color: #94a3b8;">Never</span>') + '</td>
<td style="' + @TdRightStyle + '">' +
CASE
WHEN recovery_model_desc = 'SIMPLE' THEN 'N/A'
WHEN LogAgeMinutes IS NULL THEN 'N/A'
WHEN LogAgeMinutes < 60 THEN CAST(LogAgeMinutes AS VARCHAR(10)) + ' Min'
ELSE CAST(LogAgeMinutes / 60 AS VARCHAR(10)) + ' Hrs'
END + '</td>
<td style="' + @TdStyle + ' text-align: center;"><span style="' +
CASE
WHEN FullAgeDays > 7 OR FullAgeDays IS NULL THEN @BadgeRed
WHEN DiffAgeHours > 24 THEN @BadgeRed
WHEN recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL) THEN @BadgeRed
ELSE @BadgeGreen
END + '">' +
CASE
WHEN FullAgeDays > 7 OR FullAgeDays IS NULL THEN 'CheckNow'
WHEN DiffAgeHours > 24 THEN 'CheckNow'
WHEN recovery_model_desc <> 'SIMPLE' AND (LogAgeMinutes > 60 OR LogAgeMinutes IS NULL) THEN 'CheckNow'
ELSE 'Relax'
END + '</span></td>
</tr>'
FROM #BackupInfo
ORDER BY DBName;
SET @BackupHTML = @BackupHTML + '</tbody></table>';
-- 4. Disk Details (With Dynamic Visual Bar Chart)
DECLARE @DiskHTML NVARCHAR(MAX) = N'
<div style="font-size: 15px; font-weight: 700; color: #0f172a; border-left: 4px solid #0284c7; padding-left: 8px; margin-bottom: 10px;">4. Disk Details</div>
<table style="' + @TableStyle + '">
<thead>
<tr>
<th style="' + @ThStyle + '">Drive</th>
<th style="' + @ThStyle + ' text-align: right;">Total (GB)</th>
<th style="' + @ThStyle + ' text-align: right;">Used (GB)</th>
<th style="' + @ThStyle + ' text-align: right;">Free (GB)</th>
<th style="' + @ThStyle + ' width: 180px;">Free % Visual Bar</th>
<th style="' + @ThStyle + ' text-align: center;">Status</th>
</tr>
</thead>
<tbody>';
SELECT @DiskHTML = @DiskHTML +
'<tr style="' + CASE WHEN FreePct < 15 THEN 'background-color: #fef2f2; border-bottom: 1px solid #fca5a5;' WHEN FreePct < 25 THEN 'background-color: #fffbeb; border-bottom: 1px solid #fde68a;' ELSE '' END + '">
<td style="' + @TdStyle + ' font-weight: 600;">' + VolumeName + '</td>
<td style="' + @TdRightStyle + '">' + CAST(TotalGB AS VARCHAR(20)) + '</td>
<td style="' + @TdRightStyle + '">' + CAST(UsedGB AS VARCHAR(20)) + '</td>
<td style="' + @TdRightStyle + ' font-weight: ' + CASE WHEN FreePct < 25 THEN '700;' ELSE '400;' END + '">' + CAST(FreeGB AS VARCHAR(20)) + '</td>
<td style="' + @TdStyle + '">
<div style="display: flex; align-items: center; gap: 8px;">
<div style="flex: 1; background-color: #e2e8f0; border-radius: 4px; height: 10px; overflow: hidden;">
<div style="width: ' + CAST(FreePct AS VARCHAR(10)) + '%; background-color: ' +
CASE WHEN FreePct < 15 THEN '#ef4444' WHEN FreePct < 25 THEN '#f59e0b' ELSE '#10b981' END +
'; height: 100%;"></div>
</div>
<span style="font-weight: 700; min-width: 35px; color: ' +
CASE WHEN FreePct < 15 THEN '#dc2626' WHEN FreePct < 25 THEN '#b45309' ELSE '#059669' END +
';">' + CAST(FreePct AS VARCHAR(10)) + '%</span>
</div>
</td>
<td style="' + @TdStyle + ' text-align: center;"><span style="' +
CASE WHEN FreePct < 15 THEN @BadgeRed WHEN FreePct < 25 THEN @BadgeYellow ELSE @BadgeGreen END + '">' +
CASE WHEN FreePct < 25 THEN 'CheckNow' ELSE 'Relax' END + '</span></td>
</tr>'
FROM #DiskInfo
ORDER BY VolumeName;
SET @DiskHTML = @DiskHTML + '</tbody></table>';
--------------------------------------------------------------------------------
-- 8. ASSEMBLE HTML DOCUMENT AND SEND EMAIL
--------------------------------------------------------------------------------
DECLARE @FullHTMLBody NVARCHAR(MAX);
SET @FullHTMLBody = N'<!DOCTYPE html><html><body style="font-family: Segoe UI, Helvetica, Arial, sans-serif; background-color: #f8fafc; margin: 0; padding: 20px;">' +
N'<div style="max-width: 1000px; margin: auto; background-color: #ffffff; border-radius: 12px; border: 1px solid #e2e8f0; padding: 25px; box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.05);">' +
N'<div style="text-align: center; border-bottom: 2px solid #f1f5f9; padding-bottom: 15px; margin-bottom: 20px;">' +
N'<div style="font-size: 24px; color: #0f172a; font-weight: 800; letter-spacing: -0.5px;">SQL Server Health Check Report</div>' +
N'<div style="font-size: 13px; color: #64748b; margin-top: 4px;">Generated on: <b>' + CONVERT(VARCHAR(19), @GetDate, 120) + N'</b> | SQL Instance: <b style="color: #0f172a;">' + @InstanceName + N'</b></div>' +
N'</div>' +
@SummaryHTML +
@ServerHTML +
@DbHTML +
@BackupHTML +
@DiskHTML +
N'<div style="text-align: center; color: #94a3b8; font-size: 11px; margin-top: 20px; border-top: 1px solid #f1f5f9; padding-top: 15px;">This is an automated system health notification. Please do not reply directly to this email.</div>' +
N'</div></body></html>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @MailProfile,
@recipients = @Recipients,
@subject = @Subject,
@body = @FullHTMLBody,
@body_format = 'HTML';
-- Clean Up Temp Tables
IF OBJECT_ID('tempdb..#BackupInfo') IS NOT NULL DROP TABLE #BackupInfo;
IF OBJECT_ID('tempdb..#DiskInfo') IS NOT NULL DROP TABLE #DiskInfo;
END;
GO
*/
Query to get top 30 high CPU quries
SELECT TOP 30
DB_NAME(st.dbid) AS 'Database Name',
SERVERPROPERTY('ServerName') AS 'Server Name',
s.session_id AS 'Session ID',
s.login_name AS 'Login Name',
getdate() as [TImenow],
qs.creation_time AS 'Creation Time',
qs.execution_count AS 'Execution Count',
qs.total_worker_time AS 'Total CPU Time (ms)',
qs.total_worker_time / qs.execution_count AS 'Average CPU Time (ms)',
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 'Query Text'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
INNER JOIN sys.dm_exec_requests AS r ON qs.plan_handle = r.plan_handle
INNER JOIN sys.dm_exec_sessions AS s ON r.session_id = s.session_id
ORDER BY qs.total_worker_time DESC;
CPU details complete query
SELECT
DB_NAME(r.database_id) AS 'Database Name',
s.session_id,
r.STATUS,
r.blocking_session_id AS 'Blocked By',
r.wait_type,
r.wait_resource,
r.wait_time / (1000.0) AS 'Wait Time (in Sec)',
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000.0) AS 'Elapsed Time (in Sec)',
SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS statement_text,
COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.host_process_id,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
INNER JOIN sys.databases AS d ON r.database_id = d.database_id
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC, r.STATUS, r.blocking_session_id, s.session_id;
https://blogs.msdn.microsoft.com/docast/2017/07/30/sql-high-cpu-troubleshooting-checklist/
Email::: Microsoft findings
Below queries were used on siebeldb database high CPU issue to findout which query being used, these queries given by Microsoft
We can find what is the exact query behind fetch cursor quey.
======== For transaction creation time
SELECT c.session_id, es.program_name, es.login_name, es.host_name, DB_NAME(es.database_id) AS DatabaseName, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (0) c
LEFT JOIN sys.dm_exec_sessions AS es ON c.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t
================= Plan handle with SQL statement
SELECT er.sql_handle, ec.sql_handle,
SUBSTRING(ers.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(ers.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS statement_text_er,
SUBSTRING(ecs.text, (ec.statement_start_offset/2)+1,
((CASE ec.statement_end_offset
WHEN -1 THEN DATALENGTH(ecs.text)
ELSE ec.statement_end_offset
END - ec.statement_start_offset)/2) + 1) AS statement_text_ec
FROM sys.dm_exec_requests er cross apply sys.dm_exec_cursors(er.session_id) ec
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) ers
CROSS APPLY sys.dm_exec_sql_text(ec.sql_handle) ecs
======================================Creation time =====
SELECT creation_time,
cursor_id,
c.session_id,
c.properties,
c.creation_time,
c.is_open,
SUBSTRING(st.TEXT, ( c.statement_start_offset / 2) + 1, (
( CASE c.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
ELSE c.statement_end_offset
END - c.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.Dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
CROSS apply sys.Dm_exec_sql_text(c.sql_handle) AS st
GO
========================================================================================================
Below queries were used during siebeldb CPU full issue by Microsoft
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM SYS.SYSPROCESSES R CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 2) + CASE WHEN (LEN (LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + CAST (SPID AS NVARCHAR (10)) + ' ' + BATCH AS BLOCKING_TREE FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO
==================================================
Top CPU
SELECT s.session_id
,r.STATUS
,r.blocking_session_id 'blocked by'
,r.wait_type
,wait_resource
,r.wait_time / (1000.0) 'Wait Time (in Sec)'
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)'
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
,s.host_process_id
,s.last_request_end_time
,s.login_time
,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id
=========================================
select case
when cpu_count / hyperthread_ratio > 8 then 8
else cpu_count / hyperthread_ratio
end as optimal_maxdop_setting
from sys.dm_os_sys_info;
===========================================
TOP CPU
SELECT
r.session_id,
se.host_name,
se.login_name,
Db_name(r.database_id) AS dbname,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time as duration_ms,
r.reads,
r.logical_reads,
r.writes,t.pending_io_count as "Physical_IO_Performed",
t.pending_io_byte_count as "Physical_IO_Bytes",
r.row_count as rows,r.granted_query_memory*8 as Granted_query_memory_KB ,
t.task_state as Task_Status,
t.scheduler_id,
r.blocking_session_id, r.wait_type,r.wait_time,r.wait_resource,r.lock_timeout,r.open_transaction_count,r.transaction_isolation_level,r.executing_managed_code,
tsu.database_id,tsu.internal_objects_alloc_page_count,tsu.user_objects_alloc_page_count,tsu.internal_objects_dealloc_page_count,tsu.user_objects_dealloc_page_count,
s.TEXT sql_text,
p.query_plan query_plan,
sql_CURSORSQL.text as Cursor_Text,
SQL_CURSORPLAN.query_plan as Cursor_Plan,
CURRENT_TIMESTAMP as currenttime
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions se
ON r.session_id = se.session_id
INNER JOIN sys.dm_os_tasks t on t.request_id=r.request_id and t.session_id=se.session_id and r.scheduler_id=t.scheduler_id
Inner join sys.dm_db_task_space_usage tsu on tsu.request_id = r.request_id and tsu.session_id=r.session_id and tsu.exec_context_id = t.exec_context_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
OUTER APPLY sys.dm_exec_cursors(r.session_id) AS SQL_CURSORS
OUTER APPLY sys.dm_exec_sql_text(SQL_CURSORS.sql_handle) AS SQL_CURSORSQL
LEFT JOIN sys.dm_exec_query_stats AS SQL_CURSORSTATS
ON SQL_CURSORSTATS.sql_handle = SQL_CURSORS.sql_handle
OUTER APPLY sys.dm_exec_query_plan(SQL_CURSORSTATS.plan_handle) AS SQL_CURSORPLAN
WHERE -- r.session_id <> @@SPID
se.is_user_process = 1
order by cpu_time desc
======================================================
MISSING INDEX
-------------
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
=====================================
Select * from sys.dm_exec_requests where wait_time > 0 and wait_resource like '2:%'
ORder by wait_time desc
========================================
FIND INDEX FRAGMET
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
https://www.datavail.com/blog/how-to-stripe-your-backups-into-multiple-files/
-- Stripe the full backup files for the selected databases with date and timestamp appended to the backup files
-- ************************************************************************************************************
-- Copyright © 2016 by JP Chen of DatAvail Corporation
-- This script is free for non-commercial purposes with no warranties.
-- ************************************************************************************************************
DECLARE DBFullBackups_Cursor CURSOR
FOR
-- Pick the databases you wish to run full backups
SELECT db.name
FROM sys.databases db
WHERE db.name IN ('VIT_GLOBAL_TOOLBOX')
OPEN DBFullBackups_Cursor
DECLARE @db VARCHAR(125);
DECLARE @BackupPath VARCHAR(525);
DECLARE @BackupCmd VARCHAR(8000);
DECLARE @DeleteDate DATETIME = DATEADD(hh,-22,GETDATE());
-- Check to make sure the path exists
SET @BackupPath = 'W:\MSSQL12.SQL1\MSSQL\DATA\UserDB\Test\' -- specify your own backup folder
FETCH NEXT
FROM DBFullBackups_Cursor
INTO @db
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
-- Backup command
SET @BackupCmd = 'BACKUP DATABASE [' + @db + '] TO
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_1.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_2.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_3.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_4.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_5.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_6.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_7.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_8.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_9.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_10.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_11.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_12.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_13.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_14.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_15.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_16.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_17.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_18.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_19.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_20.bak'''
-- Print the backup command
PRINT @BackupCmd
-- Run the backup command
EXEC (@BackupCmd)
-- Fetch the next database
FETCH NEXT
FROM DBFullBackups_Cursor
INTO @db
END
EXEC master.sys.xp_delete_file 0,@BackupPath,'bak',@DeleteDate,0;
-- Close and deallocate the cursor
CLOSE DBFullBackups_Cursor
DEALLOCATE DBFullBackups_Cursor
GO
Query one:
DECLARE @DatabaseName VARCHAR(100) = 'VIT_GLOBAL_TOOLBOX'
DECLARE @BackupPath VARCHAR(1000) = 'H:\DIFF\LATNATIVE\'
DECLARE @BackupCmd NVARCHAR(MAX)
DECLARE @FileNumber INT = 1
WHILE @FileNumber <= 10 -- Number of backup files to create
BEGIN
DECLARE @BackupFile VARCHAR(1000) = @BackupPath + @DatabaseName + '_Diff_Backup_' + CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '') + '_Part' + CAST(@FileNumber AS VARCHAR(2)) + '.bak'
SET @BackupCmd = 'BACKUP DATABASE ' + @DatabaseName + ' ' +
'TO DISK = ''' + @BackupFile + ''' ' +
'WITH DIFFERENTIAL';
PRINT @BackupCmd
EXEC (@BackupCmd)
SET @FileNumber += 1
END
Query two:
DECLARE DBFullBackups_Cursor CURSOR
FOR
-- Pick the databases you wish to run full backups
SELECT db.name
FROM sys.databases db
WHERE db.name IN ('VIT_GLOBAL_TOOLBOX')
OPEN DBFullBackups_Cursor
DECLARE @db VARCHAR(125);
DECLARE @BackupPath VARCHAR(525);
DECLARE @BackupCmd VARCHAR(8000);
DECLARE @DeleteDate DATETIME = DATEADD(hh,-22,GETDATE());
-- Check to make sure the path exists
SET @BackupPath = 'H:\DIFF\LATNATIVE\' -- specify your own backup folder
FETCH NEXT
FROM DBFullBackups_Cursor
INTO @db
WHILE (@@FETCH_STATUS <> - 1)
BEGIN
-- Backup command
SET @BackupCmd = 'BACKUP DATABASE [' + @db + '] TO
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + 'Diff_1.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + 'Diff_2.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + 'Diff_4.bak'',
DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + 'Diff_5.bak'''
+
'WITH DIFFERENTIAL';
-- Print the backup command
PRINT @BackupCmd
-- Run the backup command
EXEC (@BackupCmd)
-- Fetch the next database
FETCH NEXT
FROM DBFullBackups_Cursor
INTO @db
END
EXEC master.sys.xp_delete_file 0,@BackupPath,'bak',@DeleteDate,0;
-- Close and deallocate the cursor
CLOSE DBFullBackups_Cursor
DEALLOCATE DBFullBackups_Cursor
GO
To shrink all log files in server at one shot
create table #dbfiles (
[Database] sysname
, Name sysname
, [Type] sysname
, [Filename] nvarchar(1024)
, Allocated int
, Used int
, Available int
)
exec sp_msforeachdb 'use [?];insert into #dbfiles
select
''?'' as [Database]
, a.Name
, dbf.type_desc as [Type]
, a.Filename
, convert(int,round(a.Size/128.000,0)) as Allocated
, convert(int,round(fileproperty(a.Name,''SpaceUsed'')/128.000,0)) as Used
, convert(int,round((a.Size-fileproperty(a.Name,''SpaceUsed''))/128.000,0)) as Available
from
dbo.sysfiles a (nolock)
inner join sys.database_files dbf (nolock)
on a.fileid = dbf.file_id
where
db_id(''?'') not in (1,2,3,4)';
select
*
, 'use [' + [Database] + ']; dbcc shrinkfile (''' + [Name] + ''', ' + cast((Used + 1) as nvarchar(16)) + ');'
from
#dbfiles where [type]='LOG'
order by
Available desc
drop table #dbfiles
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
SELECT @@ServerName AS [Instance Name], @@SERVICENAME AS Instance,GETDATE() AS TimeOfQuery,loginname AS [Login Name],createdate AS [Created Date], updatedate AS [Modified Date], accdate AS [Access Date],
CASE WHEN sysadmin = 1 THEN 'Login is a member of the sysadmin server role' ELSE 'NA' END AS SysAdmin,
((CASE WHEN hasaccess = 1 THEN 'Login has been granted access to the server' + CHAR(13)+CHAR(10) ELSE 'NA' + CHAR(13)+CHAR(10) END) +
(CASE WHEN isntname = 1 THEN 'Login is a Windows user or group' + CHAR(13)+CHAR(10) ELSE 'Login is a SQL Server login' + CHAR(13)+CHAR(10) END) +
(CASE WHEN isntgroup = 1 THEN 'Login is a Windows group' + CHAR(13)+CHAR(10) ELSE '' END) +
(CASE WHEN isntuser = 1 THEN 'Login is a Windows user' + CHAR(13)+CHAR(10) ELSE '' END) +
(CASE WHEN securityadmin = 1 THEN 'Login is a member of the securityadmin server role' + CHAR(13)+CHAR(10) ELSE '' END) +
(CASE WHEN serveradmin = 1 THEN 'Login is a member of the serveradmin fixed server role' + CHAR(13)+CHAR(10) ELSE '' END) +
(CASE WHEN setupadmin = 1 THEN 'Login is a member of the setupadmin fixed server role' + CHAR(13)+CHAR(10) ELSE '' END) +
(CASE WHEN processadmin = 1 THEN 'Login is a member of the processadmin fixed server role' + CHAR(13)+CHAR(10) ELSE '' END) +
(CASE WHEN diskadmin = 1 THEN 'Login is a member of the diskadmin fixed server role' + CHAR(13)+CHAR(10) ELSE '' END) +
(CASE WHEN dbcreator = 1 THEN 'Login is a member of the dbcreator fixed server role' + CHAR(13)+CHAR(10) ELSE '' END) +
(CASE WHEN bulkadmin = 1 THEN ' Login is a member of the bulkadmin fixed server role' + CHAR(13)+CHAR(10) ELSE '' END )) AS Description ,
'NA' as [GivenName], 'NA' as [Name], 'NA' as [Surname], 'NA' as [FullName] , 'NA' as [UserPrincipalName]
FROM syslogins INNER JOIN sys.server_principals ON server_principals.sid = syslogins.sid
WHERE syslogins.name NOT IN ('sa' , 'VCN\CS-WS-SQL-SystemAdministrators', 'VCN\CS-WS-S-SQLDBTRACK')
AND syslogins.name NOT LIKE '%NT SERVICE%' AND syslogins.name NOT LIKE '%##%' AND syslogins.name NOT LIKE '%NT AUTHORITY%'
AND syslogins.name NOT LIKE '%IT-GLB-SQL-SOX'
AND server_principals.is_disabled = 0;
Percentage query
use master;
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')
Another query
SELECT @@servername[Servername],getdate() [TimeNow],command, r.session_id, r.blocking_session_id,
s.text,
start_time,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')