Growing Log Files

  • I am intermediate in database admin. Please give me your solution for this issue.

    Size of my db is growing about 5M every 2 weeks. Now its size is 118G. my db is in full recovery mode, and I do not have log backup.

    Is this a good solution?

    1- Perform a full backup

    2- execute checkpoint

    3-Perform full Backup

    4- Perform transactional log backup

    5- shrink log file type

    6- schedule transactional log backup

    Also, can I do these processes during users are connected ?

  • mp5387 (4/4/2013)


    I am intermediate in database admin. Please give me your solution for this issue.

    Size of my db is growing about 5M every 2 weeks. Now its size is 118G. my db is in full recovery mode, and I do not have log backup.

    Is this a good solution?

    1- Perform a full backup

    2- execute checkpoint

    3-Perform full Backup

    4- Perform transactional log backup

    5- shrink log file type

    6- schedule transactional log backup

    Also, can I do these processes during users are connected ?

    Skip steps 2 and 3. Move step 6 to the new step 1.

    And if your log file is truly too big then do step 5.

    So the new order would be:

    1. Create tlog backup schedule

    2. Perform Full backup

    3. Shrink log file if too large.

    Doing this will allow the schedule to take care of the tlog backups.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • mp5387 (4/4/2013)


    I am intermediate in database admin. Please give me your solution for this issue.

    Size of my db is growing about 5M every 2 weeks. Now its size is 118G. my db is in full recovery mode, and I do not have log backup.

    Is this a good solution?

    1- Perform a full backup

    2- execute checkpoint

    3-Perform full Backup

    4- Perform transactional log backup

    5- shrink log file type

    6- schedule transactional log backup

    Also, can I do these processes during users are connected ?

    I would say, do full backup weekly, daily differential and hourly log backups based on the RTO provided.

    If your database is in full recovery model, then definitely, you need to do log backup to truncate the logs.

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • SQLCrazyCertified (4/4/2013)


    mp5387 (4/4/2013)


    I am intermediate in database admin. Please give me your solution for this issue.

    Size of my db is growing about 5M every 2 weeks. Now its size is 118G. my db is in full recovery mode, and I do not have log backup.

    Is this a good solution?

    1- Perform a full backup

    2- execute checkpoint

    3-Perform full Backup

    4- Perform transactional log backup

    5- shrink log file type

    6- schedule transactional log backup

    Also, can I do these processes during users are connected ?

    I would say, do full backup weekly, daily differential and hourly log backups based on the RTO provided.

    If your database is in full recovery model, then definitely, you need to do log backup to truncate the logs.

    SueTons.

    An RTO was not provided. And your suggestion has more to do with RPO and not RTO. Though neither has been specified so they are both unknowns.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Please read through this - Managing Transaction Logs[/url]

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • SQLRNNR (4/4/2013)


    SQLCrazyCertified (4/4/2013)


    mp5387 (4/4/2013)


    I am intermediate in database admin. Please give me your solution for this issue.

    Size of my db is growing about 5M every 2 weeks. Now its size is 118G. my db is in full recovery mode, and I do not have log backup.

    Is this a good solution?

    1- Perform a full backup

    2- execute checkpoint

    3-Perform full Backup

    4- Perform transactional log backup

    5- shrink log file type

    6- schedule transactional log backup

    Also, can I do these processes during users are connected ?

    I would say, do full backup weekly, daily differential and hourly log backups based on the RTO provided.

    If your database is in full recovery model, then definitely, you need to do log backup to truncate the logs.

    SueTons.

    An RTO was not provided. And your suggestion has more to do with RPO and not RTO. Though neither has been specified so they are both unknowns.

    Thanks for the correction, however, I thought RPO(Recovery Point Objective) and RTO(Recovery Time Objective) are basically same? Please let me know.

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • SQLCrazyCertified (4/5/2013)


    SQLRNNR (4/4/2013)


    SQLCrazyCertified (4/4/2013)


    mp5387 (4/4/2013)


    I am intermediate in database admin. Please give me your solution for this issue.

    Size of my db is growing about 5M every 2 weeks. Now its size is 118G. my db is in full recovery mode, and I do not have log backup.

    Is this a good solution?

    1- Perform a full backup

    2- execute checkpoint

    3-Perform full Backup

    4- Perform transactional log backup

    5- shrink log file type

    6- schedule transactional log backup

    Also, can I do these processes during users are connected ?

    I would say, do full backup weekly, daily differential and hourly log backups based on the RTO provided.

    If your database is in full recovery model, then definitely, you need to do log backup to truncate the logs.

    SueTons.

    An RTO was not provided. And your suggestion has more to do with RPO and not RTO. Though neither has been specified so they are both unknowns.

    Thanks for the correction, however, I thought RPO(Recovery Point Objective) and RTO(Recovery Time Objective) are basically same? Please let me know.

    SueTons.

    RPO, how close to the time of failure do you need to recover. Or, how much data are you willing to lose time wise. Can your people manually recover the last one hour of work for example.

    RTO, how long it will take to restore operation.

  • They can be radically different.

    For example, I worked with a card processing company a few years back, their RTO was minutes. Literally, every minute they were down they were losing money because they couldn't process transactions. Their RPO however was 48 hours. They could lose up to 2 days of data without concern because they got summaries from the banks every 24 hours containing the previous 2 days of data, which they could use to recreate any lost transactions in their systems.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks guys.

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • Hi,

    Thanks for your answer. I did this solution on my test server and it worked, but I am worry! Can I do that on my production server during the users are connected and they work with application?

    Also, we donot have lots of transaction daily.So, I want to schedule one full backup daily, and one transactional backup every one hour. Now for providing transactional backup which method is good? append or overwrite

    I appreciate your answer in advance!

  • mp5387 (4/8/2013)


    Now for providing transactional backup which method is good? append or overwrite

    Neither.

    Overwrite would be rather silly with log backups since you need all log backups in sequence to restore. Set it to overwrite and you'll only have the last one, useless for restoring.

    Append leaves all backups in the same file which means your file is growing larger and larger and anything goes wrong you lose the entire set of backups rather than one.

    All backups to their own file, timestamp in the file name.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for your prompt answer:-)

  • GilaMonster (4/8/2013)


    mp5387 (4/8/2013)


    Now for providing transactional backup which method is good? append or overwrite

    Neither.

    Overwrite would be rather silly with log backups since you need all log backups in sequence to restore. Set it to overwrite and you'll only have the last one, useless for restoring.

    Append leaves all backups in the same file which means your file is growing larger and larger and anything goes wrong you lose the entire set of backups rather than one.

    All backups to their own file, timestamp in the file name.

    Also, create a clean up job to remove the old trans log backup files after taking a new full backup everyday.

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • Please ignore.....sent in error.

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • SQLCrazyCertified (4/8/2013)


    GilaMonster (4/8/2013)


    mp5387 (4/8/2013)


    Now for providing transactional backup which method is good? append or overwrite

    Neither.

    Overwrite would be rather silly with log backups since you need all log backups in sequence to restore. Set it to overwrite and you'll only have the last one, useless for restoring.

    Append leaves all backups in the same file which means your file is growing larger and larger and anything goes wrong you lose the entire set of backups rather than one.

    All backups to their own file, timestamp in the file name.

    Also, create a clean up job to remove the old trans log backup files after taking a new full backup everyday.

    SueTons.

    I wouldn't recommend that. In fact, I'd say that's not a particularly good idea. What happens if that new full backup is damaged and won't restore? By deleting all the log backups from the previous day, you now have no way to recover.

    I keep a full week of full and log backups if I can. At minimum 2 days worth.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 16 total)

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