Subscriber Cleanup After Deleting Subscription

May 15, 2006

I have a 3 tier replication with one master server on tier1, 3 servers
on tier 2 and up to 20 remote servers (tier 3) on each of the tier 2
servers. I recently had to delete the subscriptions to each of
the tier 3 servers from the tier 2 servers and then recreate the
subscription. All went well for most of the subscriptions, but
for 3 subscriptions the subscription was dropped at the tier 2 level
but failed to drop at the subscriber due to a time out. I went to
the tier 3 servers in question and issued the sp_removedbreplication
command that supposedly executed properly. On one of these 3
servers everything worked fine and I was able to re-subscribe
successfuly and have everything continue to work properly. On the
other 2 it appeared the re-subscription worked properly until users
began to try to update the database through adds and updates (all
controlled through stored procedures). Users receive an error
message claiming an invalid object name ctsv_ followed by guid looking
string of characters. I suspect it has to do with all of the
stored procedures that were created by merge replication when the
subscription was set up and initialized (no sync). These include
numerous sp_ins_, sp_sel_, and sp_upd_ ... named procedures and a bunch
of dt_ ... stored procedures as well.



How do I clean this up so that I can have users using their database
again and then re-subscribe again short of rebuilding the database (a
very time consuming process)?

View 1 Replies


ADVERTISEMENT

Cleanup Task Deleting Everything - T-SQL

Apr 24, 2007

Hi all,

I created a maintenance plan with a cleanup task to delete bak files older than 4 days, but it keeps deleting everything but the current day files.

This is the generated T-SQL:

EXECUTE master.dbo.xp_delete_file 0,N'F:SQLDBBACKUP',N'BAK',N'2007-04-20T11:59:30',1

Someone can tell what's wrong with it?

I've noticed that wheter if you put the condition on files' age or not it generates exactly the same T-SQL statement, it says that the actual one could be different if you set conditions on the task, but if it is so why they work the saem way?

Thanks!

Cheers,

Giovanni

View 4 Replies View Related

SQL 2005 Maintenance Cleanup Task Is Not Deleting Files On Remote File Share

Jan 16, 2008

I have the following issue with Maintenance plan backups that work for BAK DIF and TRN to a remote server share.
When I try and remove the old files with a clean up task I get an error and the files don't get deleted.


The version is as follows
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on
Windows NT 5.2 (Build 3790: Service Pack 2)

The error result is as follows,

Failed-1073548784) Executing the query "EXECUTE master.dbo.xp_delete_file
0,N'\\EXECUTE master.dbo.xp_delete_file 0,N'\ABCD-A1\BACKUPS\ABCD_BACKUP\ABC_DAILY\ABCD',N'trn',N'2008-01-13T12:52:49'" failed with the following error: "xp_delete_file() returned error 2, 'The
system cannot find the file specified.'". Possible failure reasons: Problems
with the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.

The maintenance plan seems to be adding extra "" though when i enter the
code directly in a query i get same error.

Query:

EXECUTE master.dbo.xp_delete_file 0,N'\ABCD-A1BACKUPSABCD_BACKUPABC_DAILYABCD',N'trn',N'2008-01-13T12:52:49'

Error:

xp_delete_file() returned error 2, 'The system cannot find the file specified.'

The servers belong to the same domain and are using the same Service account which has all the necessary rights to the share and the file directory location. The backups work but i get the error on the cleanup task.

Trying to figure out how to get the Cleanup task to delete old files. The same happens for all file extensions and I have tried other locations with simpler file paths same error.


Regards,
Scott


View 6 Replies View Related

Deleting Unwanted Entries At The Subscriber .

Oct 8, 2002

Hello ,

I have created a publication from SQL Server 7.0 and subscribed the publication it to one of the SQL 2000 server std. edition .

Now there are some entries in the subscription list at the subscriber which need to be deleted .
The list shows the name of the publication , the database name and the date .

I wanted to know hoe can we delete those unwanted entries. If i select the entry and right click on it , there is no delete option .

Are those entries to be deleted from one of the tables at the subscriber or the publisher ? If yes, then what is the name of the table ?

Any ideas ?

Thank you very much .

View 3 Replies View Related

Monitoring Merge Push Subscription From Subscriber

Jul 3, 2007

Hi all!

Is there an option to monitor current state of Merge Push Subscription from the Subscriber, without connecting to the Publisher Server? I have examined many SPs and system tables at Subscriber, but didn't find any reliable method...

View 4 Replies View Related

SQL 2012 :: Updatable Subscription - View Pending Log From Subscriber To Publisher

May 7, 2015

I have setup Transnational replication with updatable subscription.

There were around 7000 rows update at subscriber from one query which was executed at subscriber side.

But this update has not taken place at publisher yet and so to other subscribers.

Is there any way i can monitor pending log or where is the issue?

