tempdb is large

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
LVL 7
TRACEYMARYAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MrNeticCommented:
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.
TRACEYMARYAuthor Commented:
Its the MDF thats the large one.
Backing up will not reduce the MDF ?

LDF = 12352
MDF = 32472842

Kevin HillSr. SQL Server DBACommented:
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...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

execute the statement " BACKUP LOG TEMPDB WITH TRUNCATE_ONLY "

Give some feedback if it works.

Best Regards,

Paulo Condeça.
TRACEYMARYAuthor Commented:
This is my command
dbcc shrinkfile (tempdev, '1000)


Kevin HillSr. SQL Server DBACommented:
MrNetic...why would truncating the tlog affect the MDF?
TRACEYMARYAuthor 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
TRACEYMARYAuthor 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....


TRACEYMARYAuthor 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.....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.