What happens when tempdb fills a disk?
-
I am having a problem with a tempdb growing so large that it fills up the disk. The server holds a SQL Server 2000 data warehouse.
Drive D: is the 410GB data drive, with 136GB free. (2 data files for the warehouse, 22GB & 24GB).
Drive E: holds the indexes, 135GB with 69GB free.
Drive F: holds tempdb, 135GB with 21GB free.
The server guys have Tivoli monitoring disk usage. When Drive F: gets down to 1% free, it pages the server guy on call, typically in the middle of the night on a Friday when we are doing a load. What happens when tempdb can no longer grow? How will it affect SQL Agent jobs that are currently running?
There is no "i" in team, but idiot has two. -
Hello Dave,
Possible reasons for Tempdb getting filled might be
1. Temporary objects being created and not dropped at all.
2. Lots of DML operations carried on without transaction handling.
Hope this might give you some lead for you to proceed further.
Thanks
Lucky -
The warehouse load is a big, hairy monster that I hesitate to disturb. The server guys want to turn off monitoring so they don't get a page when the disk fills up. Instead, I will hear about it Monday morning. I am OK with that so long as it doesn't break anything. Can I set up a job to periodically shrink it? I also notice that it is set up to autogrow by 1GB at a time. If I set that to a smaller value (50MB?), will that buy some time for whatever heavy user of tempdb to complete without grabbing the whole disk?
There is no "i" in team, but idiot has two. -
I do not see why not if it can be shrunk/truncated ( I have not shrunk tempdb trans log before so test )MSSQL would not let you truncate an active portion ....
Then find the root cause of your problem ...
-
You can truncate and shrink it, but if there's a process that needs that much space it will grow again. The idea is to size tempdb at it's max, so it doesn't need to grow.
-
Now that I think of it, since my tempdb is the only occupant of F:, there's really no reason to not size it to 146GB and be done with it. If it can't grow any bigger, and it fills up, what happens next? Does SQL Server just stop doing useful work? And is there anything I can do about it?
There is no "i" in team, but idiot has two. -
Nothing works without TEMPDB. If it grows too big, it will have a chain-reaction down through the rest of the databases, to include making them temporarily unusable. An emergency solution is always to stop and start the SQL Server services. That causes TEMPDB to rebuild at its original size.
-SQLBill
-
Or, option B, what if I add a file to tempdb on my D: drive (which has 136GB free)? If I don't let tempdb autogrow, but instead set the primary file to 146GB and the secondary to 25GB, it should spill over from F: to D: when necessary, and that should be big enough. Or am I going about this the wrong way?
There is no "i" in team, but idiot has two. -
- I suggest you start sqlprofiler to find out what is filling up you tempdb.
146Gb is quiet a volume for tempdb. It must be holding _all_ your data;
My guess is there must be running a bunch of "select distinct * .." from a number of crossjoins or so...
Analyse the load an optimize the queries. e.g. by selecting only what is actualy needed, ...
- There are articles and refs in BOL regarding which activity is handled in tempdb.
- off course... it is a DW server and maybe it's considered normal to actualy throw everything together
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Heavy activity in the tempdb database can drag down your application's performance. if the app is using more large temp tables, temporary stored procedures, the tempdb is occupied.
some of the temp. tables are not getting de-allocated or closed.
Use profiler to find the SP or query that use them and try to fix it.
-
You should try to figure out why tempdb growth is out of hand and fix the problem. But the options you mention are also good ideas.
You should size tempdb to fill the F: drive instead of using autogrowth. You could add another tempdb data file on D: for overflow, but you don't want it to take over. It won't wait until it fills up on F: before overflowing to D:, it will use both files by allocating pages in them proportion to the amount of free space in each. So if you want to limit the use of D: except for emergencies, you might want to make the file only 1GB or smaller (with a 1GB or larger growth increment) and shrink the file on D: back to the original size as often as necessary (say after your load job finishes).
Even better would be to add another disk and put a second tempdb file on that, sized to fill up the disk (and exclude any other use of the drive). A recommended practice is to have the same number of files in tempdb as you have CPU's on the server (real CPUs or cores, don't count hyperthreading).
-
Make sure TempDB is set to "simple" recovery mode... kill the developers that wrote cursors, views of views, and monster CTE's.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply