backup log | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

backup log

hi Please guide me about the pros and cons of taking log backup of the database which has a transaction on a frequent basis. i am planning to take backup of log file after every 1 hr, dous this going to affect any performance of transaction. The database is in a simple mode.
any idea and guidence will be highly appreciated.
regards
shiv gupta

http://www.sql-server-performance.com/backup_restore_tuning.asp says If your transaction log backups are negatively affecting your users because they take too long to run, consider backing up the logs more often so they are smaller. The smaller they are, then the less impact there will be on the server when the backup occurs. It is not uncommon to perform transaction log backups as often as 5 to 15 minutes, depending on transaction activity and on how much data you are willing to loose, assuming the database becomes corrupt.
—————————————-
http://spaces.msn.com/members/dineshasanka

If your database is in simple model then you cannot backup the Transaction log backup and you must schedule frequent database backups to recover the database in the event of failure. As suggested by Dinesh, checkout where and when the backup is taking too long and consuming server resources. PERFMON will help you to capture the counters of server resources for further assessment. 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.
In other words you have to change the recovery model to be able to apply transaction log backups.
hi every body thanx for your advice. i got your points.

small question .. i have log backup each 3 hours, and log back size is just 300KB .But the problem comes during some weekends when some user runs something like report generation or something like that. My log grows and logbackup fails giving out of disk space error .Hence all subsequent log backup fails. Is ther any solution to this occations log growth. Also there is no way to add disk space, i have to manage with my 7GB fres space available now . –Rajiv
Just to add to the existing posts, one negative side-effect of running the log backup frequently (say every 5 minutes) is that if you do have to restore them you then have to potentially run lots of restores. Just something worth bearing in mind.<br /><br />Having said that, that’s not a good enough reason to not run regular log backups. Unless you don’t care about data loss <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by shivg</i><br /><br />hi<br /><br />Please guide me about the pros and cons of taking log backup of the database which has a transaction on a frequent basis.<br /><br />i am planning to take backup of log file after every 1 hr, dous this going to affect any performance of transaction.<br /><br />The database is in a simple mode.<br /><br /><br />any idea and guidence will be highly appreciated.<br /><br /><br />regards<br />shiv gupta<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com
small question .. i have log backup each 3 hours, and log back size is just 300KB .But the problem comes during some weekends when some user runs something like report generation or something like that. My log grows and logbackup fails giving out of disk space error .Hence all subsequent log backup fails. Is ther any solution to this occations log growth. Also there is no way to add disk space, i have to manage with my 7GB fres space available now . –Rajiv
7 Gigs seems like a lot of space to have available for the log backup. Is the problem that the job that runs ends up growing your transaction log space so that it ends up gobbling up most of the space so that you end up not having enough space for the backup to occur? However, the bigger question I have is given you are running in Simple Mode I’m not sure that you need to be doing the log backups at all. Hopefully, others with more experience will clarify and correct if I’m wrong. I’ve pasted the following from books on line which indicates that you can’t restore log backups for Simple Recovery Mode anyway, so I’m not sure what they would be used for or why they would be needed. Simple recovery model The Simple Recovery model typically requires less log space, but it incurs the greatest potential work loss if data or log files are damaged. Only events needed for basic recovery are logged. Using the Simple Recovery Model, only full database and differential database backups are available. In the event of a failure, all committed work since the last backup must be redone. This model is the simplest to administer, but it is not a good choice for a mission-critical application where loss of committed work cannot be tolerated.

Rajiv
Check the error generated by the statement, I don’t think it is related to the free space available by the backup. It must be the database size related, as the report queries are running TEMPDB might be using a lot and in order to cater the sizes you must resize and set proper values to the tempdb and frequent tlog backups. 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.
Satya,
I have my Temp DB in another drive .So tht is not an issue. SQL error log says –
Operating system error 112-There is not enough space on the disk. ie, K: drive This occasional growth of log file cause next log backup to fillup my backup drive(K:) and wil break the next chain of log backups.
–Rajiv
<<This occasional growth of log file cause next log backup to fillup my backup drive(K:) and wil break the next chain of log backups.>> If you are running in Simple mode, you have no chain of log backups.
Interesting that you say the logs are usually only 300KB, but over the weekend you are using up 7GB of disk space???? I once had a similar problem with extreme log use during report generation. After doing some digging I found the "report generator" was loading millions of rows into "temporary" tables (not #temp tables, temporary in the since they only held transient data) and then DELETED the rows after doing some number crunching. Changed the DELETE to TRUNACATE and performance/log usage improved.

quote:Originally posted by dtipton <<This occasional growth of log file cause next log backup to fillup my backup drive(K:) and wil break the next chain of log backups.>> If you are running in Simple mode, you have no chain of log backups.
Interesting that you say the logs are usually only 300KB, but over the weekend you are using up 7GB of disk space????
I cant set to Simple as its my prod database. Yes same as you said .usually 300KB but occasionaly backup is around 7GB size cause out of diskspace
]]>