EEP! My transaction log is full!

When getting paged for a full transaction log there are a few things I look at:

What is the status of the log NOW?  Sometimes a backup happens between the time the log fills and the time you are notified of the issue.  Your database may be functional when you investigate, but you should take this as a warning that your log may not be sized correctly. 

To get a current view of your log space use the following:

DBCC SQLPERF (Logspace)



What is the recovery model of my database?  This is important because the action you take to free log space depends on the recovery model.  If your database is in SIMPLE recovery a full backup of the database will free space in your log.  If your database is in FULL recovery you need to take a backup of the log.

To get a quick look at your recovery models:

SELECT name, databasepropertyex(name,'Recovery') AS 'Recovery Model'
FROM master.dbo.sysdatabases
ORDER BY name



How big should my log be?  If your database is in FULL recovery and has been live for a few months you can get a rough estimate of the minimum amount of space needed for your log by finding the largest log backup in the previous 30 days. This isn't 100%, but when you're a complete newbie like me, this is a great place to start!

Run this code to find the size of your largest log backup over the previous 30 days:

SELECT DISTINCT msdb.dbo.backupset.database_name,
Max(msdb.dbo.backupset.backup_size)/1024/1024  AS  'MaxLogBackup(MB)'
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 30)
AND msdb..backupset.type = 'L'
GROUPBY msdb.dbo.backupset.database_name

(Needed to remove database names, sorry)




Do I need to add space to my log?  After reviewing the information gathered so far you may determine the log needs more space.  If this is true you first need to make sure you have enough storage available.  Remember you have to check how much space is being taken up on your storage device by all files living there, not just the file you are working with.  This also means you need to take the auto-grow settings into account.

Here is an example of changing the size of a log file:

USE [master]
GO
ALTER DATABASE [AdventureWorks] MODIFY FILE ( NAME = N'AdventureWorks_Log', SIZE = 524288KB )
GO


This concludes my post on full transaction logs.  I hope someone finds it useful :)  Please please please post any needed corrections.  I am new to being a DBA and this blog is somewhat of an exercise for myself to become more proficient with SQL Server.

Thanks for reading!
Kim