May 21, 2007 at 2:51 pm
Guys,
I recently deleted a database and then restored it from the backup. Since then I have noticed a strange behavior with tempdb database. It is constantly growing (~ 1GB per hour), until it fills up our whole hard disk space.
Currently I set up a job to run periodically to shrink it, which works like a charm. However, I see it as a temporary patch. I still want to find out what is causing it.
Can those with experience with tempdb growth share their experiences/suggestions?
Thanks a lot
May 22, 2007 at 5:56 am
Lots of things use TempDB, so this may be tricky to sort out. Start by looking at the table in TempDB itself to see if they were created by users or stored procedures (# or ## tables).
If they have not, you may have a lot of data spilling over into TempDB regularly is normal SQL operations. This is often a sign of low memory or incorrect memory settings. When SQL generates tables for joins, it tries to put them in memory, but if they do not fit, it uses TempDB.
What prompted your restore?
May 22, 2007 at 7:56 am
Michael, thanks for the suggestions ... I'm looking at it right now.
As for the restore, we basically have 2 servers with identical settings. One of the servers had to go down for a while. Once we brought it back up, we had to synchronize the 2 servers. The easiest way to do it seemed to back up the database on the working server, delete the database on the previously non-working server and then restore the backup.
This is what I did. After that, the server where db was restored began to have this strange behavior with TEMPDB. Should I have done something else after the DB restore was made?
Thank you
May 22, 2007 at 8:24 am
No, probably not. Look at the memory setting for the SQL server install on both servers and make sure they are both the same. You should also look at your page faults/sec performance counter to see if you have a memory problem - it is not that likely, but it is worth looking at.
Without looking at the server directly, I would guess that something in the configuration got changed while it was down and SQL is using TempDB more than it should be. Just run sp_configure on both servers and compare the results.
May 22, 2007 at 8:24 am
It is an interesting problem. I would like to check if there is any other process running against this server. For example, some people create temp tables and never drop them.
May 22, 2007 at 8:28 am
You can find user defined temp tables in TempDB pretty easily: select * from tempdb.dbo.sysobjects where type = 'u'
If it has a name starting with a # a bunch of filler underscores, and a number at the end of the name - a user created it. The sysobjects table as a crdate field that will tell you how long the temp table has been in existance.
May 22, 2007 at 10:02 am
Michael,
I just ran sp_configure - the configurations on the two are identical.
As for temp tables, they appear to be constantly created and deleted (i.e. as I run the command[select * from tempdb.dbo.sysobjects where name like '#%'], I see them, and next second I do not see them) - in both environments. The difference, however, is that the TEMPDB transaction log of one environment is constantly using < 100MB, whereas another one is constantly growing (now ~5GB). It is as if the storage is not being deallocated at all in this faulty server.
What can prevent deallocation of space of the temp tables in tempDB?
Thanks a lot guys
May 22, 2007 at 10:31 am
First thing I would try is to reboot the server then examine the System and Application event logs along with the SQL Server errorlog. Next I would take a look with sp_who or sp_who2 to see what process(es) are using tempdb directly (if any). Lastly I would fire up Profiler.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 22, 2007 at 11:01 am
What is the recovery model set to on your TempDB?
It should be Simple so the transaction log is truncated on each checkpoint.
May 22, 2007 at 11:08 am
Michael,
The recovery model is simple - and I don't think that anyone has control over making it not Simple. As for truncation, it is as if it does not happen. How can I check if the checkpoint occurred?
Is there a way to do it?
Thank you
May 22, 2007 at 11:18 am
I don't think there is a way to verify checkpoints are happening, but you can do them manually just by running CHECKPOINT in query analyzer. I doubt they would not be happening.
It is more likely that whatever is causing the growth is actually in a single transaction so the checkpoint cannot cut it into pieces. This would also suggest that you can find the problem by waiting for a temporary table that stays in TempDB for a relatively long period of time.
I don't know if it is good or bad news, but none of this is likely to have been related to your backup and restore operation and is more likely a coincidence that it happened around the same time. If you have not done it already, rebuild TempDB (just stop SQL, delete or rename the mdf and ldf files, and start SQL again).
At this point, you are probably going to have to use profiler to trace the activity on the server if you are to find the culprit.
May 22, 2007 at 3:54 pm
Guys,
I rebooted the machine ... and the problem seems to have vanished. Now my transaction log for the TEMPDB database is at 2GB, but it is using at most 70%. It actually drops down to ~2% every now and then, and then goes up to ~70%, but never exceeds that.
I am not sure what the problem was, but apparently, some hanging process/query, was filling up TEMPDB, indefinitely. I learned, once again, that a simple reboot could solve a big problem.
Thanks for all the valuable input!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply