Post reply

Transaction Log file size issue

  • February 6, 2012 at 2:33 am

    #256192

    Hi All,

    I am not a DBA but need some help regarding the database transactions logs and maintenance. I have PROD database running with data file (6.78 GB size ) and log file (79 GB). I understand that log file size if very large and has already created disk space issue in the past. But that time - we increased disk space rather than thinking about good solution.

    At this moment - we do full backups daily at midnight though point in time recovery is critical for us. I understand we need to differnet backup stratergy and I am thinking of weekly full back, daily differntial and hourly log backups. But this is not implemented yet and looking at the problems - we will have to implement it soon. Our database recovery model is FULL.

    But my problem at this moment is - log file has is very large and recently it created problem. Last friday - it automatically increased the size since autogrowth property is set to true. But while SQL server increasing log file size - we got serious issue with server slowness causing application down problem and then we had to check with server provider for server issues and all. Our application down for almost 3-4 hrs due to this and other issues it caused. Log file was increased automatically in the past but it didnt create problems at this time (may be due to other factors).

    As I said - we are going to implement to improved backup stratergy but it will take another couple of days or a week to test it on locally and we can not do it now in weekdays.

    Is there anyway to shrink the log file size immediately and still have point in time recovery when we implement the new plan without losing any logs.

    I have noticed that since last Friday I am seeing very high CPU usage contantly (around 80-95%). Could there be any relation of log size with this CPU usage ? I understand CPU usage can have multiple reasons like indexing but we have already proper indexes and it was not problem before.

    Please let me know your suggestions/solutions/comments.

    Thanks,

    Bhimraj

  • February 6, 2012 at 2:38 am

    #1442955

    Bhimraj

    So you're not doing any transaction log backups at all at the moment?

    John

  • February 6, 2012 at 2:44 am

    #1442958

    Hi John,

    No, we are not doing log backups at this moment. Only full backs daily.

    -Bhimraj

  • February 6, 2012 at 2:50 am

    #1442960

    Bhimraj

    Then you won't be able to release any space from your log file. You have two options:

    (1) Implement your new backup strategy as a matter of priority

    (2) Switch to Simple recovery mode in the meantime and lose the ability to do point-in-time recovery.

    John

  • February 6, 2012 at 2:52 am

    #1442961

    The root of the problem is that you are not taking log backups.

    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

  • February 6, 2012 at 3:12 am

    #1442967

    Hi

    I suggest you backup your log file and shrink it (although this is not recommended on SQL best practices but i believe its the best solution at the moment)

  • February 6, 2012 at 3:21 am

    #1442971

    Thanks John, Gila and Tee.M for your inputs. Seems like I have to implement new back up plan on priority. GilaMonster - your article is very interesting and informative. I am going through it.

    Tee.M - if I backup log file and shrink it - it should free some space right ? Ideally how much time it will take shrink 79 GB file ?

    If I try to do it immediately on live server - will it affect live users ( I guess - Yes but still want to confirm)

  • February 6, 2012 at 3:31 am

    #1442976

    rajg (2/6/2012)

    Tee.M - if I backup log file and shrink it - it should free some space right ? Ideally how much time it will take shrink 79 GB file ?

    Don't shrink it to 0. Shrink to a reasonable size (maybe 4GB). It should be near-instant, but may not release all the space. Do note, you'll need around 79GB for that log backup file. I would suggest instead perhaps switch to simple recovery (not now, wait until the users are offline), checkpoint, shrink log, full recovery, full backup and set up the log backup job at the same time.

    If I try to do it immediately on live server - will it affect live users ( I guess - Yes but still want to confirm)

    No. Shrink is an online operation. Only time you might have an impact is if you shrink too small and the log has to grow immediately.

    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

  • February 6, 2012 at 1:05 pm

    #1443396

    rajg (2/6/2012)

    But my problem at this moment is - log file has is very large and recently it created problem. Last friday - it automatically increased the size since autogrowth property is set to true. But while SQL server increasing log file size - we got serious issue with server slowness causing application down problem and then we had to check with server provider for server issues and all. Our application down for almost 3-4 hrs due to this and other issues it caused. Log file was increased automatically in the past but it didnt create problems at this time (may be due to other factors).

    what autogrow settings are currently set, value as percentage or in MBs?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 16, 2012 at 4:19 am

    #1447766

    Thank you all for taking time to help me on this. Finally, I took the backup of log file (Gila was correct on 79 GB 🙂 ) and then shrunk it.

    I have implemented new backup strategy in the last week for the Full (weekly) , Diff (daily) and log backups (hourly).

    Now with this strategy - I also need to take care disk space. So I am working on the backup file rotation policy where I am creating a scheduled task to keep only two weeks of backup files.

    In the last week - since it was first time log was backed up, its size is around 79 GB.

    As I understand I wont need this log file after one week (or in the future) - when I will have full backup on this weekend ? I mean even in case of some problem - I will be able to restore the database using the full backup and subsequent Diff, log backups.

    I would like to confirm if my understanding is correct so that I can free up the disk space utilized by 79GB log backup file.

    Looking for inputs 🙂 Thanks in advance.

  • February 16, 2012 at 4:28 am

    #1447770

    Yes, after you take your next full backup, if you delete the 79GB log backup, you will only be able to restore to a point in time after the full backup was taken. If that's acceptable, you're OK to delete the large backup file. Check that new backups are not appending to that file first, though.

    John

  • February 16, 2012 at 4:36 am

    #1447775

    Thanks John.

    Check that new backups are not appending to that file first, though.

    My new hourly log backups are only in MBs. This means its not appending, right ?

    In what case this can happen (i.e. new backups are appending to first file) ?

  • February 16, 2012 at 4:44 am

    #1447776

    This will happen if you use BACKUP LOG TO DISK = 'x:\MyFolder\MyBigLogBackupFile.TRN' and you don't specify WITH INIT. Use RESTORE HEADERONLY to check how many backups are in the file before you delete it. This applies whether your backups are in Bs, KBs, MBs, GBs or anything else.

    John

  • February 17, 2012 at 8:12 pm

    #1448856

    Only full backs daily

Viewing 14 posts - 1 through 13 (of 13 total)

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