Post reply

Looking a good Backup Plan - SQL Server 2005

  • July 22, 2009 at 1:18 pm

    #385033

    Hello,

    I am currently creating FULL backups on my databases, so I am creating the BAK files.

    I am also backing up the Transacation Log File, so I am creating TRN files.

    One day I noticed that the LDF files for some databases were very large, one was 160 GB and growing out of control. So, I placed a file size restriction on all the LDFs. Had to use DBCC SHRINKFILE ('db_name', 1000) on the largest file to get this size down (I avoid this as it increases fragmentation).

    Question: If I have file size restrictions on the Transacation Log File (LDF), and I do a backup creating the TRN, do I get all of the transactions? I am thinking, if the LDF does not hold everything because of the size, do I get everything during the backup?

  • July 22, 2009 at 4:53 pm

    #1028714

    Yes, you get everything in the transaction log. If you reach the max size that you have set - then your system is going to stop until there is space in the transaction log.

    If you are performing regular transaction log backups then the size of the transaction log should not grow. If it does grow, then something has changed which is generating more transactions - or you have an index rebuild operation or large import that has occured.

    Review the article I link to in my signature to understand more about how to manage your transaction logs.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • July 27, 2009 at 8:52 pm

    #1030820

    Paul, you'll want to increase the frequency of your log backups if you see growth that is abnormally large. Depending upon how many inserts, updates, and deletes you perform your frequency of log backups needs to increase. Running a shrink against an LDF will not cause fragmentation, as data and indexes are not stored in the .ldfs.

    - Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

Viewing 3 posts - 1 through 2 (of 2 total)

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