none
How to truncate/shrink Transaction Log if mirroring is enabled. RRS feed

  • Question

  • Hi Gurus,

    Please help.

    We have a SQL server 2012 database with size 200mb and Transaction Log has gone up to 34GB.

    We have SQL server Mirror is enabled and principal database is running with Full Recovery Model.

    How can I truncate/Shrink Log files? Will it impact on existing mirroring setup? I am very much new to SQL server.

    Thanks in advance.

    Regard, PPQatar.

    Tuesday, August 18, 2015 1:04 PM

Answers

  • Yes once you take a log backup, it marks the VLF's inside Log file as inactive. Then You can shrink

    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by PPQatar Friday, August 21, 2015 12:06 AM
    Tuesday, August 18, 2015 3:32 PM
  • If the last VLF is showing as 2(Active) it means active transaction and you can't shrink. Transaction log file is a sequential file. You should have atleast one file (At bottom)as inactive to shrink.

    If you want to restrict growth of your T-Log file, then change the autogrowth setting for that file. You can restrict the log file growth to some MB's. Please note that it is not recommended to change that to % option. Also make sure to have enough backup schedules are in place with more frequency based on criticality and transaction volume

    http://blogs.msdn.com/b/batuhanyildiz/archive/2013/03/02/autogrowth-option-for-sql-server-database-files.aspx


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue


    • Edited by Jinu Varghese Wednesday, August 19, 2015 11:40 AM
    • Marked as answer by PPQatar Friday, August 21, 2015 12:02 AM
    Wednesday, August 19, 2015 11:38 AM

All replies

  • First of all Truncate and Shrink are 2 different operations. Truncate marks the VLF's inside transaction log as Inactive and Shrink checks for these inactive VLF's and releases these space to OS.

    So what about t-log backups ? Are they happening successfully ?

    The command to Shrink log file is

    USE DBNAME
    GO
    DBCC SHRINKFILE(DBNAME_log, 1)


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Tuesday, August 18, 2015 1:12 PM
  • you can just do a regular shrink and it won't affect mirroring - although it's not generally recommended.

    Thanks, Andrew
    My blog...

    Tuesday, August 18, 2015 1:12 PM
  • To give another insight about shrinking based on Mirroring,..

    Database mirroring requires that each log record remain active until the principal server instance receives notification from the mirror server instance that the record has been written to disk on the mirror server. If the mirror server instance falls behind the principal server instance, the amount of active log space grows accordingly. In this case, you may need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database (using WITH NORECOVERY), and restart mirroring.

    While shrinking if you see that shrinking is not happening, use below query to see why

    select Log_reuse_wait_desc,name,* from sys.databases

    based on this you need to take necessary action and shrink again


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Tuesday, August 18, 2015 1:20 PM
  • Please do  not shrink data files it causes massive logical fragmentation.

    For shrinking log file

    1. Please take transaction log backup

    2. Use DBCC SHRINKFILE command to shrink log files follow below link

    http://msdn.microsoft.com/en-us//library/ms189493.aspx

    use Test
    go
    DBCC SHRINKFILE (test_Log, 1);
    GO
    

    3. If not enough space is released take trn log backup again

    4. Follow step 2.

    To avoid this issue in future please schedule frequent trn log backup for database.


    Vote As Helpful if it helps to solve your issue

    Tuesday, August 18, 2015 1:20 PM
  • Hi Jinu,

    I think this belongs to the mirror modell you are using. If you use synchronous mirroring, your description ist right.

    But if you use asynchronous mirroring, the principal didn't wait that the mirror commit the transaction.

    https://msdn.microsoft.com/en-us/library/dd207006.aspx?f=255&MSPPError=-2147217396

    And now, back to the issue.

    Kind regards,

    Andreas


    Tuesday, August 18, 2015 2:34 PM
  • I am sure you have not scheduled proper log backup for Mirror database. I would suggest you to first take log backup, you might need to take it multiple times to actually shrink it.

    As already mentioned log backup truncates the log and shrinking shrinks the log they are actually a different operation. Below query would return log back or does it return anything else ?

    select Log_reuse_wait_desc from sys.databases where name='db_name'
    So you need to take frequent log backups in mirroring to truncate logs. Please note taking transaction log backup on mirror database is recommended and should be done frequently. You can shrink once but dont make it a habit

    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 Article

    MVP

    Tuesday, August 18, 2015 2:47 PM
  • Thanks All.

    Please find status as below :

    Log_reuse_wait_desc output is "LOG_BACKUP".

    If am not wrong, should have to take T-Log backup at principal database first, right?

    Regards,

    PPQatar

    Tuesday, August 18, 2015 3:24 PM
  • Yes once you take a log backup, it marks the VLF's inside Log file as inactive. Then You can shrink

    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by PPQatar Friday, August 21, 2015 12:06 AM
    Tuesday, August 18, 2015 3:32 PM
  • Thanks all for quick reply.

    Executed T-Log backup and tried to shrink log file but, not reclaimed much (some MB only reclaimed).

    Can I execute below steps to shrink it properly? Do I need to pause mirroring before this activity ?

    USE AdventureWorks2012; GO

    -- Truncate the log by changing the database recovery model to SIMPLE.

    ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE; GO

    -- Shrink the truncated log file to 1 MB.

    DBCC SHRINKFILE (AdventureWorks2012_Log, 1); GO

    -- Reset the database recovery model.

    ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO

    Regards,

    PPQatar.

    Tuesday, August 18, 2015 5:35 PM
  • Thanks all for quick reply.

    Executed T-Log backup and tried to shrink log file but, not reclaimed much (some MB only reclaimed).

    Can I execute below steps to shrink it properly? Do I need to pause mirroring before this activity ?

    USE AdventureWorks2012; GO

    -- Truncate the log by changing the database recovery model to SIMPLE.

    ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE; GO

    -- Shrink the truncated log file to 1 MB.

    DBCC SHRINKFILE (AdventureWorks2012_Log, 1); GO

    -- Reset the database recovery model.

    ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO

    Regards,

    PPQatar.


    No dont do this, this will break mirroring take backup multiple times and try shrinking. Just one transaction log backup might not work

    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 Article

    MVP

    Tuesday, August 18, 2015 5:50 PM
  • I agree with Shanky.  "DONT DO RECOVERY MODEL SWITCHING" You could try taking log backups multiple times to get it shrinked.

    If you find not shrinking run the statement again to see why. Also use

    DBCC loginfo() to see if the last VLF status is 2. If its 2 its active and you can't shrink unless you have a status of 0 towards end of file.


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Tuesday, August 18, 2015 6:26 PM
  • Thanks All. It really helped us.

    Status column of "DBCC loginfo()" showing 2 for last row, also showing 2 for some initial 100-150 rows(out of 650 records).

    Does it means some active transaction still there? How can I stop growth of my t-log file?

    Regards,

    PPQatar

    • Marked as answer by PPQatar Friday, August 21, 2015 12:04 AM
    • Unmarked as answer by PPQatar Friday, August 21, 2015 12:06 AM
    Wednesday, August 19, 2015 8:25 AM
  • https://technet.microsoft.com/en-us/library/ms345583%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396


    Thanks, Andrew
    My blog...

    Wednesday, August 19, 2015 8:28 AM
  • If the last VLF is showing as 2(Active) it means active transaction and you can't shrink. Transaction log file is a sequential file. You should have atleast one file (At bottom)as inactive to shrink.

    If you want to restrict growth of your T-Log file, then change the autogrowth setting for that file. You can restrict the log file growth to some MB's. Please note that it is not recommended to change that to % option. Also make sure to have enough backup schedules are in place with more frequency based on criticality and transaction volume

    http://blogs.msdn.com/b/batuhanyildiz/archive/2013/03/02/autogrowth-option-for-sql-server-database-files.aspx


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue


    • Edited by Jinu Varghese Wednesday, August 19, 2015 11:40 AM
    • Marked as answer by PPQatar Friday, August 21, 2015 12:02 AM
    Wednesday, August 19, 2015 11:38 AM
  • Thanks to all.

    Issue got resolved...!

    New lesson learned in SQL server area.

    Regards,

    PPQatar.

    Friday, August 21, 2015 12:08 AM
  • It means your data base is in full or bulk log recovery model. You need to take the transaction log backup.to reuse that 34 Gb follow the steps

    Break the mirroring and do these on your primary server

    1) take a full backup of the current database

    2) change the recovery model to simple

    3) shrink the log file to 200 MB

    4) change the recovery model back to the previous value

    5) take a full backup.

    6) restore this full back up to your secondary server with no recovery

    7) reconfigure the mirror.

    you are good now.


    • Edited by Kumar muppa Friday, August 21, 2015 4:26 AM
    Friday, August 21, 2015 4:23 AM
  • Hi PPQatar,

    I hope you're no longer looking for an answer on this question (as it is 3 years old)

    But still, here is a simple plan that did the job for me.

    1) take a transaction log backup of your database

    2) fail over your database

    3) take a transaction log backup on you secondary server

    4) now you can shrink just fine

    and if it is still not working

    open the properties of your db, go to files and change the initial size of your log file(s) (you might need to add a second log file first)

    • Edited by MarijnV Friday, October 12, 2018 2:53 PM added extra info
    Friday, October 12, 2018 1:07 PM
  • Thanks All

    Log_reuse_wait_desc result shows "DATABASE_MIRRORING".. So what can i do now?

    Wednesday, January 1, 2020 7:02 AM