Solved

tempdb is large

Posted on 2006-11-08
11
804 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
  • 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 200 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
 
LVL 2

Assisted Solution

by:stdcitunit
stdcitunit earned 300 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
This article describes some very basic things about SQL Server filegroups.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now