SQL Server Performance Forum – Threads Archive
Transaction log file grown to very large size
HIi have a produsction database . where i have a very critical database server with atleast 2000 transactions per minute now i am facing a problem my transaction log file has become of a size of 6 gb but my database is just 448 mb what should i do i am little worried aboout all this how can i do that and if u have some commands to do that ?
How often you backup the Tlog? If the transaction log is not important you can set database in SIMPLE RECOVERY MODEL which truncates the log at checkpoint. Before that you use DBCC SHRINKFILE to reduce the Tlog size. But maintain database backups regularly. Refer to BOL for more information. Satya SKJ
trans log files are important and i dont want to keep my database to simple mode as i have to keep my logs too
As satya has asked, how often do you backup the transaction logs? On very busy servers, I generally do this every 15 minutes, others I do hourly. Every time you backup the transaction log, it is truncated, preventing it from getting to big. When you say the transaction log is big, do you mean the total size of the file, or the contents of the file? Is it possible that the physical size is 6GB, but the actual size is much smaller? If so, you can shrink the size of the transaction log to a more realistic size. Also, if backing up the transaction log doesn’t reduce the size of the contents of the file, this might indicate that you have an open transaction that is preventing the log from being truncated.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Then you should implement DBCC SHRINKFILE to a sustainable size for Tlog file and maintain Tlog backups as referred by Brad. And also clarify the couple of queries referred above. Satya SKJ
]]>