I find so many ways to see pending logs or statistics for - "Publisher to Subscriber" but not for "subscriber to publisher"

What is the way to see subscriber to publisher pending and issues?

View 0 Replies View Related

SP2 Deleting Subscription Problem

Feb 13, 2007

I have installed SP2 and have been testing it. I have found an issue with deleting subscriptions thru report manager. I get an error on page:


Line: 1
Char: 58
Error: 'return' statement outside of function
Code: 0

I can delete thru SSMS with no problem.

Is anyone else have an issue with this?

Thanks!

BobP

View 3 Replies View Related

Deleting The Master Table Withour Deleting The Child Tables

Aug 9, 2007

Hi
i have to delete the master table data without deleting the child table records,is there any solution for this,  parent table has relation with the child table.
regards
vinod.t.v

View 9 Replies View Related

T-SQL (SS2K8) :: Deleting Only 1 Row At A Time Instead Of Using Condition And Deleting Many Rows?

Jul 18, 2014

/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]

-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.

[Code] ...

*** Getting this error below when executing the code ***

Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.

View 9 Replies View Related

SSRS - Resetting A Subscription After A Subscription Run Failure

Feb 11, 2008



It seems to me that if a scheduled SSRS report subscription fails ( Status Message - An error has occurred during report processing) , that I actually need to delete the subscription and reconfigure it from scratch. The scheduled job doesn't try to run again automatically (say the next Monday on a weekly Monday schedule).

Is there a way to "reset" a failed subscription without have to recreate the entire subscription?

Thank you
Devon Kyle

View 5 Replies View Related

Existing Subscription Already Contains Table Included In The New Subscription

Sep 26, 2007







28638


SSCE_M_DUPLICATETABLE


Existing subscription already contains table included in the new subscription.


What are the possible causes of this merge replication error?
Could it be caused by a SQL Server Compact Edition User trying to sync their .sdf file after their subscription has already expired on the SQL Server?

Would you expect to see a different message if a SQL Server Compact Edition user tried to sync a subscriber database (.sdf file) with merge replication if it's been longer than the subscription retention period since their last sync?

View 6 Replies View Related

SQL DB Cleanup

Oct 12, 2007

We have SQL SERVER 2000 Runnin on our server. We are trying to do a db cleanup, ie all tbls/views that were created earlier and are not needed any more, need to be deleted. However, is there a way, to do a cleanup in a better method, other than going thru the whole bunch of tbls/views manually,and determining which ones are needed or not and delete the ones that are not needed.

View 5 Replies View Related

Replication Cleanup Sp.

Feb 9, 1999

Which sp code part is efficient?
1st code from sp_MSdistribution_cleanup. Used for Replication Cleanup job.
For 1500 rows, runs a day. Is something wrong with this code?
2nd code part is an alternative idea!.
/************************************************** *****************/
delete MSsubscriber_status from MSsubscriber_status ss1 where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db and
job_id < @max_cleanup_job and
job_id <> (select max(job_id) from
MSsubscriber_status ss2 (index = ucMSsubscriber_status) where
ss2.publisher_id = @publisher_id and
ss2.publisher_db = @publisher_db and
ss2.subscriber_id = @subscriber_id and
ss2.subscriber_db = @subscriber_db)
/************************************************** *******************/
select @maxCleanup_job = max(job_id) from
MSsubscriber_status (index = ucMSsubscriber_status) where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db
delete MSsubscriber_status from MSsubscriber_status ss1 where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db and
job_id < @max_cleanup_job and
job_id <> @maxCleanup_job

View 3 Replies View Related

Ghost Cleanup....

Mar 28, 2008

Hi:

I need to restore a DB but it was prevented by a background process of "Ghost Cleanup".
server is SQL2000 ENT. sp4.

It could not be killed, neither it was stoped after restart the server. Is there a way to change its running schedule and/or to kill it when I need to restore the db?

thanks
David

View 1 Replies View Related

Cleanup The Server Log?

May 14, 2007

Hi,

Just wanted to know how can I delete those old server/error/agent log?

Thanks!

View 1 Replies View Related

Deletescript For Cleanup

Oct 26, 2007



Hi everyone,

I'm writing a small script to clean up our database. We have a couple of databases which contain many gigabytes of data.

The script fills a few temp tables, with price-id's which can be deleted (based on a few rules). Then it deletes all related data first, before actually deleting the price records themselves.

This works fine, except for performance issues. After 12 hours, I had to cancel the running script as it was taking too many resources.

My question is, how can I increase performance? Should I add 'commit' after deleting data in each table? Would it help to make it a stored procedure?

Script looks kinda like this:

SELECT priceId INTO #tobedeleted FROM prices WHERE ... (few rules)

DELETE FROM price_product WHERE priceId IN ... #tobedeleted
....
....

View 6 Replies View Related

Distribution Cleanup

Jun 17, 2007

I would love to be able to run the distribution cleanup job with a switch that says cleanup all distributed transactions.

Because when I use peer to peer replication the @allow_initialize_from_backup publication property is set to true which is good. But it has the down side that transactions are stored the max retention period in the distribution database. I want to use the deafault 72 hours for my retention period so that the subscritions don't get deactivated but in a system with a high transaction rate there wil be a lot of transactions in 72 hours. This means that the cleanup job will have a tough time to figure out which transactions to delete so the cleanup job will run for a long time not a very big problem but the problem is that the cleanup job will keep the log reader agent from delivering trtansactions to the distribution database and the subscribers won't get their data in time.



Could Microsoft please give me a switch so I can choose when I want to save my transaction and when I want to delete them as soon as they have been delivered to all subscribers?

Is this a feature in SQL Server 2008? Could it be released in SP3 for SQL Server 2005. (The SP 2 cleanup job has a bug so I have to use the SP 1 verison of the cleanup job)

View 2 Replies View Related

How To Cleanup All Database&#65311;

Mar 6, 2006

hello,if I want to cleanup all the data that in a database,how to do?
thanks!

View 5 Replies View Related

History Cleanup Task

Feb 11, 2008

I have set up a history cleanup task to be performed once per week, however it doesn't seem to delete the old files, what am I doing wrong?

View 8 Replies View Related

Maint Cleanup Task

Mar 19, 2007

Ques; can "both" files the db backup (.bak) file "and" the (.txt)report file in a maint plan object be cleaned up at same time?

The object is working ok but trying to setup and match backup text reports to the db, I have way more .bak files than text files.

Created from/after db maint plan (which is working ok) from/in the object Maintenance Cleanup Task and the object selections are

Delete files of the following type:
_backup files
_maintenance plan text reports

File location:
_delte specific file
File name: __
_search folder and delete files based on an extension
Folder:__
File extension:__
File age:
_Delete files based on the age of the file at task run time...
delete files oder than the following. . . .

View 1 Replies View Related

Problem With Ghost Cleanup

Nov 12, 2007

I have problem with 'Ghost cleanup' system process. It is locking up my tables and user transactions are keep getting wait status. So is there any way to disable or change the schedule of ghost cleaner?
Thanks in advance..

View 12 Replies View Related

Maintenance Cleanup Task……

Sep 14, 2007




SQL Server 2005 Standard Edition
Windows Server 2003.

I have created an €˜Backup Database Task€™ to create the database backups and checked the option €œCreate a subdirectory for each database€?.

When I add the €œMaintenance Cleanup Task€? I am not getting the similar option to clean the backup files in the subdirectory.

Any suggestion on how to solve this?

I can always uncheck the €œCreate a subdirectory for each database€? option to create the backup files in one location.

Thank you,
Smith

View 10 Replies View Related

Sysmail_mailitems Table Cleanup

Aug 22, 2007

sysmail_mailitems table in msdb stores data for emails sent using database mail. The DB was growing and I found sysmail_mailitems table is taking almost 85% space. What is the procedure to clean up data in this table. I did dirty way of removing FK's for the table, truncating data and re-establishing FK relationships.


Is there some way or settings somewhere to schedule cleanup on this table?

Thanks,

View 1 Replies View Related

Distribution Table Cleanup

Sep 15, 2006

Using SQL Server 2005. Replication working fine except the distribution table is continuely growing. Started to configure maintenance job (catagory: REPL-Distribution Cleanup) Any suggested steps that will not break the replication. Is there a SP available that will address my problem.

Thanks,

View 1 Replies View Related

Blocking On Something Like Ghost Cleanup?

Aug 23, 2007

I noticed that several connections were blocked by something called Ghost Cleanup (or something like that). I know what the cleanup does, but it often causes blocking for quite a while . . .

Anything I can do about it? SS2000.

Thanks,

Michael

View 1 Replies View Related

SQL 2012 :: Cleanup Of Differential And Log Backups

Oct 15, 2014

I am using Ola Hallegren's scripts to do backups. He uses @Cleanup Time to delete backups older than a certain number of hours. My situation is I do a full backup of a database on Sunday and then I have a few Differentials and then log backups for the rest of the week. When Sunday rolls around again and my full backup is finished, I would like to delete all the differential backups and log backups. Any way that I could accomplish this using Ola's scripts?

View 1 Replies View Related

How To Identify Unused DB Objects For Cleanup

Aug 28, 2013

I need to setup a Server Audit in sql 2012 standard edition. The goal is to identify all procs, views, tables, functions that are not being used anymore. What is the best way to setup a server audit to accomplish this? What events need to be captured at a minimum so I can delete all the unused crap in this database. This is a smaller, growing company.

View 2 Replies View Related

