locked
SQL Transactional Logs with Database Mirroring RRS feed

  • Question

  • Problem Background

    In my environment I have two sets of mirrored database 1 for prod and 1 for DR,
    I need to know what are recommended ways of managing Transactional Logs files when database mirroring is used,
    There is a specific database rtcxds TLog for which has initial size of 4 GB and is Limited to 100 GB,

    Not sure what all kind of transactions take place in this DB, but this seem to grow pretty rapidly,

    Queries:-
    1. How can I backup database and TLog using sql powershell commands as I am backing up databases that way, I need to
        know how do I set the filename and Location

    2. Now once this TLog is Backedup-Truncated and has also been shrinked, it is not using or showing 100 GB as the space
        allocated to it, instead it keeps at 4 GB

    More Info:-
    These sets are basically backends for SkypeForBusinessServer 2015,
    It is surprising that no MS Support engineer ever mentioned regarding this, As this creates lots of issues once the log is full,
    Skype Documentation only tells how to configure mirroring and share commands for the same,


    An Extremist

    Friday, February 17, 2017 5:14 AM

All replies


  • I need to know what are recommended ways of managing Transactional Logs files when database mirroring is used,
    There is a specific database rtcxds TLog for which has initial size of 4 GB and is Limited to 100 GB,

    Not sure what all kind of transactions take place in this DB, but this seem to grow pretty rapidly,

    Hi

    Managing transaction log in Mirroring is not complex all you have to do is make sure you do not run very heavy transaction which will generated lot of logs in short time, this will flood your network with log bytes and will make mirror server behind principal and also will force principal to wait till transactions commit on mirror (in Sync Mirroring). Make sure you take frequent log backups to keep the log size under check. The index maintenance operations should be performed wisely.

    Make sure autogrowth is NOT in percentage but in MB  start from 400 MB.


    1. How can I backup database and TLog using sql powershell commands as I am backing up databases that way, I need to 
        know how do I set the filename and Location

    2. Now once this TLog is Backedup-Truncated and has also been shrinked, it is not using or showing 100 GB as the space 
        allocated to it, instead it keeps at 4 GB

    1. I am not a powershell guy so sharing few links with you

    http://sqlmag.com/powershell/powershell-lets-you-back-sql-server-your-way

    https://blogs.technet.microsoft.com/sql_server_isv/2010/10/12/backing-up-all-sql-server-databases-using-powershell/

    2. Please don't shrink transaction logs that would effect the performance of database. The so called initial size is 4 G for you and normal shrink would not let you go beyond that size. You would have to run alter database command to change the initial size. But believe me you you should not do that leave it as it is. You must allow logs to grow instead of keeping restriction on logs manage your transactions which are going into the database.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Friday, February 17, 2017 5:41 AM
    1. Initial size is set to 4 GB and Limited to 100 GB T-Log for RTCXDS database
    2. However, whenever I run dbcc sqlperf (logpace)
    3. It shows me the usage percentage for 4 GB and not the limited size
    4. How or when is it going use the rest of 96gb of space allocated to it

    An Extremist

    Friday, February 17, 2017 8:39 AM

    1. How or when is it going use the rest of 96gb of space allocated to it

    An Extremist

    It will be used when required and this is good when requirement will come it would not have to go and ask for disk space which would take time. Now it already has free space so it will be utilized immediately

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, February 17, 2017 9:44 AM
  • I want to know what is the checkpoint on this,
    I mean is it some percentage level it will wait till or what,
    As I am seeing it on the server it is showing me that the Log is about 80% full and the sizing showing 4 GB why ? where and when the rest of 96GB will be used and set to autogrow with 512 MB till 100 GB


    An Extremist

    Friday, February 17, 2017 9:48 AM

  • As I am seeing it on the server it is showing me that the Log is about 80% full and the sizing showing 4 GB why ? where and when the rest of 96GB will be used and set to autogrow with 512 MB till 100 GB

    I believe you ran dbcc sqlperf(logspace). The 80% is the percentage of space allocated to the tempdb. So suppose you have allocated 50 G to tempdb log file 80% here means 40G is being utilized and 10G is free. I am really having hard time understanding your question, I hope I understood it correctly

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, February 17, 2017 11:26 AM

  • An Extremist

    Friday, February 17, 2017 11:38 AM

  • An Extremist

    Friday, February 17, 2017 11:40 AM
  • Please check ur selves and advice the output is for both tempdb and rtcxds

    An Extremist

    Friday, February 17, 2017 11:40 AM
  • In both case tempdb size is few MB and out of the size in MB the % says what is used. So like so 2nd picture tempdb size is 2.74MB and is 61% used which means 1.67 MB is used.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, February 17, 2017 3:27 PM
  • I get that Shanky, however what I need to know is when is this T-Log is going to show 100 GB as the space used, what is that event which will cause it show space used for the log is more than 46 GB.....

    I mean is it some percentage ?

    or what else ?


    An Extremist


    • Edited by AnExtremist Monday, February 20, 2017 5:37 AM
    Monday, February 20, 2017 5:37 AM
  • I get that Shanky, however what I need to know is when is this T-Log is going to show 100 GB as the space used, what is that event which will cause it show space used for the log is more than 46 GB.....

    I mean is it some percentage ?

    or what else ?


    An Extremist


    I cannot understand your question clearly their can be lot of events which can cause log file to grow like index rebuild for all databases, bulk DML operation on large data set etc.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, February 20, 2017 8:21 AM