{"id":29,"date":"2012-12-04T05:35:18","date_gmt":"2012-12-04T05:35:18","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=29"},"modified":"2013-02-06T02:04:43","modified_gmt":"2013-02-06T02:04:43","slug":"transaction-log-grows-unexpectedly-due-to-orphan-transactions","status":"publish","type":"post","link":"https:\/\/www.theSQLReport.com\/?p=29","title":{"rendered":"Transaction Log Grows Unexpectedly Due to Orphan Transactions"},"content":{"rendered":"

Problem:<\/p>\n

A SQL Server 2005 database’s transaction log file is growing.\u00a0 The transaction log file is originally 20 gigabytes in size.\u00a0 Over a three day period, the transaction log grow to 120 gigabytes in size.\u00a0 Doing the log backup does not release all the space in the transaction log.\u00a0 \u00a0 The transaction log backup would run successfully every hour, but the backup file would only be a gigabyte in size.\u00a0 In this case, after each transaction log backup the “Log Space Used (%)” never went below 95%.\u00a0 Very frustrating \ud83d\ude41<\/p>\n

We found the issue to be orphaned transactions in the database.<\/p>\n

\"\"<\/a><\/p>\n

 <\/p>\n

First Step: Identify the orphan transactions.<\/p>\n

When I ran sys.dm_tran_database_transactions (as seen below), I see that I have transactions that are days old that have not committed.\u00a0\u00a0 For example, transaction_id: 874532313, was created on 11\/23\/2012, and the database_transaction_state is 4 ( transaction has generated log records ).\u00a0 To state the obvious, we should not have a transaction_id that is days old like this.\u00a0\u00a0 That mean this transaction has not been commited, nor has it been rolled back.<\/p>\n

Please refer to this link on sys.dm_tran_database_transactions<\/a> for more information on this dynamic object.<\/p>\n

\"\"<\/a><\/p>\n

Second Step:<\/p>\n

Run “DBCC LOGINFO”.\u00a0\u00a0\u00a0 When we ran this statement, we found that a number of the virtual log files had a status of 2, which mean that it can not be overwritten.<\/p>\n

Third Step:<\/p>\n

We queried sys.dm_tran_locks, we were able to see that the transaction_id from\u00a0sys.dm_tran_database_transactions was called request_owner_id in this dynamic object, and that the session was -2. (the picture below\u00a0 does not correspond to the pictures above, this is only reference).\u00a0\u00a0 The -2 for a session owner signifies that this transaction has been orphaned.\u00a0 The\u00a0session owner was closed or killed before the transaction could be committed or rolled back.<\/p>\n

On side note, we already new that the software writing to this database would produce orphan transactions when it experienced high volume of transactions.<\/p>\n

\"\"<\/a>

SYS.DM_TRAN_LOCKS - Dynamic Object<\/p><\/div>\n

Final Step:<\/p>\n

With the information in the request_owner_guid column of the\u00a0sys.dm_tran_locks, you can use the kill command rollback the orphaned transaction.<\/p>\n

For example: if you wanted to remove transaction_id\/request_owner_id: 1137037072, then run the following command:<\/p>\n

—<\/p>\n

KILL ‘4C79FC71-D1FF-402A-AB70-95BA150984EB’<\/p>\n

GO<\/p>\n

—<\/p>\n

See the Microsoft link for more information regarding the KILL<\/a> command and orphaned transactions.<\/p>\n

Once the orphaned transactions were killed, we were successfully able to do a transaction log backup, and the\u00a0“Log Space Used (%)” went from 95% to 0.04%.<\/p>\n

In the “DBCC LOGINFO”, the status of all but one line changes from 2 to 0.<\/p>\n

Please note that the software did have a another batch process by which we did recover all these transactions.<\/p>\n

Final Notes:<\/p>\n

Thanks to Dan who found the that the request_owner_guid in sys.dm_tran_locks could be used in the KILL statement.<\/p>\n

Also this post will be re-posted, when I can get screen shots of a single transaction_id to be used in all the pictures.<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Problem: A SQL Server 2005 database’s transaction log file is growing.\u00a0 The transaction log file is originally 20 gigabytes in size.\u00a0 Over a three day period, the transaction log grow … Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":41,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-29","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server"],"_links":{"self":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/29"}],"collection":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=29"}],"version-history":[{"count":30,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/29\/revisions"}],"predecessor-version":[{"id":64,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/29\/revisions\/64"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/41"}],"wp:attachment":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=29"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=29"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=29"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}