Search This Blog

SQL TIPS: SQL Tempdb log is full

16AUG
For those of you who have gotten here with a real issue, here is the fix. If you run this, your server will probably start to cook until the bad tempdb eating query is finished.

USE [master]
GO
ALTER DATABASE [tempdb] ADD LOG FILE ( NAME = N'templog2', FILENAME = N'C:dbtemplog2.ldf' , SIZE = 131072KB , FILEGROWTH = 131072KB )
GO.
Now, we can get into the why part. First, a little about tempdb. This system database is used by sql server for many behind the scenes processes. It is truncated every time sql starts. Its proper configuration is vitally important to performance. tempdb is in the simple recovery model and cannot be changed. If your server crashed you would not restore tempdb so there isn’t a need to back it up.
Simple mode databases overwrite previous log entries once a checkpoint occurs. Linchi Shea points out that huge long running transactions are more likely to fill up a simple log but it is possible to do it with fast enough small transactions. http://sqlblog.com/blogs/linchi_shea/archive/2009/05/21/why-does-my-tempdb-log-keep-growing.aspx
When I ran into this problem, I saw a server that had a bunch of open connections that were waiting on something to actually do some work. No CPU or Disk was active at the time. The SSMS GUI was working slightly but no agent was showing and I would get errors when I tried to open certain features. The one thing I could do is run queries. The problem was shown when I first connected to the server. Now I just needed to fix it and find out why it happened, maybe not in that order.
The first few things that poped up on google are not good solutions or even solutions at all.
1. Backup tempdb log – nope can’t do this because its in simple mode.
2. Backup tempdb log with truncate_only – nope again
3. Shrink the log – bad idea, beside there is no free space…
4. restart sql
Technically, 4 would probably work. However, its a bad idea because you then hinder your troubleshooting abilities. There was probably one bad query or report that once it fails, the user will just try again. If you were in the middle of something, when your server comes back online it might be stuck “in recovery…” Before you grow the log you should try to fire off sp_whoisactivehttp://sqlblog.com/blogs/adam_machanic/archive/2009/12/03/who-is-active-v9-57-fast-comprehensive-dmv-collection.aspx That will show you active sessions, the login, the query and their tempdb activity, all the ammunition you need to stop this problem dead in its tracks.