Runaway TEMP.mdf file

[SantaMufasa]

I just received a frantic call from a system administrator (who has no on-site DBA). Apparently, on their production SQL Server instance, their TEMP.mdf has grown from 6GB to 69GB in about 1 1/2 days. This has caused their instance to hang due running out of space.

He is currently doing a shrink on the TEMP.mdf, but when he finishes, we must isolate and resolve what is causing that runaway growth to occur.

What steps can you recommend I go through to isolate what is causing that runaway behaviour?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ptheriault]

Look for long running queries.

If you can reboot the system it will recreate the tempdb.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SantaMufasa]

Paul,

Thanks for the response. When you say "reboot the system," do you mean the SQL Server instance or the hardware?

And to help me get my arms around SQL Server's use of the TEMP.mdf, could you please clarify when it comes into play? In Oracle, TEMP segments generally result from[ul][li]large queries that involve an ORDER BY that cannot occur in memory, and/or[/li][li]index creations that (also) require significant sorting of data.[/li][/ul]Are TEMP.mdf objects used for the same purpose, or is it more than that?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[gmmastros]

Santa,

I would encourage you to check a couple things first. There is a system database called TempDB that is used for various things. Whenever you create a temp table, it's created in TempDB. There are a lot of other times that TempDB is used.

The part I find a bit strange is... On my computer, TempDB uses the 2 following files.

C:\Program Files\Microsoft SQL Server\MSSQL\data\[!]tempdb.mdf [/!]
C:\Program Files\Microsoft SQL Server\MSSQL\data\[!]templog.ldf[/!]

Since you said temp.mdf, and not tempdb.mdf it is possible that you have a 'regular' database named Temp and are actually referring to that database instead.

I would suggest that you run this:

Select name, filename From master..sysdatabases

This will show you all of the databases on your sql server instance, and more importantly, the files associated with those databases.

It's important to know which database this file is associated with.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ptheriault]

Good point George, I just assumed that his temp.mdf was his tempdb.

Dave,
just by restarting the SQL services you will create a new tempdb if in fact that is the db in question here.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Paul,

Do you recommend setting the recovery mode to 'simple' to fix this?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SantaMufasa]

All,

Upon further investigation, the runaway-file problem is with the standard, db-instance TEMPDB. (Sorry for my mis-statement in my earlier post(s).) Additionally, I have discovered that this SQL Server instance is not only their production server, but they are also using this instance for their development platform (...risky, I know, but that is how they're doing it). Further, they are doing significant legacy-to-new-system data migration on this machine.

Therefore, the massive growth of the TEMPDB-related files could be justified/explained. The fact that the TEMPDB-related files seem to grow unchecked and do not appear to reduce in size upon completion of whatever task is ballooning their sizes is the current worry.

Have you suggestions of what we can check (or set) to deal with this debilitating occurrence?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ptheriault]

Dave,
What are there production hours? Are the 24X7? My suggestion would be a simple restart of the SQL Instance. IF they can have the 5 min to restart. If not you can try DBCC SHRINKDB.

That is very risky having prod and dev on the same box and running migration scripts. They are lucky this is the only problem they are seeing.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[JayKusch]

Jsut to add my two cents here ...

If they are using the TempDB pretty heavily, it may be adventageous to create a couple more files for tempDB especially if they have a multi-proc box.

Best Practices would dictate setting on file per processor. So if you have a dual proc - dual core box, you would create 3 more NDF files for tempdb.

You should also set the initial size the files are built out to to some size that, when all the file groups are added together, they equal the normal working size of the tempDB plus a bit more room based on you knowledge and judgement.



Thanks

J. Kusch

 

[SQLWilts]

Just a thought and it may be way out of line here, but a client of mine had exactly the same problem. they put ina call to Micro$oft who worked on it and declared it a bug in SQL server. they have fixed this issue in a post SP4 hot fix. Can you tell me what SP level you are at?