HI All, I have a database on the mdf file is not increasing but the ldf file increasing rapidly. I have truncated the log file and make it to 1 MB in size but in just one day it baecame 50 GB in size, i take T log twice a day from the maintanece plan. I think from maintanence plan the log file do not truncate while taking backup. Do i need to take more frequently backup.. Thanks Basit Khan
You must have a great deal of transactions updating and or changing data consistently. You should do much more frequent backups. I would start with either 15 or 30 minutes. If you are already doing twice a day but it is growing to 50 GB in size, you need much more frequent backups. You should also read Kimberly's [blog](
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx) on virtual log files. You are probably having problems with tons of virtual log files and aren't even aware of it.
It's hard to tell if 50GB transaction log is "large" or "small". It depends on your size of disks, your speed of disks, size of the data file(s) etc. Use DBCC SQLPERF(logspace) to find out how much of the transaction log is actually used. If the transaction log is nearly full, you won't be able to shrink it either. (But you probably don't want to do that anyway) If what's happening is that you have a large transaction log, back it up, shrink it and then it grows back to 50GB again in one day, I'd say you should either back it up more frequently or simply let it be 50GB. Otherwise your queries will perform poorly because the transaction log needs to auto grow all the time. (Plus you'll get a lot of virtual files, like Tim said).