Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Overview IV - DBA's Morning Review

By David Bird,

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

Resources:

SQL_Overview_Part4.zip
Total article views: 12867 | Views in the last 30 days: 8
 
Related Articles
SCRIPT

DB File Overview

This script will give you a overview of the current database files.

FORUM

Server Database Backup

Server Database Backup

SCRIPT

SQL Server System Report

Overview report for tracking key database performance metrics, general system stats, overall activit...

FORUM

Backup BizTalk Server Databases

Backup of BizTalk Server Databases

BLOG

SQL Server # Database Backup Report using T-SQL

Today, I am going to share few very useful scripts which will report us on Database Backup from diff...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones