Daily DBA Checks

DBA Daily Checks Email Report

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. 

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.  (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.

Version History

DateVersionNotes
03-Apr-2012Version 1.2 BETAFix to DBAChecks_JobStats stored procedure.  Added CDATA section.
14-Dec-2010Version 1.1 BETAFixed datetime conversion bug in DBAChecks_JobStats procedure.
13-Jul-2009Version 1.0 BETABeta 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:

 @AgentJobsNumDaysThe number of days SQL SQL Server jobs are reported over.
 @FileStatsIncludedDatabasesA list of databases (comma-separated) to display file stats for.  Default value is NULL (All databases).
 @FileStatsExcludedDatabasesA list of databases (comma-separated) that are excluded from database file stats.  Default values is NULL (No excluded databases)
 @FileStatsPctUsedWarningIf the percent used space in the database file is larger than this value (but less than critical threshold) it will be highlighted in yellow.
 @FileStatsPctUsedCriticalIf the percent used space in the database file is larger than this value it will be highlighted in red.
 @DiffWarningThresholdDaysHighlights differential backups that have not been completed for over “X” number of days
 @FullWarningThresholdDaysHighlights full backups that have not been completed for over “X” number of days
 @TranWarningThresholdHoursHighlights transaction log backups that have not been completed for over “X” number of hours.
 @FreeDiskSpacePercentWarningThresholdUsed to highlight disk drives with low disk space in yellow, where the free disk space percent is less than the value specified.
 @FreeDiskSpacePercentCriticalThresholdUsed to highlight disk drives with low disk space in red, where the free disk space percent is less than the value specified.
 @UptimeCriticalThe uptime in minutes threshold that causes the uptime to be highlighted in red.
 @UptimeWarningThe uptime in minutes threshold that causes the uptime to be highlighted in yellow.
 @ErrorLogDaysThe number of days worth of events included in the attached error log html file.
 @RecipientsThe email addresses where the report will be sent.
 @MailProfileThe mail profile used to send the email.  NULL = default profile.

Database Code

NameInfo
dbo.DBAChecksThis 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_BackupsProduces HTML for the “Backups” section of the report.
dbo.DBAChecks_DBFilesProduces HTML for the “Database Files” section of the report. 
dbo.DBAChecks_DiskDrivesProduces HTML for the “Disk Drives” section of the report.
dbo.DBAChecks_ErrorLogProduces HTML for the “ErrorLog.htm” report attachment.  Review and ammend the filter applied to the error log as appropriate.
dbo.DBAChecks_FailedAgentJobsProduces HTML for the “Failed Jobs” section of the report.
dbo.DBAChecks_JobStatsProduces 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.

Posted in T-SQL and tagged , .