Database Mirroring and Log File Size

Posted on 2009-05-10
Medium Priority
Last Modified: 2012-05-06
Hi All,

I have setup database mirroring and noticed after few days that the size of Transaction Log is around 180 GB. The max size is set to around 203 GB. this way I am soon gonna run out disk space.

Can somebody please help me on how to shrink the transaction log where database mirroring is setup. I have another database on the same server with database mirroring and the log file for that server is fine no probs.

Cannot understand why with this database the log file is increasing like anything ?

Thanks in advance.

Question by:matrix_aash
  • 2
  • 2
LVL 75

Accepted Solution

Anthony Perkins earned 1000 total points
ID: 24350038
The database is set to Full Recovery Model and you have failed to backup the transaction logs.  So either change it to Simple or start backing up the transaction log.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24350041
Once you have done that we can talk about shrinking it down to a more manageble size.

Expert Comment

ID: 24351286
In SQL Server 2005, a shrink operation (DBCC SHRINKFILE) tries to shrink the specified transaction log file to the requested size immediately. To shrink the transaction log file manually under the full recovery model, first back up the transaction log file. Then, use the DBCC SHRINKFILE statement to shrink the transaction log file. To backup the transaction log for your database below is the syntax:

BACKUP LOG <databasename> TO DISK='Backup File'

e.g., BACKUP LOG TestDB TO DISK='C:\TestDB1.bak'

Then, shrink                         the transaction log file. To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement:

e.g., DBCC SHRINKFILE (AdventureWorks_Log, 1) WITH NO_INFOMSGS

Providethe filename of the log file and a target size in MB

If the DBCC SHRINKFILE statement does not shrink the transaction log file to the                         target size, run the BACKUP LOG statement that is mentioned previously to make more                         of the virtual log files inactive and then run the DBCC SHRINKFILE statement that is mentioned later, again. After this operation, the transaction log file should be                         similar to the target size.

You can follow this link, if you want go through all options of DBCC SHRINKFILE:


Hope this answered your question fully.

Author Comment

ID: 24352285
Thanks guys for prompt reply.

I have managed to shrink the transaction log file by removing database mirroring changing it to simple mode and than mentioned the size of the log file to 1GB.

I assume I still have the risk of log file growing big time untill I take transaction log backup regularly ?

If I go down the route of taking transaction log backups. Can somebody please guide me on how abouts to do it in database mirroring ?

Thanks in advance.


Assisted Solution

bull_rider earned 1000 total points
ID: 24352550
Database mirroring is somewhat similar to transactional replication in that it requires that the transactions remain in the log until 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 will grow.

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 and restart mirroring.

Hope this helps. Please let me know if you want something in particular.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question