Full backup - Transaction log too big RRS feed

  • Question

  • I am setup for full backup.  I have an automaed process that runs every night. 

    The transaction log continues to grow in my database.  I am not doing log backups.  I understand that my recover point is the time of the last full backup.

    I don't understand why the transaction log doesn't reset after the  backup.  I see that I can do a backup log with truncate and then srink, but I don't understand what effect that has.   I see that it reduces the size of the log, but am I loosing anything?

    If I do the truncate and srink, am I loosing information, or does my database contain all the data?

    I've read two Wrox books on sql server administration, and this isn't clear.  Is there a good reference?

    I may change my strategy and do periodic log backups during the day.  I need better understanding first.
    Friday, December 19, 2008 12:19 PM

All replies

  • Hi Do you have the Recovery Option setup for Simple instead of full.   You can find this by right clicking on your database, choose options and You will see Recovery Model - if it is set to full the transaction log will not reset after a full backup.  You can read about this in the BOL. 
    Friday, December 19, 2008 2:35 PM
  • No, I have recovery set to full.  I understand that Simple will truncate the log.

    In full recover mode, I do not understand when the log is truncated. 

    1.    I do a full backup every day.
    2.    Lets say I do a transaction backup at 10AM, and again at 2PM.  That gives me three recovery points each day. 

    When does the log file get "srunk"?  Do I have to issue special commands to do this?  I can't find an answer to this. 

    I currently have an automated procedure to do daily backups.  I want to add one to do the transaction log backups at 10 and 2.  Is it those transaction backups that truncate the log file? 

    Also, does "overrite existing media" have anthing to do with this?

    Friday, December 19, 2008 4:02 PM
  • You've really answered your own question with respect to your log size -  "I am not doing log backups". Full backups are backing up all the data that has been committed to the database so you can safely get back all your data to the last full backup. The log is not being backed up so it has a lot of transactions that have already been committed and are doing nothing more than taking up your log space. If you need a point in time recovery, setup log backups to run at whatever interval is appropriate for your business (mine run every 30 minutes). If you don't need point in time recovery, set the database to simple. On checkpoint, the transactions will be automatically purged from the log. I use various strategies, depending on my business need. For example, for one database I do a full backup on Sunday night, log backups every 30 minutes during business hours and a differential each evening (other than Sunday). Leave me with no more than 30 minutes of potential data loss (which is acceptable to my users). Some databases I simply run a full backup each evening. Data loss is not critical on these and my recovery mode is set to simple. It really depends on your business. 

    I would recommend you either backup the log or set it to simple mode. DO NOT truncate and shrink unless you need disk space or you had a one-off job that spiked out the size of the log. If you do truncate the log, do an immediate full backup as you've broken the restore chain. Use Google to search for recommendations on "SQL Server Backup Strategies" (a couple of million hits) . There are many good SQL forums that could also give you clearer picture of what may fit your needs. The good news is you're at least doing a full backup. Too many times I've seen "How do I get my data back. I do not have a backup?" - short answer, you don't.  Assess your business needs and develop a plan that works for you (and test that strategy as a D/R when you put it in place). Good luck.
    Regards, Terry
    Friday, December 19, 2008 8:25 PM
  • >>I see that I can do a backup log with truncate...

    Do not try that. This just trashes all the contents of the log and you are in big trouble until you get your next full back up. Please read this article from Paul Randal the guru. (

    >>Is there a good reference?

    Sankar Reddy |
    Saturday, December 20, 2008 7:48 AM
  • Somtimes you have to truncate it though - so you can DO that full backup when disk space runs low.

    This will show you how to truncate the logs real quick:
    Saturday, June 20, 2009 2:33 PM