Update: This was my original solution to automate the DBA Checks process. I don’t recommend people use this solution as it doesn’t scale well to a larger number of servers and the CLR function used to get drive space requires the TRUSTWORTHY property to be enabled which can be a potential security risk.
I recommend people use DBA Dash for daily DBA Checks. This gives you a central dashboard so you can see the status of all your SQL instances on a single page with drill downs for more detailed information.
Introduction
In addition to alerts I recommend running daily checks to validate that backups are running as expected, servers have sufficient disk space and agent jobs are running successfully. Although alerts provide immediate notification that there is an issue you can’t assume that everything is OK because you haven’t received an email notification.
For example, an email alert might get caught by a spam filter or matched by a rule in your email system that moves it to a different folder. It’s also possible there was a problem sending the notification email. If you are not careful with how you configure alerts you can easily get spammed by a large number of alert emails and important notifications could be lost in this flood.
You might not get a failed backup job notification if your backup job never ran – maybe the job got disabled or the SQL Agent wasn’t running. These are some of the reasons I recommend daily checks.
The solution in this post has each SQL Server send a report via email that runs various checks and highlights any issues. It’s a solution I used when I managed a smaller number of servers but as the number of instances I managed increased it became unwieldy. Depending on the number of serves you manage you might find it useful.
I now use a different solution that collects data from multiple servers in a central repository. This allows me to have a single dashboard that highlights problems across multiple servers instead of having to check individual reports for each server. I hope to share my new method at some point.
Report Sample
The image below is a sample report that was generated from a test instance of SQL Server.

Compatibility
- SQL Server 2005 (SP3)
There seems to be a bug in earlier builds related to attachments in email messages. You might get an error similar to this one:
Exception happened when running extended stored procedure ‘xp_sysmail_format_query’ in the library ‘xpstar90.dll’. SQL Server is terminating process 62. Exception type: Win32 exception; Exception code: 0xc0000005.
You can fix this by upgrading to the latest service pack or removing the code that adds the error log attachment to the email. Restarting the SQL Server Agent can also provide a temporary fix for this issue, but the problem is likely to re-occur. - SQL Server 2008
- SQL Server 2008 R2
Installation Instructions
- Create a database for DBA related tasks if one doesn’t already exist.
- Install SQLIO_fnGetDrives() CLR function. See here.
- Install DBA Checks stored procedures. (Script below)
- Make sure you have configured database mail on the SQL Server instance.
- Create a SQL Server Agent Job to run the dbo.DBAChecks stored procedure.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[DBAChecks_Backups]( @HTML varchar(max) OUT, @FullWarningThresholdDays int, @DiffWarningThresholdDays int, @TranWarningThresHoldHours int ) AS SET NOCOUNT ON /* Returns HTML for "Backups" section of DBA Checks Report */ declare @Server varchar(40) set @server = CONVERT(varchar(35), SERVERPROPERTY('machinename'))+ COALESCE('\'+ CONVERT(varchar(35), SERVERPROPERTY('instancename')),'') DECLARE @backuplog TABLE( DBServer sysname, DBName sysname, LastFullBackup datetime, LastDiffBackup datetime, LastTranBackup datetime, DiffDays int, FullDays int, TranHours int ) INSERT INTO @backuplog SELECT fullrec.server_name [DBServer], fullrec.database_name, fullrec.backup_finish_date LastFullBackup, diffrec.backup_finish_date LastDiffBackup, tranrec.backup_finish_date LastTranBackup, datediff(dd,diffrec.backup_finish_date,getdate()) DiffDays, datediff(dd,fullrec.backup_finish_date,getdate()) FullDays, datediff(hh,tranrec.backup_finish_date,getdate()) TranHours FROM msdb..backupset as fullrec left outer join msdb..backupset as tranrec on tranrec.database_name = fullrec.database_name and tranrec.server_name = fullrec.server_name and tranrec.type = 'L' and tranrec.backup_finish_date = ((select max(backup_finish_date) from msdb..backupset b2 where b2.database_name = fullrec.database_name and b2.server_name = fullrec.server_name and b2.type = 'L')) left outer join msdb..backupset as diffrec on diffrec.database_name = fullrec.database_name and diffrec.server_name = fullrec.server_name and diffrec.type = 'I' and diffrec.backup_finish_date = ((select max(backup_finish_date) from msdb..backupset b2 where b2.database_name = fullrec.database_name and b2.server_name = fullrec.server_name and b2.type = 'I')) where fullrec.type = 'D' -- full backups only and fullrec.backup_finish_date = (select max(backup_finish_date) from msdb..backupset b2 where b2.database_name = fullrec.database_name and b2.server_name = fullrec.server_name and b2.type = 'D') and fullrec.database_name in (select name from master..sysdatabases) and fullrec.database_name <> 'tempdb' Union all -- never backed up select @server ,name ,null ,NULL ,NULL ,NULL ,NULL ,NULL from sys.databases as record where not exists (select * from msdb..backupset where record.name = database_name and server_name = @server) and name <> 'tempdb' and source_database_id is null --exclude snapshots SET @HTML = '<h2>Backups</h2> <table> <tr> <th>DB Server</th> <th>DB Name</th> <th>Last Full Backup</th> <th>Last Diff Backup</th> <th>Last Tran Backup</th> <th>Full Days</th> <th>Diff Days</th> <th>Tran Hours</th> <th>Recovery Model</th> </tr>' +(SELECT DBServer td, DBName td, CAST(CASE WHEN FullDays IS NULL THEN '<div class="Critical">None/Unknown</div>' WHEN FullDays > @FullWarningThresholdDays THEN '<div class="Warning">' + LEFT(CONVERT(varchar,LastFullBackup,113),17) + '</div>' ELSE '<div class="Healthy">' + LEFT(CONVERT(varchar,LastFullBackup,113),17) + '</div>' END as XML) td, CAST(CASE WHEN DiffDays IS NULL THEN '<div class="Critical">None/Unknown</div>' WHEN DiffDays > @DiffWarningThresholdDays THEN '<div class="Warning">' + LEFT(CONVERT(varchar,LastDiffBackup,113),17) + '</div>' ELSE '<div class="Healthy">' + LEFT(CONVERT(varchar,LastDiffBackup,113),17) + '</div>' END as XML) td, CAST(CASE WHEN TranHours IS NULL THEN COALESCE(LEFT(NULLIF(recovery_model_desc,'SIMPLE'),0) + '<div class="Critical">None/Unknown</div>','N/A') WHEN TranHours > @TranWarningThresholdHours THEN '<div class="Warning">' + LEFT(CONVERT(varchar,LastTranBackup,113),17) + '</div>' ELSE '<div class="Healthy">' + LEFT(CONVERT(varchar,LastTranBackup,113),17) + '</div>' END as XML) td, FullDays td, DiffDays td, TranHours td, recovery_model_desc td FROM @backuplog bl LEFT JOIN sys.databases sdb on bl.DBName = sdb.name COLLATE SQL_Latin1_General_CP1_CI_AS AND bl.DBServer = @Server WHERE DBServer = @@SERVERNAME ORDER BY LastFullBackup,LastDiffBackup,LastTranBackup,DBName FOR XML RAW('tr'),ELEMENTS XSINIL ) + '</table>' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[DBAChecks_ErrorLog](@NumDays int) AS /* Returns HTML for "ErrorLog.htm" attachment of DBA Checks Report */ SET NOCOUNT ON CREATE TABLE #ErrorLog( LogDate datetime, ErrorSource nvarchar(max), ErrorMessage nvarchar(max) ) CREATE TABLE #ErrorLogs( ID INT primary key not null, LogDate DateTime NOT NULL, LogFileSize bigint ) DECLARE @MinDate datetime SET @MinDate = CONVERT(datetime,CONVERT(varchar,DATEADD(d,-@NumDays,GetDate()),112),112) --Get a list of available error logs INSERT INTO #ErrorLogs(ID,LogDate,LogFileSize) EXEC master.dbo.xp_enumerrorlogs DECLARE @ErrorLogID int DECLARE cErrorLogs CURSOR FOR SELECT ID FROM #ErrorLogs WHERE LogDate >= @MinDate OPEN cErrorLogs FETCH NEXT FROM cErrorLogs INTO @ErrorLogID -- Read applicable error logs into the #errorlog table WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage) exec sp_readerrorlog @ErrorLogID FETCH NEXT FROM cErrorLogs INTO @ErrorLogID END CLOSE cErrorLogs DEALLOCATE cErrorLogs SELECT '<HTML> <HEAD> <style type="text/css"> table { /*width:100%;*/ font:8pt tahoma,arial,sans-serif; border-collapse:collapse; } th { color:#FFFFFF; font:bold 8pt tahoma,arial,sans-serif; background-color:#204c7d; padding-left:5px; padding-right:5px; } td { color:#000000; font:8pt tahoma,arial,sans-serif; border:1px solid #DCDCDC; border-collapse:collapse; } .Warning { background-color:#FFFF00; color:#2E2E2E; } .Critical { background-color:#FF0000; color:#FFFFFF; } .Healthy { background-color:#458B00; color:#FFFFFF; } </style> </HEAD> <BODY> <table><tr><th>Log Date</th><th>Source</th><th>Message</th></tr>' + (SELECT CONVERT(varchar,LogDate,120) td, CAST('<div><![CDATA[' + ErrorSource + N']]></div>' as XML) td, CAST('<div' + CASE WHEN (ErrorMessage LIKE '%error%' OR ErrorMessage LIKE '%exception%' OR ErrorMessage LIKE '%stack dump%' OR ErrorMessage LIKE '%fail%') AND ErrorMessage NOT LIKE '%DBCC%' THEN ' Class="Critical"' WHEN ErrorMessage LIKE '%warning%' THEN ' Class="Warning"' ELSE '' END + '><![CDATA[' + ErrorMessage + N']]></div>' as XML) td FROM #ErrorLog WHERE LogDate >= @MinDate /* Remove any error log records that we are not interested in ammend the where clause as appropriate */ AND ErrorMessage NOT LIKE '%This is an informational message%' AND ErrorMessage NOT LIKE 'Authentication mode is%' AND ErrorMessage NOT LIKE 'System Manufacturer%' AND ErrorMessage NOT LIKE 'All rights reserved.' AND ErrorMessage NOT LIKE 'Server Process ID is%' AND ErrorMessage NOT LIKE 'Starting up database%' AND ErrorMessage NOT LIKE 'Registry startup parameters%' AND ErrorMessage NOT LIKE '(c) 2005 Microsoft%' AND ErrorMessage NOT LIKE 'Server is listening on%' AND ErrorMessage NOT LIKE 'Server local connection provider is ready to accept connection on%' AND ErrorMessage NOT LIKE 'Logging SQL Server messages in file%' AND ErrorMessage <> 'Clearing tempdb database.' AND ErrorMessage <> 'Using locked pages for buffer pool.' AND ErrorMessage <> 'Service Broker manager has started.' ORDER BY LogDate DESC FOR XML RAW('tr'),ELEMENTS XSINIL) + '</table></HEAD></BODY>' as HTML DROP TABLE #ErrorLog DROP TABLE #ErrorLogs GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[DBAChecks_DBFiles]( @IncludeDBs varchar(max)=NULL, @ExcludeDBs varchar(max)='master,model,msdb,tempdb', @WarningThresholdPCT int=90, @CriticalThresholdPCT int=95, @HTML varchar(max) output ) AS /* Returns HTML for "Database Files" section of DBA Checks Report */ CREATE TABLE #FileStats( [db] sysname not null, [name] [sysname] not null, [file_group] [sysname] null, [physical_name] [nvarchar](260) NOT NULL, [type_desc] [nvarchar](60) NOT NULL, [size] [varchar](33) NOT NULL, [space_used] [varchar](33) NULL, [free_space] [varchar](33) NULL, [pct_used] [float] NULL, [max_size] [varchar](33) NOT NULL, [growth] [varchar](33) NOT NULL ) DECLARE @IncludeXML XML DECLARE @ExcludeXML XML DECLARE @DB sysname IF @IncludeDBs = '' BEGIN SET @HTML = '' DROP TABLE #FileStats RETURN END SELECT @IncludeXML = '<a>' + REPLACE(@IncludeDBs,',','</a><a>') + '</a>' SELECT @ExcludeXML = '<a>' + REPLACE(@ExcludeDBs,',','</a><a>') + '</a>' DECLARE cDBs CURSOR FOR SELECT name FROM sys.databases WHERE (name IN(SELECT n.value('.','sysname') FROM @IncludeXML.nodes('/a') T(n)) OR @IncludeXML IS NULL) AND (name NOT IN(SELECT n.value('.','sysname') FROM @ExcludeXML.nodes('/a') T(n)) OR @ExcludeXML IS NULL) AND source_database_id IS NULL AND state = 0 --ONLINE ORDER BY name OPEN cDBs FETCH NEXT FROM cDBs INTO @DB WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @SQL nvarchar(max) SET @SQL = N'USE ' + QUOTENAME(@DB) + '; INSERT INTO #FileStats(db,name,file_group,physical_name,type_desc,size,space_used,free_space,[pct_used],max_size,growth) select DB_NAME() db, f.name, fg.name as file_group, f.physical_name, f.type_desc, CASE WHEN (f.size/128) < 1024 THEN CAST(f.size/128 as varchar) + '' MB'' ELSE CAST(CAST(ROUND(f.size/(128*1024.0),1) as float) as varchar) + '' GB'' END as size, CASE WHEN FILEPROPERTY(f.name,''spaceused'')/128 < 1024 THEN CAST(FILEPROPERTY(f.name,''spaceused'')/128 as varchar) + '' MB'' ELSE CAST(CAST(ROUND(FILEPROPERTY(f.name,''spaceused'')/(128*1024.0),1) as float) as varchar) + '' GB'' END space_used, CASE WHEN (f.size - FILEPROPERTY(f.name,''spaceused''))/128 < 1024 THEN CAST((f.size - FILEPROPERTY(f.name,''spaceused''))/128 as varchar) + '' MB'' ELSE CAST(CAST(ROUND((f.size - FILEPROPERTY(f.name,''spaceused''))/(128*1024.0),1) as float) as varchar) + '' GB'' END free_space, ROUND((FILEPROPERTY(f.name,''spaceused''))/CAST(size as float)*100,2) as [pct_used], CASE WHEN f.max_size =-1 THEN ''unlimited'' WHEN f.max_size/128 < 1024 THEN CAST(f.max_size/128 as varchar) + '' MB'' ELSE CAST(f.max_size/(128*1024) as varchar) + '' GB'' END as max_size, CASE WHEN f.is_percent_growth=1 THEN CAST(f.growth as varchar) + ''%'' WHEN f.growth = 0 THEN ''none'' WHEN f.growth/128 < 1024 THEN CAST(f.growth/128 as varchar) + '' MB'' ELSE CAST(CAST(ROUND(f.growth/(128*1024.0),1) as float) as varchar) + '' GB'' END growth from sys.database_files f LEFT JOIN sys.filegroups fg on f.data_space_id = fg.data_space_id where f.type_desc <> ''FULLTEXT''' exec sp_executesql @SQL FETCH NEXT FROM cDBs INTO @DB END CLOSE cDBs DEALLOCATE cDBs SELECT @HTML = '<h2>Database Files</h2><table>' + (SELECT 'Database' th, 'Name' th, 'File Group' th, 'File Path' th, 'Type' th, 'Size' th, 'Used' th, 'Free' th, 'Used %' th, 'Max Size' th, 'Growth' th FOR XML RAW('tr'),ELEMENTS ) + (SELECT db td, name td, file_group td, physical_name td, type_desc td, size td, space_used td, free_space td, CAST(CASE WHEN pct_used > @CriticalThresholdPCT THEN '<div class="Critical">' + CAST(pct_used as varchar) + '</div>' WHEN pct_used > @WarningThresholdPCT THEN '<div class="Warning">' + CAST(pct_used as varchar) + '</div>' ELSE '<div class="Healthy">' + CAST(pct_used as varchar) + '</div>' END as XML) td, max_size td, CAST(CASE WHEN growth='none' THEN '<div class="Warning">' + growth + '</div>' ELSE growth END as XML) td FROM #FileStats ORDER BY db,type_desc DESC,file_group,name FOR XML RAW('tr'),ELEMENTS XSINIL) + '</table>' DROP TABLE #FileStats GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[DBAChecks_JobStats](@NumDays int,@HTML nvarchar(max) out) AS /* Returns HTML for "Agent Jobs Stats in the last 'X' days" section of DBA Checks Report */ SET ANSI_WARNINGS OFF DECLARE @FromDate char(8) SET @FromDate = CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112); WITH nextRun as ( SELECT js.job_id, MAX(CONVERT(datetime,CONVERT(CHAR(8), NULLIF(next_run_date,0), 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), next_run_time), 6), 5, 0, ':'), 3, 0, ':') ) ) as next_run_time FROM msdb..sysjobschedules js GROUP BY js.job_id ), lastRun as ( SELECT jh.job_id,CONVERT(datetime,CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':') ) as last_run_time, run_status as last_run_status, CAST(message as nvarchar(max)) as last_result, ROW_NUMBER() OVER(PARTITION BY job_id ORDER BY run_date DESC,run_time DESC) rnum FROM msdb..sysjobhistory jh WHERE run_status IN(0,1,3) --Succeeded/Failed/Cancelled ) ,JobStats AS ( select name, MAX(enabled) enabled, SUM(CASE WHEN run_status = 1 THEN 1 ELSE 0 END) as SucceededCount, SUM(CASE WHEN run_status = 0 THEN 1 ELSE 0 END) as FailedCount, SUM(CASE WHEN run_status = 3 THEN 1 ELSE 0 END) as CancelledCount, MAX(last_run_time) last_run_time, MAX(next_run_time) next_run_time, MAX(last_run_status) last_run_status, COALESCE(MAX(last_result),'Unknown') last_result from msdb..sysjobs j LEFT JOIN msdb..sysjobhistory jh ON j.job_id = jh.job_id AND jh.run_date >= @FromDate and jh.step_id = 0 LEFT JOIN nextrun ON j.job_id = nextrun.job_id LEFT JOIN lastRun ON j.job_id = lastRun.job_id AND rnum=1 GROUP BY name ) SELECT @HTML =N'<h2>Agent Job Stats in the last ' + CAST(@NumDays as varchar) + N' days</h2> <table>' + (SELECT 'Name' th, 'Enabled' th, 'Succeeded' th, 'Failed' th, 'Cancelled' th, 'Last Run Time' th, 'Next Run Time' th, 'Last Result' th FOR XML RAW('tr'),ELEMENTS) + (SELECT name td, CAST(CASE WHEN enabled = 1 THEN N'<div class="Healthy">Yes</div>' ELSE N'<div class="Warning">No</div>' END as XML) td, CAST(CASE WHEN SucceededCount = 0 THEN N'<div class="Warning">' ELSE N'<div>' END + CAST(SucceededCount as varchar) + '</div>' as XML) td, CAST(CASE WHEN FailedCount >0 THEN N'<div class="Critical">' ELSE N'<div class="Healthy">' END + CAST(FailedCount as varchar) + N'</div>' as XML) td, CAST(CASE WHEN CancelledCount >0 THEN N'<div class="Critical">' ELSE N'<div class="Healthy">' END + CAST(CancelledCount as varchar) + N'</div>' as XML) td, LEFT(CONVERT(varchar,last_run_time,13),17) td, LEFT(CONVERT(varchar,next_run_time,13),17) td, CAST(CASE WHEN last_run_status = 1 THEN N'<span class="Healthy"><![CDATA[' + last_result + N']]></span>' ELSE N'<span class="Critical"><![CDATA[' + last_result + N']]></span>' END AS XML) td FROM JobStats ORDER BY last_run_time DESC FOR XML RAW('tr'),ELEMENTS XSINIL ) + N'</table>' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[DBAChecks_FailedAgentJobs](@HTML varchar(max) out,@NumDays int) AS /* Returns HTML for "Failed jobs in the last 'X' days" section of DBA Checks Report */ DECLARE @FromDate char(8) DECLARE @SucceededCount int SET @FromDate = CONVERT(char(8),dateadd (day,-@NumDays, getdate()), 112) IF EXISTS( SELECT * FROM msdb..sysjobhistory jh JOIN msdb..sysjobs j ON jh.job_id = j.job_id WHERE jh.run_status IN(0,3) -- Failed/Cancelled AND jh.step_id <> 0 AND run_date >= @FromDate) BEGIN SET @HTML = '<h2>Failed Jobs in the last ' + CAST(@NumDays as varchar) + ' days</h2> <table> <tr> <th>Date</th> <th>Job Name</th> <th>Job Status</th> <th>Step ID</th> <th>Step Name</th> <th>Message</th> <th>Run Duration</th> </tr>' + (SELECT CONVERT(datetime,CAST(jh.run_date AS char(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':'),101) AS td, j.name AS td, CAST(CASE jh.run_status WHEN 0 THEN '<div class="Critical">Failed</div>' WHEN 3 THEN '<div class="Warning">Cancelled</div>' ELSE NULL END as XML) as td, jh.step_id as td, jh.step_name as td, jh.message as td, RIGHT('00' +CAST(run_duration/10000 as varchar),2) + ':' + RIGHT('00' + CAST(run_duration/100%100 as varchar),2) + ':' + RIGHT('00' + CAST(run_duration%100 as varchar),2) as td FROM msdb..sysjobhistory jh JOIN msdb..sysjobs j ON jh.job_id = j.job_id WHERE jh.run_status IN(0,3) -- Failed/Cancelled AND jh.step_id <> 0 AND run_date >= @FromDate ORDER BY CONVERT(datetime,CAST(jh.run_date AS char(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':'),101) DESC FOR XML RAW('tr'),ELEMENTS XSINIL ) + '</table><br/>' END ELSE BEGIN SET @HTML = '<h2>Failed Jobs in the last ' + CAST(@NumDays as varchar) + ' days</h2> <span class="Healthy">No failed jobs</span><br/>' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[DBAChecks_DiskDrives](@PCTFreeWarningThreshold int,@PCTFreeCriticalThreshold int,@HTML varchar(max) out) AS /* Returns HTML for "Disk Drives" section of DBA Checks Report */ DECLARE @BytesToTB float,@BytesToGB float,@BytesToMB float SELECT @BytesToTB = 1099511627776,@BytesToGB = 1073741824,@BytesToMB=1048576.0; WITH DriveInfo AS ( SELECT drive, volume_label, CASE WHEN total_size > @BytesToTB THEN CAST(ROUND(total_size /@BytesToTB,1) as varchar) + 'TB' WHEN total_size > @BytesToGB THEN CAST(ROUND(total_size /@BytesToGB,1) as varchar) + 'GB' ELSE CAST(ROUND(total_size / @BytesToMB,1) as varchar) + 'MB' END as Size, CASE WHEN free_space > @BytesToTB THEN CAST(ROUND(free_space /@BytesToTB,1) as varchar) + 'TB' WHEN free_space > @BytesToGB THEN CAST(ROUND(free_space /@BytesToGB,1) as varchar) + 'GB' ELSE CAST(ROUND(free_space / @BytesToMB,1) as varchar) + 'MB' END as Free, ROUND(free_space/cast(total_size as float)*100,2) as PercentFree FROM dbo.SQLIO_fnGetDrives() WHERE drive_type=3 --Fixed ) SELECT @HTML = '<h2>Disk Drives</h2> <table>' + (SELECT 'Drive' th, 'Label' th, 'Size' th, 'Free' th, 'Free %' th FOR XML RAW('tr'),ELEMENTS) + (SELECT drive td, volume_label td, Size td, Free td, CAST(CASE WHEN PercentFree < @PCTFreeCriticalThreshold THEN '<div class="Critical">' + CAST(PercentFree as varchar) + '</div>' WHEN PercentFree < @PCTFreeWarningThreshold THEN '<div class="Warning">' + CAST(PercentFree as varchar) + '</div>' ELSE '<div class="Healthy">' + CAST(PercentFree as varchar) + '</div>' END as XML) td FROM DriveInfo FOR XML RAW('tr'),ELEMENTS XSINIL) + '</table>' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[DBAChecks]( /* Agent Jobs related Params */ @AgentJobsNumDays int=3, --Num of days to report failed jobs over /* Database Files related Params */ @FileStatsIncludedDatabases varchar(max)=NULL, -- Comma sep list of databases to report filestats for. NULL=All, '' = None @FileStatsExcludedDatabases varchar(max)=NULL, -- Comma sep list of databases to report filestats for. NULL=No Exclusions @FileStatsPctUsedWarning int=90, -- Warn (Yellow) if free space in a database file is less than this threshold (Just for database specified in @FileStatsDatabases) @FileStatsPctUsedCritical int=95, -- Warn (Red) if free space in a database file is less than this threshold (Just for database specified in @FileStatsDatabases) /* Backup related Params */ @DiffWarningThresholdDays int=3, -- Backup warning if no diff backup for "x" days @FullWarningThresholdDays int=7, -- Backup warning if no full backup for "x" days @TranWarningThresholdHours int=4, -- Backup warning if no tran backup for "x" hours /* Disc Drive related params*/ @FreeDiskSpacePercentWarningThreshold int=15, -- Warn (Yellow) if free space is less than this threshold @FreeDiskSpacePercentCriticalThreshold int=10, -- Warn (Red) if free space is less than this threshold /* General related params */ @UptimeCritical int = 1440, -- Critical/Red if system uptime (in minutes) is less than this value @UptimeWarning int = 2880, -- Warn/Yellow if system uptime (in minutes) is less than this value, /* Error Log Params */ @ErrorLogDays int = 3, /* Email/Profile params */ @Recipients nvarchar(max), -- Email list @MailProfile sysname=NULL ) AS /* Created By David Wiseman Version: 1.0 http://www.wisesoft.co.uk Generates a DBA Checks HTML email report */ SET NOCOUNT ON DECLARE @AgentJobsHTML varchar(max) DECLARE @AgentJobStatsHTML varchar(max) DECLARE @FileStatsHTML varchar(max) DECLARE @DisksHTML varchar(max) DECLARE @BackupsHTML varchar(max) DECLARE @HTML varchar(max) DECLARE @Uptime varchar(max) SELECT @Uptime = CASE WHEN DATEDIFF(mi,create_date,GetDate()) < @UptimeCritical THEN '<span class="Critical">' WHEN DATEDIFF(mi,create_date,GetDate()) < @Uptimewarning THEN '<span class="Warning">' ELSE '<span class="Healthy">' END + -- get system uptime COALESCE(NULLIF(CAST((DATEDIFF(mi,create_date,GetDate())/1440 ) as varchar),'0') + ' day(s), ','') + COALESCE(NULLIF(CAST(((DATEDIFF(mi,create_date,GetDate())%1440)/60) as varchar),'0') + ' hour(s), ','') + CAST((DATEDIFF(mi,create_date,GetDate())%60) as varchar) + 'min' -- + '</span>' FROM sys.databases WHERE NAME='tempdb' exec dbo.DBAChecks_FailedAgentJobs @HTML=@AgentJobsHTML out,@NumDays=@AgentJobsNumDays exec dbo.DBAChecks_JobStats @HTML=@AgentJobStatsHTML out,@NumDays=@AgentJobsNumDays exec dbo.DBAChecks_DBFiles @IncludeDBs=@FileStatsIncludedDatabases, @ExcludeDBs=@FileStatsExcludedDatabases, @WarningThresholdPCT=@FileStatsPctUsedWarning, @CriticalThresholdPCT=@FileStatsPctUsedCritical, @HTML=@FileStatsHTML out exec dbo.DBAChecks_DiskDrives @HTML=@DisksHTML out,@PCTFreeWarningThreshold=@FreeDiskSpacePercentWarningThreshold,@PCTFreeCriticalThreshold=@FreeDiskSpacePercentCriticalThreshold exec dbo.DBAChecks_Backups @HTML=@BackupsHTML OUT,@DiffWarningThresHoldDays=@DiffWarningThresHoldDays, @FullWarningThresholdDays=@FullWarningThresholdDays ,@TranWarningThresholdHours=@FullWarningThresholdDays SET @HTML = '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <style type="text/css"> table { font:8pt tahoma,arial,sans-serif; } th { color:#FFFFFF; font:bold 8pt tahoma,arial,sans-serif; background-color:#204c7d; padding-left:5px; padding-right:5px; } td { color:#000000; font:8pt tahoma,arial,sans-serif; border:1px solid #DCDCDC; border-collapse:collapse; padding-left:3px; padding-right:3px; } .Warning { background-color:#FFFF00; color:#2E2E2E; } .Critical { background-color:#FF0000; color:#FFFFFF; } .Healthy { background-color:#458B00; color:#FFFFFF; } h1 { color:#FFFFFF; font:bold 16pt arial,sans-serif; background-color:#204c7d; text-align:center; } h2 { color:#204c7d; font:bold 14pt arial,sans-serif; } h3 { color:#204c7d; font:bold 12pt arial,sans-serif; } body { color:#000000; font:8pt tahoma,arial,sans-serif; margin:0px; padding:0px; } </style> </head> <body> <h1>DBA Checks Report for ' + @@SERVERNAME + '</h1> <h2>General Health</h2> <b>System Uptime (SQL Server): ' + @Uptime + '</b><br/> <b>Version: </b>' + CAST(SERVERPROPERTY('productversion') as nvarchar(max)) + ' ' + CAST(SERVERPROPERTY ('productlevel') as nvarchar(max)) + ' ' + CAST(SERVERPROPERTY ('edition') as nvarchar(max)) + COALESCE(@DisksHTML,'<div class="Critical">Error collecting Disk Info</div>') + COALESCE(@BackupsHTML,'<div class="Critical">Error collecting Backup Info</div>') + COALESCE(@AgentJobStatsHTML,'<div class="Critical">Error collecting Agent Jobs Stats</div>') + COALESCE(@AgentJobsHTML,'<div class="Critical">Error collecting Agent Jobs Info</div>') + COALESCE(@FileStatsHTML,'<div class="Critical">Error collecting File Stats Info</div>') + '</body></html>' declare @subject varchar(50) set @subject = 'DBA Checks (' + @@SERVERNAME + ')' DECLARE @ErrorLogSQL nvarchar(max) DECLARE @ExecuteQueryDB sysname SET @ErrorLogSQL = 'exec DBAChecks_ErrorLog @NumDays=' + CAST(@ErrorLogDays as varchar) SET @ExecuteQueryDB = DB_NAME() EXEC msdb.dbo.sp_send_dbmail @query=@ErrorLogSQL, @attach_query_result_as_file = 1, @query_attachment_filename = 'ErrorLog.htm', @query_result_header = 0, @query_no_truncate = 1, @query_result_width=32767, @recipients =@Recipients, @body = @HTML, @body_format ='HTML', @subject = @subject, @execute_query_database=@ExecuteQueryDB, @profile_name = @MailProfile GO |
Version History
Date | Version | Notes |
03-Apr-2012 | Version 1.2 BETA | Fix to DBAChecks_JobStats stored procedure. Added CDATA section. |
14-Dec-2010 | Version 1.1 BETA | Fixed datetime conversion bug in DBAChecks_JobStats procedure. |
13-Jul-2009 | Version 1.0 BETA | Beta release |
Running the report
The DBA Checks report is run by executing the dbo.DBAChecks stored procedure. This stored procedure takes a number of parameters, but only one is required:exec dbo.DBAChecks @recipients=’dummyemail@wisesoft.co.uk’
The code below shows a call to the DBAChecks stored procedure with all parameters specified:EXEC dbo.DBAChecks @AgentJobsNumDays=3,
@FileStatsIncludedDatabases=NULL,
@FileStatsExcludedDatabases=NULL,
@FileStatsPctUsedWarning=90,
@FileStatsPctUsedCritical=95,
@DiffWarningThresholdDays=3,
@FullWarningThresholdDays=7,
@TranWarningThresholdHours=4,
@FreeDiskSpacePercentWarningThreshold=15,
@FreeDiskSpacePercentCriticalThreshold=10,
@UptimeCritical=1440 ,
@UptimeWarning=2880,
@ErrorLogDays=3,
@Recipients=’dummyemail@wisesoft.co.uk’,
@MailProfile=NULL
A full explanation of these parameters is available here:
@AgentJobsNumDays | The number of days SQL SQL Server jobs are reported over. |
@FileStatsIncludedDatabases | A list of databases (comma-separated) to display file stats for. Default value is NULL (All databases). |
@FileStatsExcludedDatabases | A list of databases (comma-separated) that are excluded from database file stats. Default values is NULL (No excluded databases) |
@FileStatsPctUsedWarning | If the percent used space in the database file is larger than this value (but less than critical threshold) it will be highlighted in yellow. |
@FileStatsPctUsedCritical | If the percent used space in the database file is larger than this value it will be highlighted in red. |
@DiffWarningThresholdDays | Highlights differential backups that have not been completed for over “X” number of days |
@FullWarningThresholdDays | Highlights full backups that have not been completed for over “X” number of days |
@TranWarningThresholdHours | Highlights transaction log backups that have not been completed for over “X” number of hours. |
@FreeDiskSpacePercentWarningThreshold | Used to highlight disk drives with low disk space in yellow, where the free disk space percent is less than the value specified. |
@FreeDiskSpacePercentCriticalThreshold | Used to highlight disk drives with low disk space in red, where the free disk space percent is less than the value specified. |
@UptimeCritical | The uptime in minutes threshold that causes the uptime to be highlighted in red. |
@UptimeWarning | The uptime in minutes threshold that causes the uptime to be highlighted in yellow. |
@ErrorLogDays | The number of days worth of events included in the attached error log html file. |
@Recipients | The email addresses where the report will be sent. |
@MailProfile | The mail profile used to send the email. NULL = default profile. |
Database Code
Name | Info |
dbo.DBAChecks | This is the stored procedure you run to generate the email report. The stored procedure collates the information from the other stored procedures into a single email report. The parameters are described in the previous section. |
dbo.DBAChecks_Backups | Produces HTML for the “Backups” section of the report. |
dbo.DBAChecks_DBFiles | Produces HTML for the “Database Files” section of the report. |
dbo.DBAChecks_DiskDrives | Produces HTML for the “Disk Drives” section of the report. |
dbo.DBAChecks_ErrorLog | Produces HTML for the “ErrorLog.htm” report attachment. Review and ammend the filter applied to the error log as appropriate. |
dbo.DBAChecks_FailedAgentJobs | Produces HTML for the “Failed Jobs” section of the report. |
dbo.DBAChecks_JobStats | Produces HTML for the “Agent Job Stats” section of the report |
dbo.SQLIO_fnGetDrives() | This CLR function is used by the dbo.DBAChecks_DiskDrives stored procedure to produce the report on disk drives and their available free space. This function can be obtained here. |
Conclusion
Daily checks are an important part of any DBA’s routine, giving you confidence that your SQL instances are running problem free and allowing you to take corrective actions when required. Alerts are also important, but these should be used to compliment daily checks, rather than to replace them. If you rely on by exception reporting only, your servers will be at risk if something happens to the alert process.
I’m sure there are a wide variety of methods people currently use to perform their daily checks. The process you use is not important as long as it enables you to effectively monitor your SQL Server instances. If you’ve already got a routine that works for you – great! If not, hopefully this article will provide you with a better solution.