Forum Discussion

MrGeen_blog's avatar
MrGeen_blog
Copper Contributor
Mar 21, 2024

How to shrink SQL log file in full recovery mode as my server is in primary mirroring

I have a primary database and mirror databases, both of which are in full recovery mode. The log file size is significantly large, and I am unable to shrink the file using the command DBCC SHRINKFILE (N'DB2_log' , 0, TRUNCATEONLY).

Please support.

  • Javier_Villegas's avatar
    Javier_Villegas
    MVP
    Mar 21, 2024

    Hi MrGeen_blog 

     

    First of all you have to make sure that transaction log usage is super low. there are multiple ways to check that but you can start by checking   DBCC SQLPERF(LOGSPACE) return.

     

    you should also check LOG_REUSE_WAIT_DESC in sys.databases to see if you have to do a transaction log backup or if the log is catching up with the other replicas

    Finally , do not use 0,TRUNCATEONLY, I'll suggest you to first shrink it by 10% so if your current Log total file size is 100 GB , try 90GB and see if you can accomplish it

     

    Also make sure you really need to shrink it . Maybe having a big log file is not a bad thing depending on your workload

     

    Regards

    Javier

    • MrGeen_blog's avatar
      MrGeen_blog
      Copper Contributor
      Mar 22, 2024
      Thank you for reply.
      As per your suggestions I remove TRUNCATEONLY in DBCC SHRINKFILE.
      I simply did following step:
      1. backup log DB3 TO disk ='Nul'
      2. DBCC SHRINKFILE(Db3_log,4096)
      It is reduce the size to 4 GB from 300GB. It's work .
      Is this right procedure ?

      • SivertSolem's avatar
        SivertSolem
        Iron Contributor
        Apr 02, 2024
        Since you started with a "backup to NUL", I have to ask. Do you do log backups of the primary database?

        I don't have any experience with the mirroring function directly, but it sounds similar in function to Always On Availability Groups, but for single databases.

        You are required to run in full recovery, as the transaction log is what's being synchronized between instances.
        If the replica for some reason is out of sync, the primary database's transaction log file cannot be truncated (nor shrunk).

        Regardless, the transaction log will continue to fill until a log backup is performed.
        If you don't do regular log backups, then your log file will grow to an unreasonable size again.
        A full backup does not remove the content of the transaction log file (truncate).

Resources