Log file unexpected growth in SQL SERVER with Database Mirroring

While implementing Database mirroring a major problem with the database sizes are to handle log file size of a database.

Reasons of Log Size Increases

1- As it is necessary to set recovery modal of a database as "FULL" all the transactions and DML operations are stored into log file before data file write. With the passage of time it will increase Size of your log file. Log file size can increase for multiple reasons we has discussed one lets discuss more reasons

2- While implementing database mirroring when fail over occurs or mirror database is unavailable due to network connections or server downtime. Log file holds all pending transactions until mirror will be available again and data will commit to the mirror. Due to this pending process Log size increase in a huge size in very low time.

3- If drive space will low to hold database files on mirror server then principal server database is unable to send and commit data on mirror server. In return it hold up all the structural and DML changes until mirror is available.

Shrinking Log File

When any of the above reason occurs you need to apply different solutions to overcome this problem. Lets discuss how can we avoid from such conditions specially when database mirroring is in place

• 1- Plan a full backup of your database for backup purpose as per your plan.
• 2 - Plan  transactional log backup of your database after some time within Full backup.

In this way your Log file size will be in control because every time transactional backup took place it will remove all the committed transactions from the log file and write to backup media. this is how it decreases the Log file size in the transaction. for more detail about backups have a look at this link

http://shamas-saeed.blogspot.com/2010/05/comprehensive-note-on-backup-policies.html

• Implementing Shrink Log Job
• Use DBCC Shrink DB Query

In above conditions you need to monitor very carefully that Jobs implementation is not on regular bases (this will increase fragmentation and effect performance) and ran successfully and you have mentioned that release space must return to the OS. You can create Shrink Log Job through Management Studio from this path... but this will shrink your database when job successfully executed otherwise log size will grows.

DBSERVER--> Management-- > Maintainess Plan -- > Plan Wizard -- > shrink DB

In Second way you need to manually notice when log file size requires shrinkage and you will run DBCC command as follows


dbcc shrinkdatabase('DBName',10)
dbcc shrinkfile('DBName_log',10)


ShrinkDatabase will release unused space and decrease to 10MB free space if more is occupied
ShrinkFile will decrease to 10 MB by reverted all uncommitted transactions.

Note: Your database status must be "Simple" not "Full". If you want to do this in your mirroring solution then you need to disconnect mirroring and change database status to Simple.

You can shrink your database using Management Studio as well you can shrink database file. look at the following screens.