November 13, 2007 at 8:45 am
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,
November 13, 2007 at 8:51 am
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.
November 13, 2007 at 9:20 am
Thanks Steve for your quick reply. I did not do a large import/bulk import. This instance is transaction intensive. Will check the connections.
November 13, 2007 at 10:51 am
Bounced the engine that cleared the 55 GB LDF.
Upon re-start : shrink the MDF to 1 MB. This is set to autogrow.
😉
November 14, 2007 at 8:02 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply