Friday, March 23, 2012

Log File size increase

I have set up an alert for Percent log used and i get this message every now and then:

The SQL Server performance counter 'Percent Log Used' (instance 'TelehopBilling') of object 'SQLServer:Databases' is now above the threshold of 90.00 (the current value is 95.00).

My database settings are:

Transaction log files space allocated

file1 1 mb

file 2 3 mb

file 3 1201 mb

automatically grow file : not checked

auto shrink - off

shrinking of database thru auto task: twice a week.

Please guide what is the solution to this. Also suggest me a suitable database settings. This is a production database and the actual log file size is around 2 GB

Thanks,

Why you are using multiple files for Transaction log?

As you might not gain anything using this way, I would suggest to use only one file and set appropriate size to transaction log. At the same time do not waste the SQL resources by shrinking the log regularly. You need to consider the set of processess including day to day, scheduled jobs such as database maintenance tasks, any bulk insert jobs and during these processes it will have impact on size.

Also ensure to maintain the backup log schedule frequently to take care of virtual log size boundaries that will stop unprecedented growth during any process.

As of now your setup seems ok other than having multiple log files, also I don't understand what is the problem you have with this setup.

No comments:

Post a Comment