cleaning up system objects left by a merge repl.

Hi,
After disabling publishing on my server, there were
numerous merge replication related objects.
How do I clean them up. I tried to drop them, but I get a
message saying I am trying to drop system objects, and the
effort fails.
Thanks,
Sang
Sang,
try sp_removedbreplication (assuming the database is no longer contains any
publications/subscriptions).
Hilary Cotter sent me a link to a script he created at http://www.ava.co.uk
(technical resouces section) that you might want to look at, if the above
stored proc doesn't remove all the objects.
HTH,
Paul Ibison

Cleaning up merge metadata manually is not working

Hi all,

I'm trying delete metadata of a sql 2005 sp1 subscriber from a sql 2005 sp1 merge publication, but is not working, the "retention" parameter for the publication is 999 and this is the code I'm using:

declare @.num_genhistory_rows int,

@.num_contents_rows int,

@.num_tombstone_rows int

declare @.retcode smallint

--select count(*) from msmerge_contents

-- records before 2,633,848

exec @.retcode = sys.sp_mergemetadataretentioncleanup @.num_genhistory_rows OUTPUT , @.num_contents_rows OUTPUT , @.num_tombstone_rows OUTPUT

select retcode =@.retcode

select num_genhistory_rows =@.num_genhistory_rows

select num_contents_rows=@.num_contents_rows

select num_tombstone_rows=@.num_tombstone_rows

--select count(*) from msmerge_contents

-- records after 2,633,8

Results :

retcode

0

num_genhistory_rows

0

num_contents_rows

0

num_tombstone_rows

0

Has omebody any idea why this is not working ?

I did check "sp_mergemetadataretentioncleanup " and I note that is using a function to calculate the limit date, but I could not testing because it give me the below error :

declare @.curdate datetime, @.cutoffdate datetime

select @.curdate = getdate()

select @.cutoffdate = null

-- find max retention of all pubs the article belongs to.

-- add some safety margin to compensate for different clock speeds

select @.cutoffdate = min(sys.fn_subtract_units_from_date(isnull(retention,0), retention_period_unit, @.curdate))

from dbo.sysmergepublications where

pubid in (select pubid from dbo.sysmergearticles where nickname = 5088000)

select @.cutoffdate

and this is the message error:

Msg 4121, Level 16, State 1, Line 7

Cannot find either column "sys" or the user-defined function or aggregate "sys.fn_subtract_units_from_date", or the name is ambiguous.

I looked this function but I didn't find it.

any help will be appreciated !

Firstly, if your retention is 999 days, it means that only metadata that is 1000 days old will be cleaned up (automatically or manually). So do you have data that old in the first place?

If you want to cleanup metadata, set the retention to a lower value and you will start seeing the metadata getting cleaned up automatically when merge anget runs.

The sys.xxx functions are internal functions that reside in the system resource and hence cannot be called explictly by a user.

|||

Thanks for your response Mahesh,

Here is the complete story about 999

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1148225&SiteID=1

I suspect that if I change the "retention" parameter the subscriptions will expire, is this possible with sql 2005 SP1 ?

|||

the retention cleanup will take time on the first call because it will need to do some real cleanup.

However after this, every time merge agent runs, this proc will be called. But the delta to cleanup will be very less so it should not take that long, unless of course your data load everyday is very huge.

So now if you set it to 999, and not enough metadata is genenrated, you will not see anything/or see less metadata cleaned up.

sqlsql

checking the status of merge agent

My merge agent is getting stopped at midnight. because of which i have large
number of records which needs to be merged when i come in the morning.
Is it possible To create a job which will run every 3-4 hours, which will
check whether the merge agent is running or not? If the merge agent is
stopped then start the merge agent.
Is there a better way? Any suggestions?
just reschedule your merge agent to run every 5 minutes, or have step 4 on
failure return to step 1.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:F63A7EB7-31BF-44CE-A966-5089BF241D3C@.microsoft.com...
> My merge agent is getting stopped at midnight. because of which i have
large
> number of records which needs to be merged when i come in the morning.
> Is it possible To create a job which will run every 3-4 hours, which will
> check whether the merge agent is running or not? If the merge agent is
> stopped then start the merge agent.
> Is there a better way? Any suggestions?
>
|||> just reschedule your merge agent to run every 5 minutes,
This is a cool option...

