0

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…

Eric Swiggum
  • 618
  • 1
  • 11
  • 29
  • 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 Gruttemeyer Feb 01 '17 at 20:24
  • 2
    What 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 Darling Feb 01 '17 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 Bertrand Feb 01 '17 at 21:03
  • IIRC, tempdb doesn't clear its own t-log until 70% full, except on a manual checkpoint. – Kevin3NF Feb 01 '17 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 Swiggum Feb 01 '17 at 21:27
  • You should get used to accessing properties and system status without using the GUI as a crutch, for exactly this reason (also, sometimes you need to connect as the DAC, which is allowed only a single connection, and those pesky GUIs take multiple). – Aaron Bertrand Feb 01 '17 at 21:36
  • I was just showing that there was some issue with tempdb, i've never been blocked from performing simple maintenance like this whereas SQL Server continues to run "fine" otherwise. – Eric Swiggum Feb 03 '17 at 15:36

1 Answers1

3

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.

Community
  • 1
Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
  • 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! – Eric Swiggum Feb 03 '17 at 15:34