April 28, 2003 at 8:05 am
My tempdb database is 28 GB. I have stopped and restarted the SQL service and rebooted the server and the tempdb comes back online at 28GB. My understanding is that the tempdb database should be recreated from model each time the sql service is started. Model is 640K. Do I need to do anything special to get tempdb to be recreated?
April 28, 2003 at 8:13 am
Have you tried to shrink the database?
Jeremy
April 28, 2003 at 8:19 am
Tempdb is recreated to a default size defined in master not based on model (I believe it is the size value in sysaltfiles). However, it is not uncommon the file is not deleted and when you shut down the SQL Service may need to delete yourself to correct. Delete the tempdb file then restart the service and see if return to 28GB.
April 28, 2003 at 10:57 am
Have you tried using Enterprise Manager, right clicking on TEMPDB, selecting properties and then checked to see the default size of the database? That's what TEMPDB will start at when it's rebuilt.
-SQLBill
April 28, 2003 at 4:10 pm
Just run dbcc shrinkdatabase(tempdb)
from DA. it's that simple.
Robert
April 28, 2003 at 4:11 pm
I am sorry, run it from query analyzer.
April 29, 2003 at 5:03 pm
I have tried to shrink the database using DBCC ShrinkDatabase. This appears to be just removing the free space in the database. (The database was previously 30 GB, shrinking it brought it down to 28 GB.)
I have tried stopping the sql service, then manually deleting the tempdb file through windows explorer (actually I just renamed it.) SQL does create a new file called tempdb, but the sql service won't start back up until you put the old 28 GB file back in place. By the way, the new tempdb file was 0 KB.
I don't see anything about a default size on the properties dialog box of the database. I see current size, but not default size.
Any help would be greatly appreciated. I am hurting for disk space and am at a loss...
Thanks.
April 29, 2003 at 5:19 pm
quote:
My tempdb database is 28 GB
Did you rename both MDF and LDF files of TEMPDB?
I was able to recreate tempdb with method Antares686 suggested in SQL Server 2000 & 7.0.
How large your LDF and MDF file of your tempdb? You can find out from EM. Try DBCC Shrinkfile to shrink MDF and LDF files separtately.
Edited by - Allen_Cui on 04/29/2003 5:26:54 PM
Edited by - Allen_Cui on 04/29/2003 5:33:29 PM
April 29, 2003 at 7:44 pm
1 additional note I have found, sometimes the shrink database piece doesn't work with tempdb. But even if you have no SQL 2K machine using the SQL 2K EM on the DB to shrink to a specific size always seems to work.
May 2, 2003 at 7:25 am
DBCC ShrinkFile did the trick. Thank you! The file is now 4 GB.
By the way, anybody know why shrinkdatabase wasn't working? The result set it returned stated that the current size of the database was 64 pages after the shrink, but the windows file was actually 28 GB.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply