June 3, 2011 at 11:29 am
I'm putting together a plan to set up log shipping for disaster recovery purposes, but before I go turning it on I want to do some analysis against our production databases to get an idea of how large my TRN files will end up being at 15 minute intervals over the course of a few days so I can have an idea of what kind of bandwidth strains I might run into when we have to ship these to our remote site.
Does anyone have a script that I could schedule to do something like that? Or can anyone point me to some useful DBCC or sp_'s that I could use to make my own scripts? My databases are set to FULL recovery mode at the moment, so I can run queries to examine the LDF, but I can't quite figure out how to translate that into estimating the size that my individual TRN files will be.
Any bright ideas?
June 3, 2011 at 12:03 pm
Since the database is already in full recovery, I assume you are already running tlog backups every 15 min. You can query msdb.dbo.backupset to pull the transaction log backup sizes.
SELECT backup_finish_date,(backup_size)/1024/1024 AS tlog_backup_MB FROM msdb.dbo.backupset
WHERE type ='L' AND database_name = DB_NAME()
ORDER BY backup_finish_date DESC
June 3, 2011 at 12:04 pm
Why don't you just schedule the existing TRX logs on those databases for every 15 minutes. That would tell you pretty easily. Or if you have exisiting TRX logs you might try selecting them all and seeing how big they are. It should be about right. The rescheduling them will tell you how big of a file will typically need to be transfered.
CEWII
June 3, 2011 at 12:12 pm
JeremyE (6/3/2011)
Since the database is already in full recovery, I assume you are already running tlog backups every 15 min. You can query msdb.dbo.backupset to pull the transaction log backup sizes.SELECT backup_finish_date,(backup_size)/1024/1024 AS tlog_backup_MB FROM msdb.dbo.backupset
WHERE type ='L' AND database_name = DB_NAME()
ORDER BY backup_finish_date DESC
This is exactly what I was looking for. Thanks!
June 3, 2011 at 1:09 pm
If you are using SQL Server 2008 Enterprise Edition or SQL Server 2008 R2 Enterprise Edition or Standard Edition, make sure that you set your transaction log backups to compressed to minimize the bandwidth.
June 17, 2011 at 7:35 am
JeremyE (6/3/2011)
Since the database is already in full recovery, I assume you are already running tlog backups every 15 min. You can query msdb.dbo.backupset to pull the transaction log backup sizes.SELECT backup_finish_date,(backup_size)/1024/1024 AS tlog_backup_MB FROM msdb.dbo.backupset
WHERE type ='L' AND database_name = DB_NAME()
ORDER BY backup_finish_date DESC
very useful, saving this for later use.
thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply