Solved

Database Mirroring and Log File Size

Posted on 2009-05-10
5
265 Views
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.

0
Comment
Question by:matrix_aash
  • 2
  • 2
5 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 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.
0
 
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.
0
 
LVL 6

Expert Comment

by:bull_rider
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:
DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS

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:

http://msdn.microsoft.com/en-us/library/ms189493.aspx

Hope this answered your question fully.
0
 
LVL 8

Author Comment

by:matrix_aash
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.

0
 
LVL 6

Assisted Solution

by:bull_rider
bull_rider earned 250 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.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

807 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