Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Free SQL Server Webcast > Building Really Fast SQL Server VMs
 

Script to find SQL Server databases without transaction log backups


By:   |   Read Comments (3)   |   Related Tips: 1 | 2 | 3 | More > Backup

Problem
This 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.

Solution
Usually 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.
  • Learn more about transaction logs and log backups at MSSQLTips.
  • The concept of Dynamic Management Objects was mentioned in this tip.  Tips about DMVs are available here.
  • More tips from the author are available via this link.


Last Update:


next webcast button


next tip button



About the author





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.



    



Wednesday, January 06, 2010 - 12:42:23 PM - SandraV Back To Top

Do you want parenthesis around the test for BS1.[last_log_backup_date] IS NULL OR BS1.[last_log_backup_date] < BS2.[last_data_backup_date]?

Without them, this returns one of my DBs that's in SIMPLE model but was previously FULL because it has a T-Log backup in its history from back in May and then a Full DB backup from today.


Wednesday, January 06, 2010 - 9:02:55 AM - timmer26 Back To Top

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.


Wednesday, January 06, 2010 - 7:05:07 AM - --cranfield Back To Top

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


Learn more about SQL Server tools