January 14, 2003 at 10:19 am
Our tempdb has grown to 3 GB, although only 1 percent currently used. I am unable to reduce this allocation under Properties, Data Files. Can anyone help?
January 14, 2003 at 10:28 am
Restart the server should bring it back down.
I assume you've right clicked in EM and done shrink. If not, do that.
Any errors in the logs? Might also try this shrink script:http://www.sqlservercentral.com/scripts/contributions/26.asp
Steve Jones
January 14, 2003 at 2:44 pm
TempDB is not really supposed to be shrunk for the simple reason it is where a lot of work occurrs and it sizes itself. If stopping, (sometimes deleteing the .mdd and .ldf files for it) and restarting does not shirnk then certain factors exist that make SQL assume that is the logical size to make the DB. I believe it has to do with the largest table or some factor of the largest DB but I forget where I saw the details. If you absolutely need to shrink then the only way I found was using SQL 2K Client EM, drill to tempDB on the server, right click and choose task->shirnk database. There you can choose to shrink the DB to a target size in files dialog, but again unless there is an absolute reason to do it is not suggested.
Keep in mind
1) All temp tables are stored here.
2) Joins may require a work table be built to properly perform and they are here.
3) Order by on large datasets may require work table and again is stored here.
4) There are other things that generate work tables and tempdb is the home for this.
January 14, 2003 at 5:42 pm
Restart didn't work;
Shrink under EM didn't work;
Tried the script but got:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
Maybe because I'm on SQL 2K?
January 14, 2003 at 8:42 pm
When you stopped SQL Server did you goto the location on the file system to make sure the tempdb log and data file were delete. I have seen on a few occasion the file get hung and SQL reattach to it when it should delete and rebuild anyway. SQL 2K has given me know issues in the past when I forced a 36Gb file down to 4GB (It was because of a hugh database we got rid of but the file did not resize).
January 16, 2003 at 3:20 pm
Was able to Shrink database, resize the *.mdf file. This seems to be OK. Think I'll change the auto-growth method from "Grow file by 10%" to Grow by 50 MB.
Still learning.....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply