| Groups | Blog | Home |
sql server replication : snapshot cleanup with sql server 2000 merge replication
I have a problem where old snapshots are not being deleted after new ones are created. I am running merge replication between two sql servers for multiple publications on multiple databases. Both servers are running sql server 2000 sp4 on windows 2000 sp4 with all updates installed. The publisher is also the distributor. Only push subscriptions are used. The snapshot agent for each publication is configured to run once per week (the default). The distributution clean up agent is configured to run once every 10 minutes. The distriubution cleanup agent job is owned by 'sa' and the account that the sql server agent runs as has full control permissions on the directories where the snapshots are stored. The distribution cleanup job runs sucessfully however old snapshots are never deleted. Out of curiousity, I looked at the stored prodcedure it runs(distribution.dbo.sp_MSdistribution_cleanup) and the stored procedures it calls. It looks like the procedure to cleanup the snapshots(distribution.dbo.sp_MSdelete_publisherdb_trans) is only called if the publication_id is returned by: select distinct publisher_database_id from MSrepl_transactions When I run this query, no results are returned. Additionally, if I generate a sql script for replication and select the distributor and check "script creation of replication jobs", the script contains: "-- **Error: Could not script out replication History Cleanup and Distribution Cleanup jobs for distribution databases, either user does not have permission to view the jobs or the jobs do not exist." Is this normal for merge replication or is something messed up? If so, can you point me to a knowledge base article about how to correct it. Also, I read another posting that suggested that it was not necessary to schedule snapshot generation when using merge replication if only push subscriptions were used. Is this correct? If so, should I disable/delete the schedule for all snapshot jobs for merge publications and only run generate the snapshots as needed and then manaully delete the old snapshots? Thanks,
For merge replication, the snapshot agent rather than the distribution
cleanup agent is responsible for cleaning up the last generated snapshot. The snapshot agent will only try once to remove the previous snapshot files so if those files were locked at that moment (by the merge agent e.g.), it is possible that snapshot files will be left over. As such, you may want to check the snapshot agent history and see if the snapshot agent reported any problems removing the old snapshot files in the past. Also note that there is currently no automatic cleanup mechanism for dynamic (or parameterized) snapshot in SQL2000. In SQL2005, we do have better reporting of any snapshot folder removal problems and better resiliency in the snapshot folder cleanup mechanism. HTH -Raymond [quoted text, click to view] "J.T. Moore" <JTMoore@discussions.microsoft.com> wrote in message news:BCD56BDC-645A-4E65-8DA0-8CF5A443D7FB@microsoft.com... > Hello, > > I have a problem where old snapshots are not being deleted after new ones > are created. > > I am running merge replication between two sql servers for multiple > publications on multiple databases. Both servers are running sql server > 2000 > sp4 on windows 2000 sp4 with all updates installed. The publisher is also > the > distributor. Only push subscriptions are used. The snapshot agent for each > publication is configured to run once per week (the default). The > distributution clean up agent is configured to run once every 10 minutes. > The > distriubution cleanup agent job is owned by 'sa' and the account that the > sql > server agent runs as has full control permissions on the directories where > the snapshots are stored. > > The distribution cleanup job runs sucessfully however old snapshots are > never deleted. Out of curiousity, I looked at the stored prodcedure it > runs(distribution.dbo.sp_MSdistribution_cleanup) and the stored procedures > it > calls. It looks like the procedure to cleanup the > snapshots(distribution.dbo.sp_MSdelete_publisherdb_trans) is only called > if > the publication_id is returned by: > > select distinct publisher_database_id > from MSrepl_transactions > > When I run this query, no results are returned. > > > Additionally, if I generate a sql script for replication and select the > distributor and check "script creation of replication jobs", the script > contains: > "-- **Error: Could not script out replication History Cleanup and > Distribution Cleanup jobs for distribution databases, either user does not > have permission to view the jobs or the jobs do not exist." > > > Is this normal for merge replication or is something messed up? If so, can > you point me to a knowledge base article about how to correct it. > > Also, I read another posting that suggested that it was not necessary to > schedule snapshot generation when using merge replication if only push > subscriptions were used. Is this correct? If so, should I disable/delete > the > schedule for all snapshot jobs for merge publications and only run > generate > the snapshots as needed and then manaully delete the old snapshots? > > Thanks, > > J.T.
I took a look at the snapshot agent history and didn't see any failures. I
also didn't see any messages about trying to delete old the snapshot. We are not using dynamic snapshots. We are using continous merge replication, so I tried stopping replication on one of the publications to insure the snapshot wasn't being locked by the merge agent and running the snapshot agent and the set the agent to log to a text file with -OutputVerboseLevel 2 Here are the contents of the log: ----------- Begin Log ----------- Microsoft SQL Server Snapshot Agent 8.00.2039 Copyright (c) 2000 Microsoft Corporation Microsoft SQL Server Replication Agent: IAS_SQL_02-CF_Client_Data-CF_Client_Data-94 Connecting to Distributor 'IAS_SQL_02' Connecting to Publisher 'IAS_SQL_02.CF_Client_Data' Server: DBMS: Microsoft SQL Server Version: 08.00.2039 user name: dbo API conformance: 2 SQL conformance: 1 transaction capable: 2 read only: N identifier quote char: " non_nullable_columns: 1 owner usage: 31 max table name len: 128 max column name len: 128 need long data len: Y max columns in table: 1024 max columns in index: 16 max char literal len: 524288 max statement len: 524288 max row size: 524288 [3/7/2006 6:42:40 PM]IAS_SQL_02.CF_Client_Data: sp_MSgetversion Initializing the publication 'CF_Client_Data' *** [Publication:'CF_Client_Data'] Publication view generation time: 110 (ms) *** *** [Publication:'CF_Client_Data'] Make generation time: 219 (ms) *** Generating schema script for article '[CGLOBAL]' Generating conflict schema script for article '[CGLOBAL]' Generating referential integrity script for article '[CGLOBAL]' Generating trigger script for article '[CGLOBAL]' Generating extended property script for article '[CGLOBAL]' *** [Article:'CGLOBAL'] Time generating all schema scripts: 2031 (ms) *** Generating schema script for article '[CDATA]' Generating conflict schema script for article '[CDATA]' Generating referential integrity script for article '[CDATA]' Generating trigger script for article '[CDATA]' Generating extended property script for article '[CDATA]' *** [Article:'CDATA'] Time generating all schema scripts: 1469 (ms) *** *** [System table:'MSmerge_contents'] .SCH script generation time: 15 (ms) *** *** [System table:'MSmerge_tombstone'] .SCH script generation time: 0 (ms) *** *** [System table:'MSmerge_genhistory'] .SCH script generation time: 16 (ms) *** *** [System table:'sysmergesubsetfilters'] .SCH script generation time: 0 (ms) *** [3/7/2006 6:42:45 PM]IAS_SQL_02.CF_Client_Data: select 1 from [dbo].[CGLOBAL] (TABLOCK HOLDLOCK) where 1=2 [3/7/2006 6:42:45 PM]IAS_SQL_02.CF_Client_Data: select 1 from [dbo].[CDATA] (TABLOCK HOLDLOCK) where 1=2 Bulk copying snapshot data for system table 'MSmerge_contents' select * from cont483DA94F38D3489C87D21E113D380C96 where 1 = 2 [3/7/2006 6:42:45 PM]IAS_SQL_02.CF_Client_Data: select * from cont483DA94F38D3489C87D21E113D380C96 where 1 = 2 Bulk copied snapshot data for system table 'MSmerge_contents'. *** [System table:'MSmerge_contents'] Bulk copy time: 687 (ms) *** Bulk copying snapshot data for system table 'MSmerge_tombstone' select * from ts483DA94F38D3489C87D21E113D380C96 where 1 = 2 [3/7/2006 6:42:45 PM]IAS_SQL_02.CF_Client_Data: select * from ts483DA94F38D3489C87D21E113D380C96 where 1 = 2 Bulk copied snapshot data for system table 'MSmerge_tombstone'. *** [System table:'MSmerge_tombstone'] Bulk copy time: 219 (ms) *** Bulk copying snapshot data for system table 'MSmerge_genhistory' select * from gh483DA94F38D3489C87D21E113D380C96 where 1 = 2 [3/7/2006 6:42:46 PM]IAS_SQL_02.CF_Client_Data: select * from gh483DA94F38D3489C87D21E113D380C96 where 1 = 2 Bulk copied snapshot data for system table 'MSmerge_genhistory'. *** [System table:'MSmerge_genhistory'] Bulk copy time: 312 (ms) *** Bulk copying snapshot data for system table 'sysmergesubsetfilters' select * from filt483DA94F38D3489C87D21E113D380C96 where 1 = 2 [3/7/2006 6:42:46 PM]IAS_SQL_02.CF_Client_Data: select * from filt483DA94F38D3489C87D21E113D380C96 where 1 = 2 Bulk copied snapshot data for system table 'sysmergesubsetfilters'. *** [System table:'sysmergesubsetfilters'] Bulk copy time: 219 (ms) *** Bulk copying snapshot data for article '[CGLOBAL]' select * from [dbo].[CGLOBAL] where 1 = 2 [3/7/2006 6:42:46 PM]IAS_SQL_02.CF_Client_Data: select * from [dbo].[CGLOBAL] where 1 = 2 Bulk copied snapshot data for article '[CGLOBAL]' (37895 rows). *** [Article:'CGLOBAL'] Bulk copy time: 906 (ms) *** Bulk copying snapshot data for article '[CDATA]' select * from [dbo].[CDATA] where 1 = 2 [3/7/2006 6:42:47 PM]IAS_SQL_02.CF_Client_Data: select * from [dbo].[CDATA] where 1 = 2 Bulk copied snapshot data for article '[CDATA]' (37438 rows). *** [Article:'CDATA'] Bulk copy time: 1000 (ms) *** *** [Publication:'CF_Client_Data'] Total snapshot generation time excluding publication setup: 10656 (ms) *** A snapshot of 2 article(s) was generated. Disconnecting from Publisher 'IAS_SQL_02' ----------- End Log ----------- Even while the merge agent is running, I can change the name of the snapshot folder and then change it back, which indicates to me that the file isn't locked. Any other ideas? Thanks, J.T. [quoted text, click to view] "Raymond Mak [MSFT]" wrote:
> For merge replication, the snapshot agent rather than the distribution > cleanup agent is responsible for cleaning up the last generated snapshot. > The snapshot agent will only try once to remove the previous snapshot files > so if those files were locked at that moment (by the merge agent e.g.), it > is possible that snapshot files will be left over. As such, you may want to > check the snapshot agent history and see if the snapshot agent reported any > problems removing the old snapshot files in the past. Also note that there > is currently no automatic cleanup mechanism for dynamic (or parameterized) > snapshot in SQL2000. In SQL2005, we do have better reporting of any snapshot > folder removal problems and better resiliency in the snapshot folder cleanup > mechanism. > > HTH > > -Raymond >
I am out of ideas other than this may be a bug. Unfortunately the lack of
diagnostic information in the SQL2000 snapshot agent makes it impossible to debug this over the newsgroup. You can try calling sp_browsemergesnapshotfolder N'<PublicationName>' and see if there is anything obviously amiss with the paths that are returned. If possible, we would really appreciate if you can log a bug at the MSDN feedback center at http://lab.msdn.microsoft.com/productfeedback/ with sufficient information (servername + pubdb name + publication name etc) so we can have a good shot at reproducing the problem. -Raymond [quoted text, click to view] "J.T. Moore" <JTMoore@discussions.microsoft.com> wrote in message
news:AA5CBD56-0086-432D-849B-2854C6466070@microsoft.com... >I took a look at the snapshot agent history and didn't see any failures. I > also didn't see any messages about trying to delete old the snapshot. > > We are not using dynamic snapshots. > > We are using continous merge replication, so I tried stopping replication > on > one of the publications to insure the snapshot wasn't being locked by the > merge agent and running the snapshot agent and the set the agent to log to > a > text file with -OutputVerboseLevel 2 > > Here are the contents of the log: > > ----------- Begin Log ----------- > Microsoft SQL Server Snapshot Agent 8.00.2039 > Copyright (c) 2000 Microsoft Corporation > Microsoft SQL Server Replication Agent: > IAS_SQL_02-CF_Client_Data-CF_Client_Data-94 > > Connecting to Distributor 'IAS_SQL_02' > Connecting to Publisher 'IAS_SQL_02.CF_Client_Data' > > Server: > DBMS: Microsoft SQL Server > Version: 08.00.2039 > user name: dbo > API conformance: 2 > SQL conformance: 1 > transaction capable: 2 > read only: N > identifier quote char: " > non_nullable_columns: 1 > owner usage: 31 > max table name len: 128 > max column name len: 128 > need long data len: Y > max columns in table: 1024 > max columns in index: 16 > max char literal len: 524288 > max statement len: 524288 > max row size: 524288 > > [3/7/2006 6:42:40 PM]IAS_SQL_02.CF_Client_Data: sp_MSgetversion > Initializing the publication 'CF_Client_Data' > > *** [Publication:'CF_Client_Data'] Publication view generation time: 110 > (ms) *** > > > *** [Publication:'CF_Client_Data'] Make generation time: 219 (ms) *** > > Generating schema script for article '[CGLOBAL]' > Generating conflict schema script for article '[CGLOBAL]' > Generating referential integrity script for article '[CGLOBAL]' > Generating trigger script for article '[CGLOBAL]' > Generating extended property script for article '[CGLOBAL]' > > *** [Article:'CGLOBAL'] Time generating all schema scripts: 2031 (ms) *** > > Generating schema script for article '[CDATA]' > Generating conflict schema script for article '[CDATA]' > Generating referential integrity script for article '[CDATA]' > Generating trigger script for article '[CDATA]' > Generating extended property script for article '[CDATA]' > > *** [Article:'CDATA'] Time generating all schema scripts: 1469 (ms) *** > > > *** [System table:'MSmerge_contents'] .SCH script generation time: 15 (ms) > *** > > > *** [System table:'MSmerge_tombstone'] .SCH script generation time: 0 (ms) > *** > > > *** [System table:'MSmerge_genhistory'] .SCH script generation time: 16 > (ms) > *** > > > *** [System table:'sysmergesubsetfilters'] .SCH script generation time: 0 > (ms) *** > > [3/7/2006 6:42:45 PM]IAS_SQL_02.CF_Client_Data: select 1 from > [dbo].[CGLOBAL] (TABLOCK HOLDLOCK) where 1=2 > [3/7/2006 6:42:45 PM]IAS_SQL_02.CF_Client_Data: select 1 from > [dbo].[CDATA] > (TABLOCK HOLDLOCK) where 1=2 > Bulk copying snapshot data for system table 'MSmerge_contents' > select * from cont483DA94F38D3489C87D21E113D380C96 where 1 = 2 > [3/7/2006 6:42:45 PM]IAS_SQL_02.CF_Client_Data: select * from > cont483DA94F38D3489C87D21E113D380C96 where 1 = 2 > > Bulk copied snapshot data for system table 'MSmerge_contents'. > > *** [System table:'MSmerge_contents'] Bulk copy time: 687 (ms) *** > > Bulk copying snapshot data for system table 'MSmerge_tombstone' > select * from ts483DA94F38D3489C87D21E113D380C96 where 1 = 2 > [3/7/2006 6:42:45 PM]IAS_SQL_02.CF_Client_Data: select * from > ts483DA94F38D3489C87D21E113D380C96 where 1 = 2 > > Bulk copied snapshot data for system table 'MSmerge_tombstone'. > > *** [System table:'MSmerge_tombstone'] Bulk copy time: 219 (ms) *** > > Bulk copying snapshot data for system table 'MSmerge_genhistory' > select * from gh483DA94F38D3489C87D21E113D380C96 where 1 = 2 > [3/7/2006 6:42:46 PM]IAS_SQL_02.CF_Client_Data: select * from > gh483DA94F38D3489C87D21E113D380C96 where 1 = 2 > > Bulk copied snapshot data for system table 'MSmerge_genhistory'. > > *** [System table:'MSmerge_genhistory'] Bulk copy time: 312 (ms) *** > > Bulk copying snapshot data for system table 'sysmergesubsetfilters' > select * from filt483DA94F38D3489C87D21E113D380C96 where 1 = 2 > [3/7/2006 6:42:46 PM]IAS_SQL_02.CF_Client_Data: select * from > filt483DA94F38D3489C87D21E113D380C96 where 1 = 2 > > Bulk copied snapshot data for system table 'sysmergesubsetfilters'. > > *** [System table:'sysmergesubsetfilters'] Bulk copy time: 219 (ms) *** > > Bulk copying snapshot data for article '[CGLOBAL]' > select * from [dbo].[CGLOBAL] where 1 = 2 > [3/7/2006 6:42:46 PM]IAS_SQL_02.CF_Client_Data: select * from > [dbo].[CGLOBAL] where 1 = 2 > > Bulk copied snapshot data for article '[CGLOBAL]' (37895 rows). > > *** [Article:'CGLOBAL'] Bulk copy time: 906 (ms) *** > > Bulk copying snapshot data for article '[CDATA]' > select * from [dbo].[CDATA] where 1 = 2 > [3/7/2006 6:42:47 PM]IAS_SQL_02.CF_Client_Data: select * from > [dbo].[CDATA] > where 1 = 2 > > Bulk copied snapshot data for article '[CDATA]' (37438 rows). > > *** [Article:'CDATA'] Bulk copy time: 1000 (ms) *** > > > *** [Publication:'CF_Client_Data'] Total snapshot generation time > excluding > publication setup: 10656 (ms) *** > > A snapshot of 2 article(s) was generated. > Disconnecting from Publisher 'IAS_SQL_02' > ----------- End Log ----------- > > Even while the merge agent is running, I can change the name of the > snapshot > folder and then change it back, which indicates to me that the file isn't > locked. > > Any other ideas? > > Thanks, > > J.T. > > > > > "Raymond Mak [MSFT]" wrote: > >> For merge replication, the snapshot agent rather than the distribution >> cleanup agent is responsible for cleaning up the last generated snapshot. >> The snapshot agent will only try once to remove the previous snapshot >> files >> so if those files were locked at that moment (by the merge agent e.g.), >> it >> is possible that snapshot files will be left over. As such, you may want >> to >> check the snapshot agent history and see if the snapshot agent reported >> any
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
October 2008
| home · blog · groups | Questions? Comments? Contact the dn |