Post reply

Transaction Log file Deletion or movement?

  • January 2, 2013 at 7:20 am

    #266658

    Hello All,

    I have a Query Please help me out about this:

    Scenerio is, I have a database server which have a harddisk size is 70 GB c drives is occupied by OS. on which I have 2 databases DBA1 and DBA2 both database are live databases and configured in production environment.DBA1 and DBA2 are also configred with database mirroring without Automatic failover.

    Now on my Primary server in C drive[Size=30GB] datafiles are configured which is .mdf format and in D drive[Size=30GB] have Logfiles are storing DBA1 logfile size is 12 Gb and DBA2 logfile size is 13GB and these logfiles are growing everyday 500MB.

    Now how to manage these logfile?

    Thanks

  • January 2, 2013 at 8:12 am

    #1572901

    This would be a good start.

    http://www.sqlservercentral.com/articles/Administration/64582/

  • January 2, 2013 at 8:16 am

    #1572903

    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

  • January 2, 2013 at 9:12 am

    #1572933

    Hi,

    Thanks for quick response I have read the document.But as I mentioned this is a production database and my log is growing every day 500mb and It will be full in next 8 days.And it will create aproblem after 8 days I want something like automate that after 15 days automatic my log will move to some other NAS drive location is that possible to live databases?

    Thanks

  • January 2, 2013 at 11:29 am

    #1573008

    Read the article again.

    If your log is continually growing it's because you're mis-managing it. Most likely full recovery model with no log backups. Read the article, make sure you have appropriate log management going on. A properly managed transaction log does not continually grow.

    And no, you can't move it, you can't delete it. Not without potentially destroying your database.

    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

  • January 2, 2013 at 10:13 pm

    #1573162

    To simplify the fix for you

    What is the backup strategy you have implemented for these databases?

    You need to have log backups at regular intervals inorder to manage your transaction log growth.

    Please read the article referred above that makes you understand how transaction log files are managed

  • January 2, 2013 at 10:28 pm

    #1573163

    jitendra.te (1/2/2013)

    I have a Query Please help me out about this:

    Scenerio is, I have a database server which have a harddisk size is 70 GB c drives is occupied by OS. on which I have 2 databases DBA1 and DBA2 both database are live databases and configured in production environment.DBA1 and DBA2 are also configred with database mirroring without Automatic failover.

    Now on my Primary server in C drive[Size=30GB] datafiles are configured which is .mdf format and in D drive[Size=30GB] have Logfiles are storing DBA1 logfile size is 12 Gb and DBA2 logfile size is 13GB and these logfiles are growing everyday 500MB.

    i found this configuration a bit confusing , you are saying that you have single harddisk with size 70gb plus you are using the same in production ?

    additionally you have mirrong setup too , what i am thinking here is apart from youre space issue you might be getting IO isssues too sooner or later.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 7 posts - 1 through 6 (of 6 total)

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