CODEWITHSUNDEEP
sql-serverlog-shipping
Giova
Giova

Reputation: 1127

log shipping bandwith estimation

We have a lot of sql server databases in Simple Recovery Mode. We need to move these databases on a different datacenter. These databases can not be closed (they are serving 24x7 mission critical services). We are thinking to use log shipping to create a remote standby database and then switching the remote copy to be primary.

How we can estimate the amount o transaction log written daily by each of these databases? We need to know this amount to estimate netowork bandwith and size of transaction log backup.

Regards Giovanni

Upvotes: 3

Views: 418

Answers (2)

Giova
Giova

Reputation: 1127

I wrote a stored procedure that do the following:

  • read num_of_bytes_written from sys.dm_io_virtual_file_stats
  • random insert and delete rows from tables
  • read num_of_bytes_written from sys.dm_io_virtual_file_stats
  • calculate difference in megabytes between the two values of num_of_bytes_written
  • make compressed transaction log backup
  • check the difference between log backup size and the num of bytes written

After 1000 executions I'll calculate the ratio between log size and backup size

regards Giova

Upvotes: 0

Ben Thul
Ben Thul

Reputation: 32687

You don't have to estimate: set the database to full recovery and start taking log backups. You'll have to do this anyways to do log shipping and you should really be doing this anyways if the db is that important to you. Once you're taking the log backups, you'll know how big the log backups are over any given prior of time so you can estimate bandwidth based on that.

Upvotes: 3

Related Questions