You have been doing a fantastic job of late.  You have all of your databases being backed up on a regular schedule.  Now you get an alert that your transaction log just keeps growing.  Why is that?

You decide to do a little investigation and find that you have your recovery model set to full and you are performing full backups.  That should cover it right?  No, that is not right!

What is this?  Now you have to do something more?  Yes, that is correct.  Performing full backups is not always enough to recover your critical data.  You need to know the recovery requirements for the database / server in question.  But since you have the recovery model set to full, let’s just talk about what else you should be doing.

The first step should be to run a quick script to determine what databases you have in full recovery and which of those databases do not have a LOG backup.  You see, when a database is in full recovery, you should also backup your transaction log on a regular schedule too.  That schedule is to be determined as a part of the second step (and I will only talk about the first two and only briefly about the second step).

In that first step, you can query your msdb database to help generate a report of which databases have had a transaction log backup.  That should be easy enough to do.  Despite the ease, it should not lessen the importance by any degree.  Here is the script that I wrote recently to help determine which databases were in need of a log backup.

Code block

SELECT T1.Name AS DatabaseName
,ISNULL(t2.database_name, 'No Backup Taken') AS LogBackupAvail
,T1.recovery_model_desc
,'Log' AS BackupType 
,ISNULL(CONVERT(VARCHAR(23), CONVERT(DATETIME, MAX(T2.backup_finish_date), 131))
, CASE WHEN T1.recovery_model_desc = 'Simple'
THEN 'N/A'
ELSE 'Backup Not Taken'
END
) AS LastBackUpTaken
FROM sys.databases T1
LEFT OUTER JOIN msdb.dbo.backupset T2
ON t1.name = t2.database_name
AND t2.type = 'L'
GROUP BY T1.Name,T2.database_name,T1.recovery_model_desc

You will likely notice that I am querying both sys.databases out of the master database as well as dbo.backupset out of msdb.  Look more closely and you will see the employ of a Left Outer Join with two conditions on the Join.  In this case, both conditions are required to produce the Outer Join effect that I was seeking.  Had I used a script like the following:

Code block

SELECT T1.Name AS DatabaseName
,ISNULL(t2.database_name, 'No Backup Taken') AS LogBackupAvail
,T1.recovery_model_desc
,'Log' AS BackupType 
,ISNULL(CONVERT(VARCHAR(23), CONVERT(DATETIME, MAX(T2.backup_finish_date), 131))
, CASE WHEN T1.recovery_model_desc = 'Simple'
THEN 'N/A'
ELSE 'Backup Not Taken'
END
) AS LastBackUpTaken
FROM sys.databases T1
LEFT OUTER JOIN msdb.dbo.backupset T2
ON t1.name = t2.database_name
WHERE t2.type = 'L'
GROUP BY T1.Name,T2.database_name,T1.recovery_model_desc

You would see a considerably different result set.  The reason for this different result set is tied to the predicate used and the Join conditions.  And when one examines the execution plan, the difference becomes a little more evident.

That WHERE predicate converted our Left Outer Join to an Inner Join.  Now, if I had only wanted to return results for databases that had log backups, that might be fine.  I want to report on all databases and find not only the last log backup for a database, but I also want to find if a log backup is missing.  Therefore, I need to ensure that both conditions are declared as part of my Left Outer Join.

Running a query such as this will now provide us with some knowledge as to the database recovery models as well as which databases have had a log backup, have not had a log backup, and which do not need a log backup (based on recovery model).

Armed with this information, it is imperative to do a bit more digging now.  Knowing what the business deems as an acceptable data loss is important.  Find that out and then create transaction log backups accordingly so the business can recover appropriately in the event of a disaster.