> or have step 4 on failure return to step 1.
There is no step 4. I am using continous merge replication. There is only
one step. Hence i can't go to the first step. Or am I understanding it in a
different way?
"Hilary Cotter" wrote:

> just reschedule your merge agent to run every 5 minutes, or have step 4 on
> failure return to step 1.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:F63A7EB7-31BF-44CE-A966-5089BF241D3C@.microsoft.com...
> large
>
>
|||right click on your agent in the merge agents folder, select agent
properties, and then steps. Change Step 3 (not step 4 - my mistake) to wrap
around to step 1 on failure. Click on the advanced tab to do this.
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:4258F2A9-F495-40E6-ACBC-784AFDCBA4F9@.microsoft.com...
> This is a cool option...
> There is no step 4. I am using continous merge replication. There is only
> one step. Hence i can't go to the first step. Or am I understanding it in
a[vbcol=seagreen]
> different way?
>
> "Hilary Cotter" wrote:
on[vbcol=seagreen]
will[vbcol=seagreen]
|||I know what you are saying.
But in my agent property i have only one step. No 3 or 4 steps.
I am using merge replication. What does the 2nd and 3rd step contain?
"Hilary Cotter" wrote:

> right click on your agent in the merge agents folder, select agent
> properties, and then steps. Change Step 3 (not step 4 - my mistake) to wrap
> around to step 1 on failure. Click on the advanced tab to do this.
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:4258F2A9-F495-40E6-ACBC-784AFDCBA4F9@.microsoft.com...
> a
> on
> will
>
>
|||is this an ActiveX script you are running? Or are you pulling from SQL CE?
"ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
news:ACF2F24C-4715-4DFE-B6EB-FA12E06CD3B0@.microsoft.com...[vbcol=seagreen]
> I know what you are saying.
> But in my agent property i have only one step. No 3 or 4 steps.
> I am using merge replication. What does the 2nd and 3rd step contain?
> "Hilary Cotter" wrote:
wrap[vbcol=seagreen]
only[vbcol=seagreen]
in[vbcol=seagreen]
step 4[vbcol=seagreen]
have[vbcol=seagreen]
morning.[vbcol=seagreen]
which[vbcol=seagreen]
agent is[vbcol=seagreen]
|||I am not using ActiveX script
Also not SQL CE
"Hilary Cotter" wrote:

> is this an ActiveX script you are running? Or are you pulling from SQL CE?
> "ravi lobo" <ravilobo@.discussions.microsoft.com> wrote in message
> news:ACF2F24C-4715-4DFE-B6EB-FA12E06CD3B0@.microsoft.com...
> wrap
> only
> in
> step 4
> have
> morning.
> which
> agent is
>
>

Checking Merge agent status in SQL2005

Hi sql gurus,
Is there any way to check the status of the merge agent job per subscriber
in SQL 2005?
Below is the same check for SQL 2000:
use distribution
select runstatus from MSmerge_history as h
where h.timestamp in (select max(timestamp) from MSmerge_history where
agent_id =
( select top 1 id from msmerge_agents where subscriber_id =
(select srvid from master..sysservers where srvname =
'<subscriber_server_name>')) )
I discovered the solution
use distribution
select mh.*, isnull(ms.runstatus,0) as runstatus
from dbo.MSmerge_history mh with (READPAST), dbo.MSmerge_sessions ms with
(READPAST)
where mh.timestamp in (select max(timestamp) from MSmerge_history where
agent_id in
( select top 1 id from msmerge_agents where subscriber_id =
(select srvid from master..sysservers where srvname =
'subscriber_server_name'))) and ms.agent_id = mh.agent_id