Shrinking Tempdb on SQL7.0

[cookuk]

I have tried the following methods to shrink the tempdb (which appears to have been filled by a stored procedure using cursors to insert data across 2 databases):-

1. Right-click on the database, All tasks, Shrink database.
2. Query analyzer dbcc shrink database(tempdb,90)
3. Restarting the services

Unfortunately the Tempdb remains 1.7 GB, with only20 Mb actually in use

Any other suggestions?

 

[Corran007]

Try making the 90 a 10 or 5. Thats the amout of free space you want left in the database. So your saying keep 90% of it free.

 

[cookuk]

Cheers.

I see what I did wrong with the dbcc shrink database(tempdb,90) statement, but dbcc shrink database(tempdb,10) has made no difference either.

Of the 1700 Mb file size, 1680 Mb is used.

Any other ideas?

 

[JayKusch]

A couple of questions here ...

1. When you said you "stopped the servives", was SQL Server shutdown or just SQL Server Agent.

2. What is your TLog default startup size and Growth settings at right now? (By that, I mean the settings you see under the TLog tab when you view the properties of the TempDB.)

Thanks

J. Kusch

 

[cookuk]

1. The server was restarted, and hence all the relevant services.

2. The log file is only 1 Mb, the MDF file 1680 Mb

Both are set to unrestricted file growth, growing by 10%.

 

[EBee]

We have the same issue with our SQl 2k database. What I found out is that you can not shrink a tempdb less than it's original allocated size. One way we have done it in 2K is execute an ALTER DATABASE of the tempdb

-erwin

 

[SonOfEmidec1100]

Heres my 2cents, straight from BOL (sql2000), I assume these comments would apply to the tempdb as well as user dbs.

You cannot shrink a database smaller than the size of the model database.

You cannot shrink an entire database to be smaller than its original size. Therefore, if a database was created with a size of 10 megabytes (MB) and grew to 100 MB, the smallest the database could be shrunk to, assuming all the data in the database has been deleted, is 10 MB.

You can shrink the individual database files smaller than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, rather than attempting to shrink the entire database.

 

[SonOfEmidec1100]

I should have added although this is from sql2000 I believe the comments apply to sql 7.0 as well.