{"id":1756,"date":"2019-02-02T16:32:22","date_gmt":"2019-02-02T16:32:22","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=1756"},"modified":"2019-02-02T16:32:22","modified_gmt":"2019-02-02T16:32:22","slug":"error-9002-severity-17-state-4-the-transaction-log-for-database-tempdb-is-full-due-to-active_transaction","status":"publish","type":"post","link":"https:\/\/www.theSQLReport.com\/?p=1756","title":{"rendered":"Error: 9002, Severity: 17, State: 4 – The transaction log for database ‘tempdb’ is full due to ‘ACTIVE_TRANSACTION’"},"content":{"rendered":"

Problem:<\/strong><\/p>\n

This was a compound problem I had on a production SQL Server 2014 instance.\u00a0 First was someone who scheduled a couple of load jobs at the same time which grew the tempdb to filling its hard-drive.\u00a0 Next was a well intentioned rookie DBA that shrunk the tempdb log file and turned off auto growth, in order to stop the alerting that notified us that the hard-drive was filled.<\/p>\n

Shortly after that we started getting the following alerts (also in SQL Server’s error log):<\/p>\n

Error: 9002, Severity: 17, State: 4.<\/div>\n
The transaction log for database ‘tempdb’ is full due to ‘ACTIVE_TRANSACTION’.<\/div>\n
<\/div>\n
\"Error:<\/a><\/div>\n
<\/div>\n

Resolution:<\/strong><\/p>\n

Note:\u00a0 images and example was done on a test system, and TempDB should never be put on be put on the C-drive for a production environment.<\/em><\/p>\n

To correct this issue, I needed to “right size” (shink) the TempDB data files.\u00a0 I used the following query to find the current data file sizes:<\/p>\n

--\r\n-- Query to show the Logical Name, Physical File Location, \r\n-- & the file size in MB for TempDB\r\n--\r\nSELECT \r\nsys.master_files.name as 'Logical Name', \r\nsys.master_files.physical_name as 'Physical File Location', \r\n(sys.master_files.size*8)\/1024 as 'Size in MB'\r\nFROM sys.master_files WITH (NOLOCK)\r\nJOIN sys.databases WITH (NOLOCK) \r\non sys.master_files.database_id=sys.databases.database_id\r\nWHERE sys.databases.name = 'tempdb' and sys.master_files.type = 0\r\nGO<\/pre>\n
\"Query<\/a><\/p>\n
My normal attempts to shrink the data files for TempDB failed.\u00a0 What I found that if I cleared the plan cache (using DBCC FREEPROCCACHE), and ran the shrink statements all at once, the data files did shrink.\u00a0 Note that I was not excited about clearing the plan cache on a production system, however the jobs were failing because the TempDB issues.\u00a0 Using the query results from above to give the logical file name, and adjusted the file size in MB using (DBCC SHRINKFILE).<\/p>\n
USE TEMPDB;\r\nGO\r\n-- Clear the Plan Cache\r\nDBCC FREEPROCCACHE;\r\nGO\r\n-- Rightsize the TempDB files \r\nDBCC SHRINKFILE(tempdev, 1024);\r\nGO\r\nDBCC SHRINKFILE(tempdev2, 1024);\r\nGO\r\nDBCC SHRINKFILE(tempdev3, 1024);\r\nGO\r\nDBCC SHRINKFILE(tempdev4, 1024);\r\nGO<\/pre>\n
\"Shink<\/a><\/p>\n
Once this ran, I reran the query from above to verify that the files did shrink.<\/p>\n
\"Verify<\/a><\/p>\n
Also I verified the free space on the drive used by TempDB.\u00a0 Then I resize the TempDB’s log file, made sure that is growth would no longer fill up the hard-drive.\u00a0 Finally I made sure auto growth was turned off for data files on TempDB now they were right sized.<\/p>\n
My example in this post is a very small TempDB, however in my production environment TempDB is over 100 GB.\u00a0 Anyone running something on this production environment that requires more then that 100 GB in TempDB is going to have to explain why they need all that space & probably will pay for the addiction storage for TempDB’s growth.<\/p>\n
Thanks to Tara Kizer<\/a> who’s post gave me the idea of clearing the plan cache.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Problem: This was a compound problem I had on a production SQL Server 2014 instance.\u00a0 First was someone who scheduled a couple of load jobs at the same time which … Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":1757,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[148,150,147,149],"class_list":["post-1756","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-active_transaction","tag-error-9002","tag-tempdb","tag-transaction-log"],"_links":{"self":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1756"}],"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=1756"}],"version-history":[{"count":7,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1756\/revisions"}],"predecessor-version":[{"id":1767,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1756\/revisions\/1767"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/1757"}],"wp:attachment":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1756"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1756"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1756"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}