Reducing size of tempdb

**NickFitz** · 18 March 2010, 05:51

Originally posted by ThomasSoerensen View Post

Hi all

After having worked with large datasets in a MS SQL Server database my tempdb has grown to 36gb.

Even after dropping/deleting that particular database so I only have the standard ones in the Management studio it does not shrink.
I have tried some of the tricks described when googling this poblem - none have worked.

Do you MS SQL server gurus know of a good way to free up some of my harddrive space and reduce the size of tempdb ? I use the standard version of SQL Server 2008.

Thanks for any help

As one might have expected, Microsoft's advice is on how to "allow for the file to grow until the disk is full." They don't explain why this might be A Good Thing

However, that MSDN document might provide some pointers on how to cope with the problem - although the overall tone seems to be "If tempdb growing is a problem, it's your fault for not allowing it to grow!"

There's a thread on another forum that includes the helpful words:

"If you have activities that leads to usage of TempDB, You should consider using bigger drives or move TempDB file to Bigger drive. Yes ,you can find appropriate time and restart MSSQL services which will create fresh new TEMPDB but make sure nothing is using TEMPDB. You can spread TempDB files accross number of cores in your server to reduce I/O contention issues. If you are inserting lots of records to TEMP TABLE,I would suggest create index to save some space.

Hope that explains everything."

I hope somebody can offer you more assistance than this. The fact that this is a problem to you, apparently a common problem, the software vendor seems to think that it isn't a problem, and the "community" surrounding said software goes along with the vendor, makes me wonder why people use this proprietary garbage in the first place.

Ah, there's something linked to from that forum - maybe this advice (work down to the long-term prevention bit) will help.

Apologies if this is just the stuff you already tried that didn't help

I still think SQL Server is a heap of dung for most purposes...

**mudskipper** · 18 March 2010, 07:13

This might be what you're looking for.

**Weltchy** · 18 March 2010, 07:39

Out of curiosity, is it the ldf or mdf file which is considerably larger?

If its the log file, I'd suggest you need to truncate and shrink it. If this was 2005, then you'd want to perform a Backup Log with Truncate only followed by a DBCC ShrinkFile, however 2008 needs a couple of properties set against the database instead. Off the top of my head, I can't remember what they are though a quick google would reveal them.

**lightng** · 18 March 2010, 07:50

Also look at over-use of temporary tables in your stored procs etc. If you have many users creating creating large user-level temporary tables, tempdb is going to bloat very quickly.

**Spacecadet** · 18 March 2010, 09:23

Originally posted by NickFitz View Post

I hope somebody can offer you more assistance than this. The fact that this is a problem to you, apparently a common problem, the software vendor seems to think that it isn't a problem, and the "community" surrounding said software goes along with the vendor, makes me wonder why people use this proprietary garbage in the first place.

Having used SQL Server now for about 7 years and in quite a few different companies and environments I've only once had a problem with TempDB and that was with a third party application which, when running year end calculations, would make heavy use of temporary objects and TempDB would swell to a huge size.
It's not considered a "problem" by the "community" because a large TempDB is a symptom not a problem

Originally posted by lightng View Post

Also look at over-use of temporary tables in your stored procs etc. If you have many users creating creating large user-level temporary tables, tempdb is going to bloat very quickly.

WHS

**ThomasSoerensen** · 25 March 2010, 11:08

Hi guys

Thank you for your help.
Unfortunately none of it worked but one of the links gave me a hint.
It showed me a small script for viewing the allocated space for tempdb.
This showed me that it was the allocated space i had a problem with.
So I went to database properties for tempdb and allogated less space and kept the allowance for it to grow.

Space has been freed up now.

Reducing size of tempdb