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_date,  CASE 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