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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-01-11 : 06:53:03
|
| James H. writes "I am running SQL Server 2000 SP4. There was a lot of heavy activity on a database with full recovery mode set last week and it caused my tempdb datafile (tempdev) to grow to 77GB. I have since set the database in question to simple recovery mode, but am unable to get the size of the tempdev datafile to go down. Properties on the tempdb show sizes: 75888.67MB w/space avail: 75887.43MB.. I have tried shrinking (via EM) and it says it worked fine, however the size reduced little on the first try, and none on subsequent tries.. From reading I found that tempdb is recreated at initialization, so I bounced the instance, and found no change. I then shut down my instance, adn then removed my datafile to another drive.. this caused a panic attack on my part because the service started, but became unresponsive. I discovered later that it was rebuilding my 77GB tempfile and wouldn't communicate until then (big WHEW).. I still have 25GB free, but need to reclaim as much of the space that tempdb is holding. Can you give me some pointers?Regards,James H." |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-11 : 11:58:28
|
| I'm confused - you refer to tempdb, tempdev, tempfile, my datafile. You say that there is heavy activity in a database with full recovery and then refer to tempdb. Is your user database a problem or is tempdb a problem, they are two very different things? |
 |
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-01-12 : 07:03:24
|
| Do reduce the size of your tempDB all you need to do is to restart the SQL Server Service Either using Service Manager or Physically Restarting teh server. When SQL Server Starts it creates tempdb from ModelThe more interesting question is what was happeinging in your user database that generated so much work for tempdb?-- RegardsTony The DBA |
|
|
|
MohammedU
Posting Yak Master
145 Posts |
|
|
|
|
|
|
|
tempdb problem