Solved

Database Mirroring and Log File Size

Posted on 2009-05-10
5
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

749 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