TEMPDB now at 4 GB

[Deaconess]

I have shut down the SQL agent, rebooted the box and still my TEMPDB
is at 4 GB plus. Is there any way to shrink it another way?

[Erland Sommarskog]

Deaconess (st**********@d eaconessokc.org ) writes:

I have shut down the SQL agent, rebooted the box and still my TEMPDB
is at 4 GB plus. Is there any way to shrink it another way?

Did you try DBCC SHRINKFILE?
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Deaconess]

Will DBCC Shrinkfile need to run in single usermode or can I run it
against TEMPdb with users online to other databases on the Sql Server

[Erland Sommarskog]

Deaconess (st**********@d eaconessokc.org ) writes:

Will DBCC Shrinkfile need to run in single usermode or can I run it
against TEMPdb with users online to other databases on the Sql Server

Did you check Books Online? It says:

The database being shrunk does not have to be in single-user mode; other
users can be working in the database when the file is shrunk. You do not
have to run SQL Server in single-user mode to shrink the system
databases.

What does not spell out equally clear is that Books Online is that
DBCC SHRINKFILE can take a lot of time, because it moves things
around, to compact the database. I suppose this can incur some load.

But there is a remedy for this, the TRUNCATE_ONLY option. On a tempdb
that swollen up because of a occasional extreme query, this is likely
to have good effect.

You should probably try to figure out what you think is a good size
for your tempdb. If that is, say 100 MB, and you shrink it all the
way down to 10 MB, you will face autoloads that takes load on the
machine.

If your tempdb after shrinking, again swells up to 4GB, you need to
investigate what might be causing this.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp