Solved

Database Mirroring and Log File Size

Posted on 2009-05-10
5
262 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore 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.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

17 Experts available now in Live!

Get 1:1 Help Now