473,320 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 3636
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: New DB Admin | last post by:
Is it a good thing to do? what are the cons? Are there any risks? (this is an ISP database running 24 hrs) I have sql 6.5 on Win NT with 256 MB Ram (64 MB reserved for SQL Server). tempdb size is...
10
by: Jay Chan | last post by:
I keep getting the following error message when I run a serie of SQL commands: Server: Msg 9002, Level 17, State 6, Line 15 The log file for database 'tempdb' is full. Back up the transaction...
3
by: tom horner | last post by:
Something strange is happening to our SQL Server DB (2000). The tempdb transaction log file continues to grow (quite slowly) for no apparent reason. We have it in simple mode, and I have tried a...
2
by: Warren Wright | last post by:
Hi All, First, where can I get some questions of this sort answered? Preferably, are there good books or online guides that I can consult for these types of answers when necessary? 1. How do...
2
by: Tom | last post by:
I received an error that the log in tempdb was full, but the log and data segments are set to automatically grow with no limit AND there is plenty of available space on the disk. So I don't...
8
by: arijitchatterjee123 | last post by:
Hi Group, I am facing a problem regarding locking. I have created a Stored Procedure in my Database. In this Stored Procedure Temprary Tables get created and after that values are inserted in...
3
by: New MSSQL DBA | last post by:
has anyone met with this before? the setting is SQL2K with SP3 on a 2 node active-active W2K3 cluster. on one of the machine, it occasionally prompts for the following error: "The log file for...
1
by: SQL Server | last post by:
Hi, The tempdb file on one of our servers grew very large and used all available disk space. This is SQL Server 2000 SP4. I have installed hotfix version 8.00.2187. I opened a profiler trace but...
2
by: Thomas R. Hummel | last post by:
I was able to find a few posts on this topic, but none of them quite seemed to fit the situation, so I'm hoping that someone else might be able to help me here. I have a client who is using SQL...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.