1

I'm trying to create some checklists for troubleshooting various issues, in this case, when TempDb is full. I'd like a prioritized repeatable process where possible to help me out in a hurry.

When TempDB is full, what are the steps you take to troubleshoot that and fix it? Here is what I have so far, but I know it's far from being adequate as I don't yet know the best steps to take to troubleshoot it.

To me, there are a couple of checklists needed for this. When the server will let you connect, and when it won't.

When the server will let you connect:

  1. Run Sp_Who2 - check for any blocks
  2. Run Sp_WhoIsActive to try to see what is filling up TempDB
  3. Kill any SPID's that could be filling up TempDb
  4. Restart the SQL Server service.

When the server won't let you connect:

  1. Restart the SQL Server service.

What other steps do you take, in order, to help you troubleshoot and fix full TempDB?

Thanks for the help!

5
  • 1
    add a new tempdb datafile to make tempdb larger ? delete the extra file off the disk that you put there earlier in order to have room for autogrow ? Oct 12 at 13:24
  • 5
    Enable the Remote DAC so you don't run into the server not letting you connect and you reduce the need to restart things. Oct 12 at 13:53
  • 3
    I don't understand the point of sp_who2 when you have Who Is Active. There's also no analysis of why certain SPIDs are filling tempdb, or of the impact of rolling them back. I guess if you need a L1 support runbook, it's okay, but I wouldn't expect an actual DBA to leave those steps out. Oct 12 at 15:25
  • Thanks @ErikDarling. I'm pretty new to diagnosing TempDB issues, that's why I'm trying to come up with a checklist. I see your points. I've used Sp_Who to see if there are any current blocks, but if it's not needed, it doesn't need to make the list.
    – JM1
    Oct 12 at 15:39
  • 1
    @JM1 To Erik's point, sp_WhoIsActive will tell you if there's any blocks, and pretty much all the same information you can drive from sp_who2 and more, so sp_who2 would just be redundant.
    – J.D.
    Oct 13 at 4:02
1

There is so many use cases..

Here's what I would check first:

1- If you connect and tempdb is no longer full:

  • Usually, this happen because some process ran out of space and SQL rolled back that transaction (releasing space in tempdb). If your Tempdb is small and you can have extra space added to the disk where your tempdb live, this is the easiest approch. Just add space and make sure auto-growth is enable (check for the required trace flag if you have many data file on version older then 2016).

  • If you cannot add space, then you get into the harder step to identify which process is using that much space in tempdb. You can work with the application team (usually, they should get an error in there log when SQL rollback the transaction). You can also try to build some kind of alert when tempdb reach a certain threshold and then run and collect info from sp_whoisactive to identify which query is causing it. Once you know what it is, you can try to tune it so that it uses less space.

2- If tempdb is still full:

  • I then check what is filling up tempdb (temp table, row versionning, etc). I'll also check who is filling it up.

  • Then, if it's row versionning, I'll check for a long running transaction or for a transaction that has an open transaction but doesn't seem to be active. Once I found it, I'll kill that session (If I can reach the dev team first, I usually do it so that I can show them exactly what's going on (there is good chance that if that happen once, it will happen again and most of the time, it needs to be fix in the code)

  • If it's user object (temp table), I'll try to contact the user running the query. If the query can be written in a better way, I'll let him know and help him improve the query. If he simply ran it with a begin tran and forgot to commit/rollback, then I'll ask him to do it. I'm always carefull with killing sessions (specially if they have been running for a while as the rollback could take even longer).

I will not reboot the instance for a full tempdb... this is a very last resort option.

1

Get the TempDBInfo stored procedure from link below, deploy (create) it to any user database

https://github.com/aleksey-vitsko/Database-Administrator-Tools

  1. Run exec TempDBInfo and it will show you TempDB information such number of data files, their fullness, log file, sessions that consume most of TempDB, etc.
  2. Kill the sessions that consume most (if these are really a problem)

No need to restart the SQL Server service after killing the sessions

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.