truncate tempdb

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » truncate tempdb

truncate tempdb

Rate Topic

Display Mode

Topic Options

Author

Message

chandrika5

Posted Tuesday, November 13, 2007 8:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 10:59 AM
Points: 142, Visits: 305

One instance of SLQSERVER has temp db 61 GB:
Db = 8 GB approx, Transaction log 54 Gb approx.
This is sqlserver 2000 sp4 clustered on 2 nodes.
Which is the best way to truncate the tempdb without bouncing the enigne or failover to the other node.

The SQL instance is on a virtual server, we cannot set up mail alerts as there is a policy of not having a mail profile on servers.
Thanks,

Post #421636

Steve Jones - SSC Editor

Posted Tuesday, November 13, 2007 8:51 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:14 PM
Points: 31,421, Visits: 13,734

tempdb is running in Simple mode, so it should keep itself trunacated on a regular basis. The only thing that remains in there is an open transaction. If you had some large import that required it to grow, then it can be "shrunk" using DBCC SHRINKFILE, but shrink the individual files, not the entire database.

However, if you require this much tempdb space, and you might if you really need a 50GB log file for an 8GB database, you aren't gaining anything by shrinking it. It will just grow again. A large log file or tempdb doesn't necessarily imply a problem.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #421642

chandrika5

Posted Tuesday, November 13, 2007 9:20 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 10:59 AM
Points: 142, Visits: 305

Thanks Steve for your quick reply. I did not do a large import/bulk import. This instance is transaction intensive. Will check the connections.

Post #421658

chandrika5

Posted Tuesday, November 13, 2007 10:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 10:59 AM
Points: 142, Visits: 305

Bounced the engine that cleared the 55 GB LDF.
Upon re-start : shrink the MDF to 1 MB. This is set to autogrow.
;)

Post #421701

Buxton69-502449

Posted Wednesday, November 14, 2007 8:02 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 19, 2009 8:32 AM
Points: 196, Visits: 465

You should size the Tempdb to a size it uses on a regular basis, if you log size stats you should analyse these to determin what size it should be, if you keep shrinking the file and it's set to autogrow then the autogrow action itself will have a negative effect on performance each time it grows.

Post #422109

« Prev Topic | Next Topic »

Permissions