Forum Discussion
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...
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 ?
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).
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).