April 17, 2015 at 9:14 am
Hi
I'm in the process of re-initialising one of publications which has a very very large table.
when the snapshot is deployed to the subscriber the Bulk inserts chain together and fill up the entire transaction log (yes it is in simple recovery mode)
the transaction log is on a 1TB drive.
so to get around this issue we added another 2 drives and additional log files so that we had a little more breathing room - now my understanding of data files is that we get a proportional fill into the files, and with log files the first one fills up and once it is full the second one starts to fill up.
but we are observing that all 3 log files are growing at the same time
a dbcc sqlperf(logspace) shows the following - log size 561106.2, % used 99.90475 --- (our snapshot is still being deployed)
but looking at the data files
file a size 26131992 then after we get to 100% log 26259992
file b size 22220800 then after we get to 100% log 22348800
file c size 22188800 then after we get to 100% log 22316800
why are all 3 log files growing rather than just the first 1? and all being filled ???????
MVDBA
April 17, 2015 at 9:37 am
You just answered to your question - yes it is proportional fill.
But let me ask you another question: why don't you initialize replication with backup/restore? You won't have such problems with log file.
April 17, 2015 at 9:44 am
microsoft documentation states that it is linear fill for log files ....
we can't do initialise from backup as there are multiple publications replicating to the subscriber database - restoring from one of the publishers will kill the other subscriptions
MVDBA
April 17, 2015 at 9:50 am
We recently went through this with a 1 TB database where we upgraded an application and had to rebuild the transactional replication. We decided to to do it from backup rather than from a snapshot and it worked very well. Here is a useful link:
April 17, 2015 at 9:57 am
MVDBA (4/17/2015)
microsoft documentation states that it is linear fill for log files ....we can't do initialise from backup as there are multiple publications replicating to the subscriber database - restoring from one of the publishers will kill the other subscriptions
So you actually have Merge replication?
April 17, 2015 at 10:15 am
Have you tried to remove the publication rather than just reinitialize it? In our case we had better luck removing it and rebuilding from scratch. We have five publications using one subscriber database and didn't have any issues with the other pubs failing. Our setup is not a merge replication.
April 20, 2015 at 3:37 am
it is transactional publication, but multiple publishers pushing to a single subscriber database (not a single table). no merge replication at all.
i'm not interested in replication topology or which initialization method i should use, my question is about t-log usage and understanding the way it works.
i'm concerned that if i need to remove this temporary drive that i will not be able to drop the log file if it is in use. I also want to better understand the growth patterns so that i can keep my server uptime close to 100%
MVDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply