2

I would like to be able to look back and see what the used space was in transaction log over a given period. Not interested in growths or file size as they don't tell me how much space is actually being used.

I am switching from simple model to full recovery model and need an idea of how large the transaction log will get.

  • Why do you want an estimate? Switch it into full mode and find out. Just make sure you take regular backups of the transaction log using SQL Agent. If you're worried it's about to fill up the log disk, swap it back into simple mode and run the checkpoint command to free the log space. – Jhunter1 Feb 28 at 21:57
  • Thanks the reason for the estimate is because we are moving to brand new servers with full recovery therefore need an idea of disk space requirements however it looks like I will have to turn on full mode on the existing system first as you suggested to get a clear idea – Marathon_Nick Feb 29 at 16:39
5

Nothing is logged by default, generally speaking. So, unless you have something keeping track of the amount of log generated, you are out of luck.

Ordinarily, we would recommend you to check the size of your log backups, but as I understand it, you don't have those and the purpose here is to determine how much space you would need for those. Catch 22. :-)

Even with some active monitoring, you would have to check this pretty frequently, since you never know when checkpoint kicks in and "truncate the log". There's a perfmon counter you can use for this: "Log File(s) Used Size (KB)". And you can grab the value from sys.dm_os_performance_counters. I.e., pretty easy to create a job that uses a SELECT to check the used space in the tlog for selected databases, store it in a table and execute that job every minute, or whatever frequency you find suitable.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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