Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScleanup_subscriber_history

  No additional text.


Syntax

create procedure sys.sp_MScleanup_subscriber_history @subid uniqueidentifier = NULL, @keep_sessions int = 0 as
begin
    set nocount on

    declare @lowest_session_id_to_keep int
    declare @ts_marker binary(8)

    if @subid is not null
    begin

        if (select count(*) from dbo.MSmerge_sessions with (nolock) where subid = @subid) <= @keep_sessions
            and (select count(*) from dbo.MSmerge_history MH JOIN dbo.MSmerge_sessions MS
                    on MH.session_id = MS.session_id
                    where MS.subid=@subid
                    )<5500 --add 500 rows to the upper bound so that we don't call the cleanup for every sync when 5000 is reached
            return 0

        --only keep the latest 5000 rows for each subscription
 		select top 5000 @ts_marker = mh.timestamp
		from dbo.MSmerge_history mh JOIN dbo.MSmerge_sessions ms ON
		mh.session_id = ms.session_id
        where ms.subid = @subid
        order by mh.timestamp desc

        delete dbo.MSmerge_history
		from dbo.MSmerge_history mh JOIN dbo.MSmerge_sessions ms ON
		mh.session_id = ms.session_id
        where ms.subid = @subid
        and mh.timestamp < @ts_marker

        select @lowest_session_id_to_keep = session_id
        from dbo.MSmerge_sessions ms1
        where subid = @subid
        and (select count(*) from dbo.MSmerge_sessions ms2
                where ms2.subid = @subid
                and ms2.session_id > ms1.session_id) = @keep_sessions

        if @lowest_session_id_to_keep is null
            return 0

        delete from dbo.MSrepl_errors where session_id <= @lowest_session_id_to_keep
            and session_id in (select session_id from dbo.MSmerge_sessions
                                where subid = @subid)

        delete from dbo.MSmerge_history where session_id <= @lowest_session_id_to_keep
            and session_id in (select session_id from dbo.MSmerge_sessions where subid = @subid)


        delete from dbo.MSmerge_articlehistory where session_id <= @lowest_session_id_to_keep
            and session_id in (select session_id from dbo.MSmerge_sessions
                                where subid = @subid)

        delete from dbo.MSmerge_sessions where session_id <= @lowest_session_id_to_keep
            and session_id in (select session_id from dbo.MSmerge_sessions
                                where subid = @subid)
    end
    else
    begin
        delete from dbo.MSmerge_sessions where subid not in
            (select subid from dbo.sysmergesubscriptions)

        delete from dbo.MSmerge_articlehistory where session_id not in
            (select session_id from dbo.MSmerge_sessions)

        delete from dbo.MSmerge_history where session_id not in
            (select session_id from dbo.MSmerge_sessions)

        delete from dbo.MSrepl_errors where session_id not in
            (select session_id from dbo.MSmerge_sessions)
    end

    return 0
end

 
Last revision 2008RTM
See also

  sp_addmergepullsubscription (Procedure)
sp_addmergesubscription (Procedure)
sp_dropmergepublication (Procedure)
sp_dropmergepullsubscription (Procedure)
sp_dropmergesubscription (Procedure)
sp_mergesubscription_cleanup (Procedure)
sp_MSaddinitialpublication (Procedure)
sp_MSadd_merge_history90 (Procedure)
sp_MSdrop_expired_mergesubscription (Procedure)
sp_MSpublicationcleanup (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash