Solved

Database Mirroring and Log File Size

Posted on 2009-05-10
5
263 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now