SQL Server Performance Forum – Threads Archive
Backing Up Nearly Full Trans Log
I’m getting problems with my transaction log regularly filling up. It is 500MB in size and is backed up every 30 minutes. This is generally okay but at busy periods the log fills to its maximum size. I have an alert set up to catch this so that when it happens the log will be backed up. What I’d like to know, is it possible to catch when the log is say 90% full and force a backup up then? IainAny problem increasing the backup frequency to – say – 15 minutes?
No problem with increasing the frequency. This is what I have been doing. I was hoping there would be a nicer solution that will catch times of exceptionally high processing.
If the recovery mode is set to FULL, and you are doing lots of bulk insert operations, one option is to drop down to Bulk Logged mode. In the event your database needs to be restored, you would then apply the transactions logs, and would need to perform all your bulk inserts again. This might not be suitable for you, but if you use bulk insert a lot, it can decrease the log activity and hence backup time
Not doing bulk inserts just normal OLTP where certain times of the day have heavier traffic than others (this is not a regular thing like 12:30 every day though)
You can set up a performance condition alert and use the "Percent Log Used" counter to alert when the log size rises above 90% (or another chosen value). You can then set the alert to run a job that backs up the log when the alert is raised. Karl Grambow www.sqldbcontrol.com
Checkhttp://www.sql-server-performance.com/absolutenm/templates/?a=260&z=1 for any open transactions. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Can you elaborate on to set a Performance Alert with a specified %? All I can find under alerts (trying to create new) is checking for the error # and severity. Not seeing anything that is customizable. I was going to suggest simply running a job that contains the script code to pull the data from the systables and checking, but if there is a way to just set the Alert that would be a much better option. Much appreciated
Check books online for error 9002 and ALERTS topic to set alert using this refernce. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
When you create a new alert:<br /><br />1) Set the type to "SQL Server Performance Condition Alert"<br />2) Select SQL Server: Databases as the object in the drop-down list<br />3) Select Percent Log Used counter<br />4) Select the database to apply this alert to<br />5) Specify the desired threshold<br />6) In the Responses tab choose whether you want to run a job or how you want to handle.<br /><br />Hope that helps <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com
Much thanks to both of you. I was missing the fact that the type was a drop down and I could select the Performance Condition. This is beautiful. Thank you both. I found the following article published on your site very, helpful because it graphically showed the drop down: http://www.sql-server-performance.com/fg_sql_server_agent.asp there might be a better way to post the link to it, but I’m not familiar with how to do it. Satya feel free to edit this any way that needs done.
Druer
Your reference is correct and this helps others too. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Fantastic. That’s exactly what I wanted. Iain
]]>