March 18, 2008 at 6:36 am
Hi all,
Gladly welcome your advices / suggestion to have in mind before truncating log file of a Replicated database running under Merge replication. I need to truncate the Publishers Transaction log.
Our publishers transaction log is beyond 50GB in size and still growing. I found lot of ways from our website to shrink it. But it will be better to get suggestion from experts before applying them on production environment.
Thanks,
Balakrishnan.K
March 18, 2008 at 7:00 am
Balakrishnan K (3/18/2008)
Hi all,Gladly welcome your advices / suggestion to have in mind before truncating log file of a Replicated database running under Merge replication. I need to truncate the Publishers Transaction log.
Our publishers transaction log is beyond 50GB in size and still growing. I found lot of ways from our website to shrink it. But it will be better to get suggestion from experts before applying them on production environment.
Thanks,
Balakrishnan.K
You should be able to just go ahead and truncate the transaction log, which will truncate all commited transactions that have also been marked as replicated. Once you've shrunk the transaction log use dbcc shrinkfile to shrink the log file.
Oh, and if you're not planning on backing up your transaction log then switch it to simple mode. Although it's always recommended that you have a transaction backup policy in place.
April 23, 2008 at 7:56 am
Hi All,
I would like to share the method to shrink Transaction Log of the DB which is a part of Replication proces. It is very simple and safe comparing to other shrinking methods.
Steps to shrink Transaction Logs:
1. Take Transactional backup of the database, with “Remove in-active log entries” option checked.
2. Shrink the Database using "Shrink Database" option in “All tasks” menu.
3. Repeat step 1.
4. Repeat step 2.
Kindly note that, we need to do the steps twice, then only SQL-Server shrinking the Log file.
Thanks,
Balakrishnan.K
April 24, 2008 at 5:23 pm
SQLZ (3/18/2008)
Balakrishnan K (3/18/2008)
Hi all,Gladly welcome your advices / suggestion to have in mind before truncating log file of a Replicated database running under Merge replication. I need to truncate the Publishers Transaction log.
Our publishers transaction log is beyond 50GB in size and still growing. I found lot of ways from our website to shrink it. But it will be better to get suggestion from experts before applying them on production environment.
Thanks,
Balakrishnan.K
You should be able to just go ahead and truncate the transaction log, which will truncate all commited transactions that have also been marked as replicated. Once you've shrunk the transaction log use dbcc shrinkfile to shrink the log file.
Oh, and if you're not planning on backing up your transaction log then switch it to simple mode. Although it's always recommended that you have a transaction backup policy in place.
replicated database needs to be in FULL mode, it will error when you try to switch it into simple mode.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
August 13, 2008 at 3:10 am
Hi All,
i only need to know why we need to run steps twice.
also is it any way make FULL BACKUP for publisher truncate enactive transaction log
August 15, 2008 at 10:50 am
engkemo_eg (8/13/2008)
Hi All,i only need to know why we need to run steps twice.
also is it any way make FULL BACKUP for publisher truncate enactive transaction log
When databases are replicated you *must* (in addition of taking full backups) take transaction log backups
to clear the already replicated log entries.
* Noel
August 15, 2008 at 11:16 am
noeld (8/15/2008)
engkemo_eg (8/13/2008)
Hi All,i only need to know why we need to run steps twice.
also is it any way make FULL BACKUP for publisher truncate enactive transaction log
When databases are replicated you *must* (in addition of taking full backups) take transaction log backups
to clear the already replicated log entries.
Noel is quite correct, the Full Backup won't release the transactions in the log.
We also add a step to CHECKPOINT the database right before the Transaction Log backup and that seems to help release the hold on the file (but it usually takes a few iterations to reap the full benefits).
Thus, our standard process (which we're using in production) is this:
1. CHECKPOINT
2. Transaction Log Backup
3. Shrink the log file
4. Repeat steps 1 - 3 until file size is reasonable.
August 15, 2008 at 11:40 am
A few comments...
Replicated databases do not require full recovery mode. That's true of log shipping and database mirroring. Replication works fine in any recovery mode.
Don't shrink databases on a regular basis (preferably don't shrink them at all). It cases bad index fragmentation, the databese is just going to grow again, possibly causing file-level fragmentation
Log files also should not be shrunk on a regular basis. If the log has to grow repeatedly, it increases the amount of virtual log files, which makes backups slower. The usual recommendation is to work out how big the log need to be for the activity seen and the interval of log backups, and then leave them at that size.
The first thing to do with a transaction log that's growing beyond expected is to find out why it's growing, why the space is not being reused. In SQL 2005 and higher, that's very easy.
SELECT name, log_reuse_wait_desc FROM master.sys.databases
The log_reuse_wait_desc column shows why the log space is not being reused. Once that's known, then an appropriate course of action can be picked.
</soapbox>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2008 at 1:55 pm
Totally agree with Gail. Don't shrink, you just need to back the log up "regularly" ( meaning as often as needed )
-Cheers!
* Noel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply