Post reply

How to Backup Large Transaction Log

  • January 28, 2005 at 12:45 pm

    #109169

    hey guys I am a total newbie to SQL Server administration and am not even a DBA but my boss is asking my assistance.

    The problem is we have a database that is very high volume and the transaction log file is currently at 39gig. This morning the server crashed because it ran out of disk space. I checked the database and it is set to Full Recovery mode (I have no idea what that means).

    My question is how can we backup this database and start from scratch, and also in the future how can we prevent this transaction log file from growing that fast.

    In need to help....

  • January 28, 2005 at 2:26 pm

    #539618

    If you care about being able to restore the database to a particular point in time you should schedule periodic transaction log backups.  Each time your transaction log backup completes, the log is truncated and returns to "zero".

    You can also perform a BACKUP LOG <dbname> WITH TRUNCATE_ONLY in order to truncate the transaction log without writting a backup file.

    If you do not care about being able to restore transactions on this database you should set your database to simple recovery.

  • January 28, 2005 at 2:29 pm

    #539620

    Thanks

  • February 1, 2005 at 1:31 pm

    #540020

    Full recovery mode means  you can do "full" backups and then add log backups and incremental backups.

    Full backup will backup your database and enough of the log file to recover the database to a consistent state.  You (if you have them) then apply log files to roll the database forward.

    The size and number of log files depends on how busy your system is and how much disk space you have.  It can also depend on your backup method (tape robot, disk, crw-dvd, ect).

    Depending on how high the volume of transactions are, logs can be taken every 10-15 minutes or as little as 2-3 times a day.

    I would start with an overview of the Online Books and look up "backup/restore strategies".

     

    If you have more specifics, a backup strategy to disk can be setup in a matter of minutes.

     

    Joseph Devereaux

  • February 1, 2005 at 1:43 pm

    #540024

    Thanks for your post

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply