February 19, 2015 at 8:50 am
I am running a SQL Server 2012 Enterprise Edition instance that hosts 19 databases. The server is part of a cluster with three instances. The cluster has 40 processors and this particular instance has 128 GB of memory allocated to it.
My problem is with the TempDB transaction log. We have had multiple instances where the TempDB log file has grown to the point that it filled up the available disk space. When this happens, we get continuous messages in the error log that state: "The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'." "Error: 9002, Severity: 17, State: 4."
The TempDB has 4 data files that are each 10 GB and the transaction log file is set to 200 GB. (We increased the log file just to try and identify the actual amount of usage needed.) I track database file growth and the data files do not grow but the log file grows repeatedly.
I have researched this extensively but I have not yet found a good solution that will help me properly monitor TempDB so I can really tell what is causing the log file to grow so much. I get partial answers or some scripts without any explanations but nothing that I have found that will really help me put together any kind of monitoring solution.
If anyone has their own solution that they are willing to share, or knows of a source I could use to help put together some sort of monitoring solution, I would greatly appreciate any help you could give me.
Thanks!
----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011
February 19, 2015 at 9:20 am
This post should be the answer you're looking for: http://dba.stackexchange.com/questions/19870/how-to-identify-which-query-is-filling-up-the-tempdb-transaction-log
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply