Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

tempdb is large

Posted on 2006-11-08
11
Medium Priority
?
890 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

650 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