Checking all instances of SQL Server every morning is one of the best ways that a DBA can ensure that the databases are available, jobs have run without a problem, and connections are being made. However, connecting to each instance of SQL Server is both time consuming and impractical. Using the SQL Overview package and the reports described in this article, the need to connect to each instance is virtually eliminated!
These articles eight reports are scheduled to run every morning to "check" on potential problems since the previous day's run. An e-mail is sent with the results of the "check". You now spend your time resolving issues instead of manually reviewing each server.
Database Missing Recent Backup Report
Description:
Report databases missing a recent database backup.
Settings:
In the table rep.Database_List, the column AllowableDaysBetweenBackups controls if a database is checked and how many days old a previous backup can be before the database is reported as needing a new backup.
- Value of NULL means skip this database when checking backups
- Value of 1 or more is the number of days the previous backup must have been created.
- Any databases flagged to be skipped will be set to 1 day when a new backup is detected for the database.
Database Missing Recent Log Backup Report
Description:
Report any database whose recovery mode is FULL or Bulk-Logged that is missing a recent database log backup.
Settings:
Use the same column settings as the Database Missing Recent Backup report.
Drives Low on Space Report
Description:
Identify any drive on a server that has 10% or less free space. This report is only e-mailed when a drive is newly detected with low space or a previously reported drives free space becomes even less.
Settings:
The setting of 10% is specified in the SQL used in creating the report. To adjust this setting you need to modify the report's SQL.
Error Log Messages Report
Description:
Report login failure, backup failures, database growth issues, and other errors recorded in the error log since the previous execution of the SQL Overview job. Error messages are selected for this report based on their message content.
Settings:
Error messages are reported based on their message contents. The current query includes messages such as login failures, backup failures, file growth errors, and others. To add messages, modify the query to include them.
Instance Recently Restarted Report
Description:
Report any SQL Server instance started since the previous time the SQL Overview job was executed.
Settings:
N/A
Possible Causes:
a) Server Blue Screen and was automatically rebooted
b) Server hung and needed to be rebooted
c) Security software was applied and a reboot was required
d) SQL Server instance required a restart
e) Scheduled server reboot
Job Failures Report
Description:
Report all SQL Agent jobs that failed since the previous execution of the SQL Overview job.
Settings:
Report any SQL Agent job or step that failed.
Large Database Log File Report
Description:
Report databases whose log file is larger than is data file. There is nothing wrong with this except it might be a symptom of a problem.
Settings:
Report any database with a log file larger than 50 mb and is larger than its database files. To change this setting you need to modify the view for this report.
Possible Causes:
a) Log backup needs to be scheduled more often.
b) Sudden increase in data modifications from a data conversion.
c) Sometimes TEMPDB log file gets larger than its database file when the database is being heavily used.
SQL Overview Table Refresh Errors Report
Description:
Report any errors captured while executing the SQL Overview package.
Settings:
N/A
Possible Causes:
a) Can't connect remotely to SQL Instance
b) Bug in the SQL Overview Package
c) Server or Instance is off line
Setup Report Jobs
Update the job "DBA_SQL Overview" adding a new step for each report job. In each step, include the following command to start the report job.
-- Replace JobName with the actual job. EXEC dbo.sp_start_job N'JobName'
List of Report Jobs:
- DBA-SQL Overview - Report Databases Missing Backups (Contains both database and log backups missing reports)
- DBA-SQL Overview - Report Drives Very Low on Space
- DBA-SQL Overview - Report Errorlog Messages
- DBA-SQL Overview - Report Instances Recently Started
- DBA-SQL Overview - Report Job Failures
- DBA-SQL Overview - Report Large Log Files
- DBA-SQL Overview - Report Table Refresh Errors
Create these report jobs using the SQL contained in this article's file attachments. Before executing the SQL, change '??@domain.com' to your DBA teams e-mail address and then execute the SQL. None of your existing jobs will be replaced.
Some of these reports require additional tables and views to be created before executing them. The SQL for creating them is include in this articles file attachment.
- rep.Database_List
- rep.Disk_Space_Alert
- rep.vw_DatabasesMissingBackups
- rep.vw_DatabasesMissingLogBackups
- rep.vw_DatabasesWithLogFileGreaterThanDatabaseFiles
Conclusion
The reports contained in this article are based on real work experiences. With these reports, you can start the workday being informed of any issues needing to be addressed, before you start getting the phone calls and e-mails. I have no doubt each of you have your own way of keeping on top of your systems. I hope that using this package with its reports will assist you.
Additional Resources
- To get a quick list of when a server was rebooted, use the windows uptime command. It lists all reboots and specifically points out when a blue screen abend occurs.
- SQL Overview Part I, II, and III
- SQL_Overview_Part4.zip