Post reply

Automate shrink log file process

  • June 18, 2012 at 11:33 am

    #258191

    I am trying to automate the process of shrinking the log files. But I got stuck with the following problem--

    suppose i have two databases - one and two with log file one_log and two_log.

    to shrink the log file-use one;dbcc shrinkfile(one_log,truncateonly)

    so how do i automatically pick up the db name and log file name. I tried to use the variables but it's not working.

    Thanks,

  • June 18, 2012 at 11:35 am

    #1502355

    Why are you trying to automate shrinking your log files? They are just going to have to grow again depending on database activity which will impact system performance.

  • June 18, 2012 at 11:42 am

    #1502362

    We had an issue with one of the log files grow out of proportion. We haven't received any alerts. I am trying to automate the process only for the databases in simple recovery mode. Trying to run this job every one month, so that we don't have to worry about it.

  • June 18, 2012 at 11:43 am

    #1502363

    Scheduled shrinking of logs is a terrible practice. Why do you want to do this to your databases?

    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

  • June 18, 2012 at 11:51 am

    #1502367

    deep_kkumar (6/18/2012)

    We had an issue with one of the log files grow out of proportion. We haven't received any alerts. I am trying to automate the process only for the databases in simple recovery mode. Trying to run this job every one month, so that we don't have to worry about it.

    You are treating the symtom not the cause. Seems to me you should first figure out why the log file grew so much and work from there. If it is due to a monthly process, what is the process doing, can it be modified or does it make sense to shrink the file after the process completes.

  • June 18, 2012 at 11:54 am

    #1502369

    actualy issue is---

    http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/34ab68ad-706d-43c4-8def-38c09e3bfc3b/

  • June 18, 2012 at 11:58 am

    #1502372

    deep_kkumar (6/18/2012)

    actualy issue is---

    http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/34ab68ad-706d-43c4-8def-38c09e3bfc3b/%5B/quote%5D

    You know, you surround the URL with the [ url ] [ /url ] IFCode short cuts (no spaces inside of the brackets), people don't have to cut and paste to go where you would like them to go.

    http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/34ab68ad-706d-43c4-8def-38c09e3bfc3b/

  • June 18, 2012 at 12:01 pm

    #1502373

    deep_kkumar (6/18/2012)

    actualy issue is---

    http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/34ab68ad-706d-43c4-8def-38c09e3bfc3b/%5B/quote%5D

    Does this mean you are having the same problem?

  • June 18, 2012 at 12:02 pm

    #1502374

    deep_kkumar (6/18/2012)

    We had an issue with one of the log files grow out of proportion.

    In that case, please read through this: http://qa.sqlservercentral.com/articles/Transaction+Log/72488/

    By repeatedly shrinking, you're addressing symptoms, not causes and possibly causing other problems (slower backups and restores, slower recovery, slower replication, etc)

    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

  • June 18, 2012 at 12:08 pm

    #1502378

    YES.

  • June 18, 2012 at 12:11 pm

    #1502379

    Well, the obvious possibility that comes to mind is that the log reader agent is falling badly behind because of internal log fragmentation, which will be worsened by shrinking the log. Basically shrinking the log will likely make the problem worse and worse.

    Google Kimberly Tripp Transaction log throughput and examine and fix your log fragmentation.

    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

  • June 18, 2012 at 12:12 pm

    #1502380

    GilaMonster (6/18/2012)

    deep_kkumar (6/18/2012)

    We had an issue with one of the log files grow out of proportion.

    In that case, please read through this: http://qa.sqlservercentral.com/articles/Transaction+Log/72488/

    By repeatedly shrinking, you're addressing symptoms, not causes and possibly causing other problems (slower backups and restores, slower recovery, slower replication, etc)

    It's a development server. We don't take backups on it. No replication. If this is the issue with PROD i will never setup a job something like this.

  • June 18, 2012 at 1:33 pm

    #1502417

    deep_kkumar (6/18/2012)

    No replication.

    No replication? But the link you posted was about the replication log wait. If the database has no replication then see the replication section of this http://qa.sqlservercentral.com/articles/Transaction+Log/72488/

    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 13 posts - 1 through 12 (of 12 total)

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