What does database_transaction_log_bytes_reserved of sys.dm_tran_database_transactions mean?

Question

Is database_transaction_log_bytes_reserved simply the difference between a log file size and database_transaction_log_bytes_used for a transaction in a database in SQL SERVER 2012?   

I'm checking that DMV since I'm trying to figure out why the log of database in simple recovery mode keeps growing even though there is only 1 transaction and database_transaction_log_bytes_used is less than the log file size.   I also checked DBCC SQLPERF but its used% does not seem to match with database_transaction_log_bytes_used or the sum of database_transaction_log_bytes_used and database_transaction_log_bytes_reserved.

Answer 1

I believe that is a reservation of spaces needed to carry out a rollback.

What does sys.databases.log_reuse_wait_desc report for the database in question?

Answer 2

Hi PCSQL66,

According to your description, this issue is caused by that your transaction log has not been cut off.

Firstly, we need to know the meaning of the result we get after executing these scripts.

1.     As other post, ‘database_transaction_log_bytes_reserved’ indicates the spaces needed to carry out a rollback.
2.     ‘database_transaction_log_bytes_used’ will give the result of the spaces used by active replication. And, the sum of database_transaction_log_bytes_used and database_transaction_log_bytes_reserved does not equal to the log file size.
3.     ‘Log Space Used (%)’ of ‘DBCC SQLPERF’ will give the percentage of the log file currently in use.

Because of the physical architecture of Transaction Log, besides the active transaction log, some inactive transaction log may also not been cut off. The column ‘Log Space Used (%)’ represents the size of all transaction log and ‘database_transaction_log_bytes_used’ only represents the size of active transaction log. As a result, the used % will not match with database_transaction_log_bytes_used. For more information about Transaction Log Physical Architecture, you can review this article: https://technet.microsoft.com/en-us/library/ms179355(v=sql.105).aspx

Secondly, your transaction log may has not been cut off for that the transaction takes a long time or does a lot of changes.

Regards,
Teige

Answer 3

AS per MSDN yes , but i didnt check on live environment.

If database is in simple recovery model, there could be different reasons like database is part of replication or long running transaction. you can refer this

---

Thanks

Saurabh Sinha

Blog Twitter LinkedIn Gallery Facebook

Please click the Mark as answer button and vote as helpful if this reply solves your problem