Slow transactional replication reinitialize

Question

Post reply

Slow transactional replication reinitialize

DKlein

SSCrazy

Points: 2351
More actions
December 15, 2010 at 8:31 am

#232472

We have a 140 gig db that is taking 14 hours to reinitialize to our reporting server. We are only shipping data. Any tips for troubleshooting or streamlining the process without filtering?
Thanks,
DK

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Have you read this article?

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

DKlein SSCrazy Points: 2351 More actions · Answer 2

Thanks for the reply David, that was helpful. I am lining up a test to implement some physical changes.

Thanks,

DK

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

Great! If you have time reply back with what you find helpful as it may be good information for the future.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

DKlein SSCrazy Points: 2351 More actions · Answer 4

I just learned the publisher is on a 32bit server and the distributor and subscriber are on a 64bit server. The 32bit server is AWE enabled and using a little over 14 GB of ram. How much would this influence the outcome?

Thanks,

DK

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

That 14hours is probably better than it would be if it was 32-32..

Are the database files pre-sized? If not then it will spend a lot of time growing them.. Shaved 30% off a SQL migration pre-sizing the files.. Highly recommend it.

CEWII

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 6

If it is busy and you have memory limitations already then that would certainly impact things. Do you know how long the copy process is taking vs the applying of the snapshot? If you are heavily weighted on the application of the snapshot the the subscriber then I would say your subscriber is underpowered. There would a necessity to confirm that though.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 7

Elliott Whitlow (12/17/2010)
That 14hours is probably better than it would be if it was 32-32..
Are the database files pre-sized? If not then it will spend a lot of time growing them.. Shaved 30% off a SQL migration pre-sizing the files.. Highly recommend it.
CEWII

Yes wholeheartedly agree and great point to consider.

Obviously disk performance plays a big part in this too. You should be watching that as well during the process.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

DKlein SSCrazy Points: 2351 More actions · Answer 8

Wow, thanks for the feedback Elliott and David.

I am not sure about the pre-sizing, how would I go about doing that, or could you suggest a good article?

Found this link:

http://www.sqlservercentral.com/Forums/Topic1020378-146-1.aspx#bm1020501

Thanks again,

DK

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

when the database is created on the destination side set its size close to or above the ending size it will be, limit the number of times it has to grow.. I don't have an article citation, sorry.

CEWII