question

Dery avatar image
Dery asked

Total Transaction Log Size in Simple Recovery Model

In SQLServer 2008R2, checking the size the transaction log backups with in a certain time range will tell you the total size of transaction logs files generated within that time range. This works perfectly if the database's recovery model is full. However, we are not taking any transaction log backups if the database is in simple recovery model. So, how can we know the total size of the transaction logs generated if the database is in simple recovery model? Thanks for your help in advance.
sql-server-2008-r2transaction-logrecovery
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

"total size of the transaction logs files generated within that time range" - You generate log backup files, not transaction log files themselves. All this shows is the amount of transactions captured since the previous log backup.
0 Likes 0 ·
The OP appears to be using transaction log backup file size as a proxy for the amount of data written to the transaction log (amount of data, number of bytes, number of transactions or some other metric of interest). The question might be better read as, "How can one determine the amount of data written to the transaction log without using transaction log backup file size as a proxy?"
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
You can't really. So instead, you could look at transactions/sec counter in Performance Monitor. You could also capture query metrics to see transactions and have a count that way. I prefer extended events for this when working on 2008 or better.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Grant. I was able to get the total number of transactions for a certain time range by using transactions/sec and by setting WAITFOR DELAY time. However, my boss also wanted the total size of data written to the log while the database is in simple recovery model. This information is needed to determine the amount of network bandwidth required to replicate between two servers. I guess the only option I have is to set the database in Full Recovery model > Take Full Backup at time T1 > Take Tran log backups at certain interval > Take last Tran Log backup at Time T2 > change Recovery Back to Simple > Take Full Backup. The total data size written to the log will then be the total size of the log files between T2 and T1. Would you agree?
0 Likes 0 ·
If you're trying to get a perfect measure of a literal size, maybe. You can gather a lot of info from other sources. DBCC LOGINFO will still show what's in the log for a period of time prior to the next time it cleans up. That will at least give you a good idea. Also, to a degree, your max log size is an indication of the anticipated log volume. Finally, take a look at DBCC SQLPERF shows log size & free space, so you can see how that changes over time too.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.