Merge Replication / Metadata Cleanup / Help!

Jan 8, 2007

At a client site we just had a customer away on holidays, came back and went to replicate and received the error. I understand why this would happen after the default 14 days, and the obvious fix to prevent it from happening in the future is to extend the period, however I am hoping someone can offer me assistance on the best way to correct the issue now

Error is:

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload).



The problem is we cannot lose the data that has been entered on this machine since the last replication. Writing scripts to manually save and pump this data will take days if not weeks (complex database). Is there any way to issue a command to cause a manual upload of the new information that is at the subscriber. Then I could just delete and recreate replication?



Any help or advice would be appreciated.

View 3 Replies View Related

Conflict Table Cleanup Interval

Nov 9, 2006

What replication parameter sets the interval in which data in the conflict tables is deleted in SQL Server 2000 SP4?

View 5 Replies View Related

Transact SQL :: Cleanup Suspect Databases

Sep 26, 2015

After cloning our production SQL 2012 server to make DEV environment, there is a bunch of databases in "Suspect" mode. This is because the DB's and Logs have got out of sync.I can clean up each DB with this script:

ALTER DATABASE DB_NAME SET EMERGENCY
GO
DBCC CHECKDB (DB_NAME)
GO
ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB (DB_NAME, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE DB_NAME SET MULTI_USER
GO

but this takes a long time replacing DB_NAME and executing for each DB.I tried putting it all into a script with variables, but I have done something wrong and it does not work. Also is there a way to just apply it to the suspect DB's?

USE master
Go
DECLARE @dbname sysname, @cmd varchar(1000), @cmd1 varchar(1000), @cmd2 varchar(1000), @cmd3 varchar(1000), @cmd4 varchar(1000)
DECLARE db_recovery_cursor CURSOR FOR
SELECT name from sysdatabases
OPEN db_recovery_cursor

[code]....

View 7 Replies View Related

I Hate The Maintenance CleanUp Task :=)

May 4, 2007

I have a backup database task that runs at 4 am.

I added a maintenance CleanUp task pointing to the right folder
I checked the include first-level subfolder box, and delete files based on the age of the file at task run time

I specified "delete files older than " 1 day


But the cleanup task is not working, I have to manually go a delete the files myself.

Am I missing something? should I run the maintenance task first?
and then the backup task?

View 10 Replies View Related

Retention-based Meta Data Cleanup

Apr 18, 2005

Hi

I am running a couple of sql 2000 SP3a servers with merge and snapshot replication. One server acting as publisher and distributor and the rest subscribers. On one of the server I have got the error below and have tried most of the suggestions by msdn. This server has not crashed ever before or any hardware problems. It has been running for a couple of months and no problems. This has not happened no any of the other servers. Any suggestions would be greatly appreciated as the only resolution I have left is to bring up a new instance, setup replication and see if this would resolve the issue. Stopping and starting of agents don't work.


[4/18/2005 11:59:20 AM]BRAAMPDC1ICAS2000.HO_Master: {call sp_MSgetversion }
[4/18/2005 11:59:20 AM]BRAAMPDC1ICAS2000.distribution: {call sp_MShelp_subscriber_info (N'BRAAMPDC1ICAS2000', N'EASTSRV3')}
Connecting to Subscriber 'EASTSRV3.ICASData'

Server: EASTSRV3
DBMS: Microsoft SQL Server
Version: 08.00.0760
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

[4/18/2005 11:59:27 AM]EASTSRV3.ICASData: {call sp_MSgetversion }
Percent Complete: 2
Connecting to Subscriber 'EASTSRV3'
Percent Complete: 3
Retrieving publication information
Percent Complete: 4
Retrieving subscription information
Percent Complete: 4
The merge process is cleaning up meta data in database 'HO_Master'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
The merge process is cleaning up meta data in database 'ICASData'.
The merge process could not perform retention-based meta data cleanup in database 'ICASData'.
Percent Complete: 0
The merge process could not perform retention-based meta data cleanup in database 'ICASData'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147199467
Message: The merge process could not perform retention-based meta data cleanup in database 'ICASData'.
Percent Complete: 0
Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_mergemetadataretentioncleanup(?, ?, ?)}
Percent Complete: 0
Category:SQLSERVER
Source: EASTSRV3
Number: 11
Message: General network error. Check your network documentation.

View 1 Replies View Related

SQL 2012 :: Maintenance Cleanup Task Not Cleaning Up?

Jul 8, 2015

I am running a maintenance cleanup task as shown below but despite the log saying "success" it is not successful as nothing is cleaned up.

Maintenance Cleanup Task (VPS20604)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 4 Weeks
Task start: 2015-07-08T16:14:24.
Task end: 2015-07-08T16:14:24.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''C:SQLBkp'',N'''',N''2015-06-10T16:14:24''
GO

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved