By David Wiseman (Administrator)published 13 Jul 2009, modified 03 Apr 2012
My Rating:
Vote
Rating:
Not Rated
Views:13103

DBA Daily Checks Email Report

Introduction

DBAs typically create a number of alerts to inform them of potential problems with their database servers.  In addition to these alerts, it's also important to proactively monitor your database servers by running a series of daily checks.  The exact checks performed will vary from organization to organization, but some common ones include:

  • Check backups
  • Check SQL Server agent jobs
  • Check disk space
  • Check free space in database files
  • Check SQL Server error log
  • Check Windows event logs

Your daily checks might not be limited to those listed above and you might also have more comprehensive checks on a weekly/monthly/quarterly basis. 

One of the problems with daily checks is that the information you need to complete your checks isn't available in a single location and potential problems are not always highlighted.  To improve this process I created a stored procedure to send a HTML email report for the SQL Server instance, which is automated via the SQL Server Agent.  If you haven't already found a good method of performing daily checks, the DBAChecks stored procedure in this article might give you a good starting point.

Report Sample

The image below is a sample report that was generated from a test instance of SQL Server. 

 DBA Checks HTML Report Sample

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.  Download.
  • 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.

Version History

Date Version Notes
03-Apr-2012Version 1.2 BETAFix 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.




  Post Comment
Order By:  
User Comments
      
julianh
Posted On: 11/11/2009 1:30:38 AM

Thanks for this - is there a way to produce the report for remote SQL servers so you can run them centrally from one server?

David Wiseman (Administrator)
United Kingdom
Posted On: 12/14/2010 12:10:03 PM

The code would need to be installed on each server. The stored procedure could be executed remotely from a central server, but it's probably eaiser to create a separate job on each server.

mgrzymala
Posted On: 6/29/2012 4:41:00 AM

I haapen to have one instance with collation Polish_CS_AS_KS where the DBAChecks_Backups procedure won't install because I get "Must declare the scalar variable @TranWarningThresholdHours" (and some others) when parsing the script. I already tried creating a separate utility database with normal collation and run the script against it, but did not help (?)
How can I install the procedure without having to change the instance collation?

mgrzymala
Posted On: 7/2/2012 8:32:51 AM

I'll answer my own question: after minor corrections of the names (upper/lower case) of some of the variables everything went through just fine. The variables that needed name-consistency adjustments were: @server, @TranWarningThresholdHours, @UptimeWarning, @DiffWarningThresholdDays and @nextrun