I've worked with SQL Server for some time and I understand the various issues that one can run into with TempDB. But, with this latest issue, if I have my issue figured out correctly, is it possible that a statement with a BEGIN TRANSACTION that uses tempdb tables/objects that is left hanging (no commit) can hold the tempdb LOG file "open" and not allow tempdb to clear its own log? This leads to the tempdb log file eventually filling up and causing issues in the SQL Server. I find this hard to believe that there isn't some sort of safeguard in place within SQL Server that would somehow allow the tempdb LOG to not be affected If not, I find this disturbing…
7
-
Have you tried looking at log_reuse_Wait_Desc in sys.databases? That may give you some information as to what is burning up your tempdb log.– Kris GruttemeyerFeb 1, 2017 at 20:24
-
2What would the safeguard be? Committing an unfinished transaction? Killing a running transaction and forcing it to roll back? These have serious consequences for what amounts to user error.– Erik DarlingFeb 1, 2017 at 20:58
-
The safeguard is similar to your seatbelt. If you want to make it so that users can run BEGIN TRANSACTION and hold up the log file forever, you need to provide ample disk space for that. You have a seatbelt in your car but if you choose not to wear it... what more should the car manufacturer do? Refuse to start until your seatbelt is fastened? Shut off automatically if the seatbelt becomes unfastened?– Aaron BertrandFeb 1, 2017 at 21:03
-
IIRC, tempdb doesn't clear its own t-log until 70% full, except on a manual checkpoint.– Kevin3NFFeb 1, 2017 at 21:14
-
ACTIVE_TRANSACTION, also I am unable to access the properties of tempdb via the GUI (SSMS), SSMS hangs so there is an obvious issue, also the transaction appears to be using some sys objects within tempdb, like sysschobjs ... so i'm not sure what is going on here– Eric SwiggumFeb 1, 2017 at 21:27
|
Show 2 more comments
1 Answer
TempDB is no different than any other database. All actions must be logged, and log space will only be reused once the log records for a given transaction have either been committed or rolled back.
As @KrisGruttemeyer said in a comment, you can find out what type of action is causing the transaction log to grow by looking at:
select d.name, d.log_reuse_wait_desc
from sys.databases d
where d.name = 'tempdb';
This will likely show you either 'active transaction' or 'nothing' for tempdb. If you see active transaction you know some transaction is open and has not been committed yet. You can see what transactions are open by looking at various system DMVs including, sys.dm_tran_active_transactions, among others.
For a much more in-depth look at what is causing your tempdb to grow, check out this question.
answered Feb 1, 2017 at 20:37
-
I just find it unfortunate that tempdb's log file can also get taken hostage by someone not closing their BEGIN TRANSACTION statement. HO HUM! Feb 3, 2017 at 15:34