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

Copper Contributor

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.

3 Replies

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

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 ?

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).