4

We have a moderately sized database (about 500GB) that we're going to switch from Simple to Full Recovery, with Trans Log backups every 15 minutes. Before we make the switch, I want to get an estimate of how much space those backups will take on average. (Actually, our storage folks want to know so they can carve out that space for me.) So how can we get that estimate?

Looking at how much log space is in use won't do it, since it's just using one area in the log while freeing others. So I'm guessing I would have to look instead at the rate at which the transaction log is being used. The plan is to monitor the VLF usage and track which VLFs are used and then freed over time, along with their size.

That's going to be a little challenging to set up, but it's doable. Before I go down that route, though, I'm hoping there's a better/easier way. Any ideas?

Improve this question
2
  • sys.dm_io_virtual_file_stats will show the amount of data written to the log.
    – Martin Smith
    Commented Oct 20, 2015 at 22:35
  • Thanks Martin - That's the answer I needed. I'm now collecting the info that DMV provides about my log file every 10 minutes. I can process that data later on to see how the trans log usage rate varies throughout the day, and get a good estimate of usage rate.
    – Dave
    Commented Oct 22, 2015 at 18:40

2 Answers 2

Reset to default
2

You didn't specify which SQL version you are on but you could use a Server Side trace or an Extended events session to capture the traffic that is sent to the SQL server (if you can live with the performance impact it might have) and replay those captured traces on a test environment to see what the daily growth would be for the day where you ran your traces.

See here how to replay a profiler trace or distributed replay for info on how to run an XEvents replay.

That would allow you to rerun the captured workload on a test system and see for real what is happening with your log file.

Improve this answer
1

If your database follows the normal pattern where data mostly gets added, less frequently updated and rarely deleted, then you can get a rough idea by determining the daily growth rate of your database. Look at your backup history and determine how much growth there is per day. That will be pretty close to your transaction log backup size.

Index maintenance will artificially inflate your transaction log backups so there will be spikes when maintenance is done, but that's how I would do it.

Improve this answer
1
  • Thanks for the idea, Jonathan. Unfortunately, the database sees mostly Update traffic. Continually.
    – Dave
    Commented Oct 20, 2015 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.