?
Solved

tempdb is large

Posted on 2006-11-08
11
Medium Priority
?
877 Views
Last Modified: 2008-02-01
I have the mdf of tempdb at 33 gig todays
how do i regain back the space..

I did shrink on the mdf but i see all the free space.

Do i do this..
Truncate free space from the end of the file

Can i do whilst activity

Should i set up a job to do it
use tempdb
   go

   dbcc shrinkfile (tempdev, '1000)
   go
   -- this command shrinks the primary data file

   dbcc shrinkfile (templog, '100)
   go
0
Comment
Question by:TRACEYMARY
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 5

Expert Comment

by:MrNetic
ID: 17898754
Could you post the size of the transaction log ?

Then run the following QUERY.

BACKUP LOG TEMPDB WITH TRUNCATE_ONLY

Best Regards,

Paulo Condeça.
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17900541
Its the MDF thats the large one.
Backing up will not reduce the MDF ?

LDF = 12352
MDF = 32472842

0
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 800 total points
ID: 17902311
What is your full ShrinkFile command?

I typically will shrink in 100mb increments in a loop...doing it all at once can have a negative performance impact...
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 2

Assisted Solution

by:stdcitunit
stdcitunit earned 1200 total points
ID: 17904337
That's rather big for tempDB.
Do you have some run away process or something?
Is this DB required to provide 24/7 availability? If not, try restarting SQL. That's clear out TempDB. You cuold also drop tables if you really have to.
Also, check out the ODBC DSN on the clients (if using ODBC). This link might help you.
http://www.databasejournal.com/features/mssql/article.php/1459101
0
 
LVL 2

Expert Comment

by:stdcitunit
ID: 17904492
For got to say in the presious message. you should investigate what's really eating up tempDB.
It shouldn't keep on growing forever.
0
 
LVL 5

Expert Comment

by:MrNetic
ID: 17904695
TraceMary,

execute the statement " BACKUP LOG TEMPDB WITH TRUNCATE_ONLY "

Give some feedback if it works.

Best Regards,

Paulo Condeça.
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17905010
This is my command
dbcc shrinkfile (tempdev, '1000)


0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 17905366
MrNetic...why would truncating the tlog affect the MDF?
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17905843
Hey i ran this...............as a job last night
use [tempdb] DBCC SHRINKFILE (N'tempdev', 1024)

And it worked its now down in size..........

Thanks all

I happened to be off tuesday and i get in wed to see the size of 33 gig i was oh my ....

Thanks all
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17905862
The ODBC
Reveiwed all the client machines' ODBC DSNs. (You can do this with Control Panel). There is a box that says "generate temporary stored procedures for prepared statements". Unchecked this box. SQL Server itself is pretty good about freeing up the objects it creates in tempdb, ho wever, ODBC is not, and often creates objects it doesn't really need to create. This resolved my specific problem.

So unflagging this "does not create the temporary stored procedures" on the sql side....


0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17929827
Im looking on ODBC
Reveiwed all the client machines' ODBC DSNs. (You can do this with Control Panel). There is a box that says "generate temporary stored procedures for prepared statements". Unchecked this box. SQL Server itself is pretty good about freeing up the objects it creates in tempdb, ho wever, ODBC is not, and often creates objects it doesn't really need to create. This resolved my specific problem.


But i cannot see thebox.....
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question