SQL server 2008 R2 frequent transaction log full and my database is a mirrored one.

  • anjang2k

    Old Hand

    Points: 393

    I have 1 pair of identical server with SQL server 2008 R2 is installed.I have 2 databases in it which are mirrored.
    Frequently I am facing disk full issue as the transaction logs gets full frequently. (with in 5 days it becomes 260 GB for 1 database).
    The following activity we do when the disk space gets full.
    1) Break the mirroring.
    2) Shrink the transaction log.
    3) Take full backup of the database and its transaction log.
    4) Restore the same in standby machine with NO RECOVERY. (first full and then transaction log).
    5) Then start the mirroring in the  primary machine that was originally before mirroring.
    This request a manual intervention. Can we automate the same through some maintenance plan or some scripts.
    It will be nice if some body guide me in this regard.

  • Emil B

    SSCertifiable

    Points: 5568

    what is your backup strategy? how often do you take transaction log backups?

  • anjang2k

    Old Hand

    Points: 393

    Emil B - Thursday, November 30, 2017 6:21 AM

    what is your backup strategy? how often do you take transaction log backups?

    At present we normally do the above mentioned steps once in 5 days.We backup transaction log only when we see that the disk is going to get full.
    We can take backup in every 5 days.And keep the backup in separate location.

  • Emil B

    SSCertifiable

    Points: 5568

    anjang2k - Thursday, November 30, 2017 11:08 PM

    Emil B - Thursday, November 30, 2017 6:21 AM

    what is your backup strategy? how often do you take transaction log backups?

    At present we normally do the above mentioned steps once in 5 days.We backup transaction log only when we see that the disk is going to get full.
    We can take backup in every 5 days.And keep the backup in separate location.

    Your log will be growing until you take regular log backups, like let's say every 1 hour for start - this will allow you later to do a point in time restores. You have to remember that mirroring does not protect you from all disaster scenarios (like delete data). Your database is already in FULL recovery model so might as well use it to it's full potential in terms of protecting your data.

    This Article should help you understand how transaction log works an why it's growing:
    http://www.sqlservercentral.com/articles/Administration/64582/

  • anjang2k

    Old Hand

    Points: 393

    Emil B - Friday, December 1, 2017 1:05 AM

    anjang2k - Thursday, November 30, 2017 11:08 PM

    Emil B - Thursday, November 30, 2017 6:21 AM

    what is your backup strategy? how often do you take transaction log backups?

    At present we normally do the above mentioned steps once in 5 days.We backup transaction log only when we see that the disk is going to get full.
    We can take backup in every 5 days.And keep the backup in separate location.

    Your log will be growing until you take regular log backups, like let's say every 1 hour for start - this will allow you later to do a point in time restores. You have to remember that mirroring does not protect you from all disaster scenarios (like delete data). Your database is already in FULL recovery model so might as well use it to it's full potential in terms of protecting your data.

    But how to take the backup of principal and mirror server by some maintenance schedule?

  • anthony.green

    SSC Guru

    Points: 112212

    You need to setup a maintenance schedule.

    Lots of ways to do it, write your own, use maintenance plans, Ola's maintenance solution, Minion Ware, Red-Gate tools, Quest tools, Idera tools, ApexSQL tools.

    Few links below for you to read up on transaction log management, backup & restore, maintenance plans, and my favorite Accidental DBA book helped me out so many times.

    http://www.sqlservercentral.com/articles/books/94938/
    http://www.sqlservercentral.com/articles/books/89519/
    http://www.sqlservercentral.com/articles/books/68380/

    https://ola.hallengren.com/
    http://www.minionware.net/
    http://www.sqlservercentral.com/articles/books/76296/

    If it was me I'd go with Ola, but his scripts can be a little daunting, so read the maintenance plan book and go with that for a first pass.



    How to post data/code for the best help - Jeff Moden[/url]
    Need a string splitter, try this - Jeff Moden[/url]
    How to post performance problems - Gail Shaw[/url]
    Managing Transaction Logs - Gail Shaw[/url]
    Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger[/url]

  • Emil B

    SSCertifiable

    Points: 5568

    you should backup principal, you can use either Olas scripts, maintenance plans or write your own.

    Set up a backup solution of your choice on both servers and make sure it will only perform a log backup when the mirroring role is 'PRINCIPAL'
    I usually use something like this in my sql agent jobs:

    DECLARE @Mirror TINYINT;

    SELECT @Mirror = mirroring_role
    FROM sys.database_mirroring
    WHERE DB_NAME(database_id) = N'DBName'

    IF (@Mirror = 1 OR @Mirror IS NULL)
    BEGIN
      PRINT 'Your Code';
    END;

  • Arsh

    SSCertifiable

    Points: 6031

    anjang2k - Thursday, November 30, 2017 11:08 PM

    At present we normally do the above mentioned steps once in 5 days.We backup transaction log only when we see that the disk is going to get full.
    We can take backup in every 5 days.And keep the backup in separate location.

    Not taking a transactional log backup for a database in Full Recovery model will make the log grow until the disk becomes full. You MUST have log backup schedule for the same at frequent intervals. Maintenance plans or agent jobs are good enough to do it. I didn't see any problem setting them up for a mirrored database. Just curious.

  • shekhar.mca

    SSC Enthusiast

    Points: 179

    Schedule(On principal server) a transaction log backup for the database in every 30 min interval and keep a tab on storage (Backup location).
    Ensure backup retention plan is in place to avoid high storage.

Viewing 9 posts - 1 through 9 (of 9 total)

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