TRN File Really Large

[schwarem]

I have databases set for simple backup, but my TRN files are growing to be really large. How do I shrink the TRN files and how do I prevent them from growing.

 

[Catadmin]

If you want to shrink the .TRN file, set the database's recovery mode to Bulk-Logged or Full temporarily and run the DBCC ShrinkFile command (see Books Online for more information on it). Then reset your DB to Simple mode.

To stop the transaction log from growing past a certain point, go into the database properties and give it a specific size. However, be aware that even in simple mode, the transaction log is used as a kind of cache to hold the transactions as they process. So it will still need some growth room. The main thing about Simple is that it doesn't keep the log after the transaction finishes processing.

You'll want to run Profiler to see what job/query is causing your log to grow so much. Then check the offending piece of code for optimization possibilities. It might be opening a transaction and not closing it.

Hope this helps. If anyone sees I said anything wrong, please correct me. Thanks,



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"