We help IT Professionals succeed at work.

tempdb is large

TRACEYMARY
TRACEYMARY asked
on
Medium Priority
952 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
Comment
Watch Question

Commented:
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.

Author

Commented:
Its the MDF thats the large one.
Backing up will not reduce the MDF ?

LDF = 12352
MDF = 32472842

Sr. SQL Server DBA
Commented:
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...

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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
For got to say in the presious message. you should investigate what's really eating up tempDB.
It shouldn't keep on growing forever.

Commented:
TraceMary,

execute the statement " BACKUP LOG TEMPDB WITH TRUNCATE_ONLY "

Give some feedback if it works.

Best Regards,

Paulo Condeça.

Author

Commented:
This is my command
dbcc shrinkfile (tempdev, '1000)


Kevin HillSr. SQL Server DBA

Commented:
MrNetic...why would truncating the tlog affect the MDF?

Author

Commented:
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

Author

Commented:
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....


Author

Commented:
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.....
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.