469,572 Members | 1,281 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,572 developers. It's quick & easy.

TEMPDB now at 4 GB

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?
Jul 20 '05 #1
3 3501
Deaconess (st**********@deaconessokc.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
Jul 20 '05 #2
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
Jul 20 '05 #3
Deaconess (st**********@deaconessokc.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
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by New DB Admin | last post: by
2 posts views Thread by Tom | last post: by
8 posts views Thread by arijitchatterjee123 | last post: by
2 posts views Thread by Thomas R. Hummel | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.