shrink tempdb

[PS1]

Hi All,
I have tempdb of allocated space 10 GB of which only 1% is used (SQL Server 7.0).
I need to free up the unused space, but shrinking this data base is not working.
Could you pls help me with this?

Best regards,
Piotr

 

[tlbroadbent]

Use DBCC SHRINKFILE. Or stop and restart the SQL Server service to rebuild tempdb. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[PS1]

Hi!
I tried DBCC SHRINKFILE (both via Query Analyzer and Enterprice Manager) before and it doesn't shrink the DB. After restarting the SQL Server Services tempdb still has the same size.
Do you have any other ideas?

Best,
PS

 

[PS1]

Hi!
I tried DBCC SHRINKFILE (both via Query Analyzer and Enterprice Manager) before and it doesn't shrink the DB. After restarting the SQL Server Services tempdb still has the same size.
Do you have any other ideas?

Best,
PS

 

[tlbroadbent]

What is the size of the model datbase? Tempdb is recreated from the model DB. Also, a database cannot shrink smaller that the model DB.

Did you use a SQL script similar to the following in Query Analyzer?

Use tempdb
go

dbcc shrinkfile(1,10)

SQL Should attempt to shrink tempdb to 10 MB with the statement above. You may want to change the 2nd parameter value to a larger or smaller target size.

Note: if the server is busy, you may need to issue the DBCC command multiple times to get tempdb to shrink. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[PS1]

Hi!
Thank you for your suggestions. I tried them, but they didn't work (the model DB was quite small - a few MB...).

I managed to shrink the tempdb via SQL Server 2000 Enterprice Manager (my SQL version is 7.0) using the All Tasks->Shrink Database feauture - I don't know why, but it works.

Best,
PS

 

[tlbroadbent]

I'm glad you were able to shrink the file. FYI - EM simply executes DBCC SHRINKDATABASE or DBCC SHRINKFILE when you select Shrink database. Shrinking from Query Analyzer and Enterprsie Manager are equivalent operations.

I suspect that you may not have refreshed the view in Enterprsies Manager after running SHRINKFILE in QA. The file size info doesn't automatically update in EM. Running SHRINKFILE from EM does refresh the DB and file size info because EM issues commands to update the stats. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[PS1]

Hi!
I did refresh it. After running the Shrink Database function in EM you get a MsgBox with info about the size after shrinking and it indicated the same size as before...
It's curious, isn't it?
Thank you for your efforts.
Best,
PS