Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jamie
Aged Yak Warrior
542 Posts |
Posted - 2003-12-18 : 11:13:30
|
hello,my tempdb on a sql server 7 server has grown to 9010 MBs.9000MB's is free space, I have tried shrinking the db but it did nothing.any suggestions how I can remove this free space ?thanking you in advance,Jamie |
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-12-18 : 11:48:52
|
Did you do dbcc shrinkfile?-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2003-12-18 : 11:53:30
|
thanks chad, just ran dbcc shrinkfile(tempdev)and it has put it down to 200 mbs !(still 190 mbs in the free space).but that should be ok, unless you know of removing al lthe free space ? |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-12-18 : 12:04:40
|
You can try setting the "target_size" to 10. But you should leave some free space for tempdb, otherwise it will just grow again.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2003-12-18 : 14:31:37
|
If this is constantly growing, you should schedule the shrinkdb each night as a job. Any plans to switch off of 7? :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2003-12-19 : 04:35:49
|
thanks for the info guys.I'v enow setup a job to run dbcc shrinkfile every night.Derrick, is this 'feature' only in MSSQL7 ? |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-12-19 : 11:33:49
|
I'm not sure this is such a good solution. If TempDB is constantly growing, you would be better off just leaving it at a pretty large size. Growing and Shrinking are resource intensive operations. What do you really buy yourself by shrinking TempDB if it is just going to grow again?BTW, what "Feature" are you referring to? You can shrink files in 2000 as well (If that's what you meant).-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2003-12-19 : 11:48:08
|
In version 7, it is a good idea to set a minimum size (for example, I know that I will use 200meg a day) and shrink the file. I've had problems in 7 with the tempdb just growing out of control You don't want to shrink it all the way. That would be stupid. Having a process to periodically shrink is just a good maintenance idea though. Also, do you have the latest service pack?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|