Suggestion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Suggestion

Author

Topic

swekik
Posting Yak Master

126 Posts

Posted - 2008-12-02 : 21:30:35

We have a transactional replication with a pull subscriptions (Replication from production to stg).Right now they are out of sync for 5 days .We started the log reader agent and its trying to sync with the subscriber.But the undistributed commands are increasing .

What is the best way to sync back with the subscriber ?Recreate a new snapshot ?

Any Suggestions

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-03 : 09:27:51

"best" is a dangerous word... everyone has opinions about that. Regenerating the snapshot an resynchronizing would be my first choice, especially if it's been 5 days; it's possible that it can't resync at that point, depending on the settings used when the replication was originally created. If that didn't work, I'd delete and recreate the subscription... but under most circumstances I doubt that would be necessary. As a last resort you could always wipe out the publication and subscription, and resync the whole thing.

Keep in mind that creating a snapshot is an IO-intensive operation, and has a tendency to lock EVERYTHING in the publisher database. I'd recommend doing it after hours if at all possible.

___________________________
Geek At Large

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 09:59:05

First of all, It is bad idea to do replication from production to staging. You are giving performance issue to production. How big is database in publisher?

swekik
Posting Yak Master

126 Posts

Posted - 2008-12-03 : 11:50:41

Thanks for the response .I posted the same post in replication group.I think most people used to see only administration group,Thats y i posted it here .

Database is 500GB.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 11:53:59

Then It is bad to replicate from Prod to stg for 500 GB database.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 12:36:55

quote:
Originally posted by jholovacs

"best" is a dangerous word... everyone has opinions about that. Regenerating the snapshot an resynchronizing would be my first choice, especially if it's been 5 days; it's possible that it can't resync at that point, depending on the settings used when the replication was originally created. If that didn't work, I'd delete and recreate the subscription... but under most circumstances I doubt that would be necessary. As a last resort you could always wipe out the publication and subscription, and resync the whole thing.

Keep in mind that creating a snapshot is an IO-intensive operation, and has a tendency to lock EVERYTHING in the publisher database. I'd recommend doing it after hours if at all possible.

___________________________
Geek At Large

There is option you can create snapshot without locking database.You have to change sync method to non-defaut one but it takes time to generate.
See this:
http://msdn.microsoft.com/en-us/library/ms188738.aspx

swekik
Posting Yak Master

126 Posts

Posted - 2008-12-03 : 13:48:16

Thanks Sodeep.

What is the best way to monitor the replication(If the Publisher & subscriber are out of sync for 10 min,it is going to send an e-mail to us)?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 14:59:13

We use third-party tools to monitor and send mail. You can configure replication jobs to send email if it fails so you know when to troubleshoot.

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-03 : 16:41:54

You might want to make a rule to put the email alerts in another folder... when it breaks, it usually sends out an email alert every 2 minutes for every subscription/ publication that's broken until you fix it. It can be a bit overwhelming.

I wish I could convince the purseholders to go third-party myself...

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 17:42:31

quote:
Originally posted by jholovacs

You might want to make a rule to put the email alerts in another folder... when it breaks, it usually sends out an email alert every 2 minutes for every subscription/ publication that's broken until you fix it. It can be a bit overwhelming.

I wish I could convince the purseholders to go third-party myself...

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

This looks better:

SELECT TOP 1
w.[name]
FROM
dbo.women w
LEFT OUTER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

swekik
Posting Yak Master

126 Posts

Posted - 2008-12-03 : 17:50:08

The weird thing is

In replication
Distributor to Subscriber History
there are 4 distribution agents are running,Which is odd.

Generally there will be only one distributiom agent running for one subscrieber.

One distribution agent trying to apply the snapshot ,after it finishes applying the snapshot,again one more distribution agent is going to start applying the same snapshot

right now 5 distribution agents are running.

How to kill the old distribution agents?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 17:54:32

I guess you have this issue:

http://support.microsoft.com/kb/934188

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-03 : 18:12:19

quote:

This looks better:

SELECT TOP 1
w.[name]
FROM
dbo.women w
LEFT OUTER JOIN

For shame! That's bad SQL. You're forcing an outer join into an inner join operation. Besides, the inlaw requirements are kinda important...

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 19:44:03

quote:
Originally posted by jholovacs

quote:

This looks better:

SELECT TOP 1
w.[name]
FROM
dbo.women w
LEFT OUTER JOIN

For shame! That's bad SQL. You're forcing an outer join into an inner join operation. Besides, the inlaw requirements are kinda important...

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

Yes it is shame to put shameless Quote like this being professional DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources