Script to find SQL Server databases without transaction log backups

ProblemThis is an opportunity for me to geek out on my favorite topic in Microsoft SQL Server: mining SQL Server metadata. If you've read my tips over the past two years you may have seen that I am a metadata junkie. This was developed from a need to automate as many tasks as possible over the past 9 years as the solo Database Administrator in my organization. At a high point, we had 2,000 databases hosted across 80 SQL Server instances (all on separate servers). We had one Database Administrator, Me.

Thankfully, through consolidation, we've pared down the number of instances by 50% and I now have help through access to 30% of one of our Oracle DBAs. Though things have improved, I still need to rely on automation and creativity in order to accomplish all the administrative tasks that confront a DBA on any given day. We all know the unexpected issues that arise will do their best to destroy any forward progress you make on a daily basis.

In this case it is the use of the System Tables from the msdb database to identify any databases, running under Full recovery, that have no log backups listed in the system's backup history. In this tip I will show a few scripts that you can use to determine if you are missing transaction log backups for your databases.

SolutionUsually I advocate utilizing the Dynamic Management Objects to mine system data from Microsoft SQL Server. However, backup history is not something stored in the Dynamic Management Views and Dynamic Management Functions (DMVs and DMFs, respectively.) This information is stored in the msdb database.

The msdb database is used to host information pertaining not to just backup and recovery metrics, but also all things SQL Server Agent: job history, job schedules, and so forth. It is this backup information we're most-interested in here.

We can get all the information for this task out of a single msdb System Table: msdb.dbo.backupset. The important columns of note for this object are itemized below. Asterisks denote the columns we'll be using in this query:

*database_name - name of the database the backup file pertains to.

*type - Type of backup process performed:

D = Database

F = File or Filegroup

G = Differential File

I = Differential (database)

L = Transaction Log

*backup_finsh_date - time when the backup process completed.

backup_start_date - time the backup process was initiated.

name - name of the backupset

user_name - user performing the backup process

expiration_date - date the backup expires

SELECT D.[name] AS [database_name], D.[recovery_model_desc] FROM sys.databases D LEFT JOIN ( SELECT BS.[database_name], MAX(BS.[backup_finish_date]) AS [last_log_backup_date] FROM msdb.dbo.backupset BS WHERE BS.type = 'L' GROUP BY BS.[database_name] ) BS1 ON D.[name] = BS1.[database_name] WHERE D.[recovery_model_desc] <> 'SIMPLE' AND BS1.[last_log_backup_date] IS NULL ORDER BY D.[name];

This code queries msdb.dbo.backupset for a listing of databases that have recorded log backups and when the latest log backup occurred. The results are then joined via an outer join construct back to the sys.databases System Catalog View on database_name. Any database that exists on the SQL Server (as presented through the sys.databases view) running in Full or Bulk-Logged recovery, that does not exist in the list of databases with associated transaction log backups, is returned as a result of the query.

Query 2

Here is another query that will show you any databases that are in the Full or Bulk-Logged recovery model and have had a full backup without any transaction log backups after the last full backup.

SELECT D.[name] AS [database_name], D.[recovery_model_desc] FROM sys.databases D LEFT JOIN ( SELECT BS.[database_name], MAX(BS.[backup_finish_date]) AS [last_log_backup_date] FROM msdb.dbo.backupset BS WHERE BS.type = 'L' GROUP BY BS.[database_name] ) BS1 ON D.[name] = BS1.[database_name] LEFT JOIN ( SELECT BS.[database_name], MAX(BS.[backup_finish_date]) AS [last_data_backup_date] FROM msdb.dbo.backupset BS WHERE BS.type = 'D' GROUP BY BS.[database_name] ) BS2 ON D.[name] = BS2.[database_name] WHERE D.[recovery_model_desc] <> 'SIMPLE' AND BS1.[last_log_backup_date] IS NULL OR BS1.[last_log_backup_date] < BS2.[last_data_backup_date] ORDER BY D.[name];

What are the implications of not issuing log backups against a database running in Full or Bulk-Logged recovery? Without log backups, transaction logs are not going to be check-pointed and space will not be re-used. Log files will grow (if Autogrowth is enabled) and space will eventually be consumed to a point that the SQL Server is unable to allocate space as needed where databases are set to Autogrow. If Autogrowth is not enabled on identified databases, your users will eventually receive an error stating that their transactions could not be fulfilled because the transaction log is full.

What impacts the results? The interval at which you delete your backup history will directly influence what results are presented through this query. If you clear backup history metadata on a daily basis you may return false positives. I strongly suggest clearing backup history information from your instances for any records over 31 days. This not only will allow you to run queries like the one I present here and obtain decent, usable information; but it also ensures you do not encounter situations where your msdb database has grown to a significant size that could impact backup and recovery performance or space issues on your SQL Server instance.

Next Steps

You can easily parameterize the query to accept a date value. From there you can alter the code to look for any databases that have not experienced a log backup in X number of days, hours, etc.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

I'm pleased that you found the tip useful. Your comments are quite interesting in that I wrote the tip from the angle of identifying databases that need to have log backups configured. We sometimes fail to forget though that there are some shops where you only need to have the option to recover to the last good full database backup and, indeed, the script works for that as well.

Thanks for the tip and the extra info on the msdb system tables. I often find that databases are created in FULL mode unnecessarily where SIMPLE would do just fine and all you need is a daily full backup. Reason that so many databases get created in FULL mode is probably due to the Model database being FULL. As a standard I always change databases to SIMPLE mode and create a daily full backup job. Only if there is a point-in-time recovery requirment will I implement FULL with tran log backups.

I also see DBAs forgetting to implement tran log backups on the Principal when implementing mirroring. Your approach will identify where this is happening.

cheers

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.