Shrinking of Data logs RRS feed

  • Question

  • Hi All,

      We have SQL server 2012 AAG cluster setup in our primary location with two node. Now we can plannig it to extend it to DR site. Here we have one problem,  The Original DB DATA size is 15 GB and  Data log size is 200GB.  Can we shrink the DATALOGS in AAG mode,  If we shrink them would it effect primary location cluster setup.

    Please suggest me the best possible solution as moving 200gb data logs are taking lot of time.. Also if possible could you please suggest the steps to be taken



    • Edited by Raje14 Friday, October 18, 2013 4:45 AM
    Friday, October 18, 2013 4:43 AM


  • Hello,

    Yes you can shrink it .But first run below command to see what is holding your trn logs

    select name, log_reuse_wait_desc from sys.databases


    dbcc loginfo(db_name)

    -see status col

    I am sure log_resuse_desc col will be like

    1. Log backup--if so take transaction log backup and try shrinking the logs

    2. Active transaction --Let the transaction complete then take trn log backup and then shrink

    3.AAG  Groups secondary replica is applying transaction log records of this database to a corresponding secondary database...(this can also occur)

    Taking trn log backup and shrinking is good option

    If status col=2 in dbcc loginfo there is active VLF which will be truncated by Log backup

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, October 18, 2013 5:35 AM