Monitoring Log File Growth in SQL Server

We recently had to ‘migrate’ a vendor supplied database with a vendor-supplied process.  We were warned that the database log file could grow some 10,000% !!!!  Not finding that particularly palatable, I upped the transaction log backup to every minute.  I needed some scripts that would allow me to monitor the process and verify that I could escape the vendor’s dire prediction.  The scripts that follow did just that…

sp_helpdb  <your database name here>

SELECT instance_name as [DBName] ,cntr_value as “LogFullPercentage”  FROM sys.dm_os_performance_counters  WHERE counter_name LIKE‘Percent Log Used%’AND instance_name like‘<your database name here>%’orderby 2 desc

–NEXT, DO WE HAVE SPACE AVAILABLE??

exec xp_fixeddrives

–NEXT, MAKE SURE THERE AREN’T ANY OPEN TRANSACTIONS:

select  * from  sys.dm_tran_database_transactionswhere database_id = <your databaseid here>

–NEXT, TO SEE THE STATUS OF THE BACKUP PROCESS…

SELECT  top 20  CONVERT(CHAR(100),SERVERPROPERTY(‘Servername’))AS Server , msdb.dbo.backupset.database_name,  datediff(  ss, msdb.dbo.backupset.backup_start_date,     msdb.dbo.backupset.backup_finish_date),    msdb.dbo.backupset.expiration_dateCASE msdb..backupset.type  WHEN‘D’THEN‘Database’  WHEN‘L’THEN‘Log’  ENDAS backup_type,     msdb.dbo.backupset.backup_size

FROM    msdb.dbo.backupmediafamily  INNERJOIN 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()1 )  and database_name like‘<your database name here> %’  ORDER  BY     msdb.dbo.backupset.database_name,   msdb.dbo.backupset.backup_finish_date desc

One Reply to “Monitoring Log File Growth in SQL Server”

  1. Hi Steve, while checking on SQL Articles I happened to chance on your article.I am not sure if this is the right place to leave the comment, but I was not able to figure out your email id and hence leaving the comment here. When seeing the section about you, I was happy to see that you mentioned that you are a born again and you even are leading people to know Jesus !! It was such a joy to read about you .. more so in a site like this where people mention all the certifications they have done and all the great companies they have worked for .. blah blah .. but you have mentioned the greatest thing!!! God bless you for this .. Amen

Leave a Reply

Your email address will not be published. Required fields are marked *