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