deleting replication - 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 2000 Forums

SQL Server Administration (2000)

deleting replication

Author

Topic

rohans
Posting Yak Master

194 Posts

Posted - 2004-01-14 : 16:28:03

How do I delete or remove a subscription. I can't seem to get it off the subscriber.

All help appreciated.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2004-01-14 : 16:38:39

Jamaica ... what I wouldn't give to be there right now. I'm in Madison, WI ...

Jonathan
Gaming will never be the same

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-14 : 16:41:59

Right click on the publication, go to properties then to subscriptions. Click Delete. This is the EM way. For the QA way, generate the delete statement from EM.

Tara

rohans
Posting Yak Master

194 Posts

Posted - 2004-01-15 : 09:43:26

I not seeing the property option when I right click. The two subscription that I see have a yellow database symbol (a cylinder) with a green curved arrow at the top. This is from the subscriber, in EM at the following location 'databasename\replication\subscriptions'. They are both push subscriptions.

All help appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 12:17:21

Read my post again. Go to the publication, not the subscriber. Once you are at the publication, right click on them (they have a book icon) and go to properties.

Tara

rohans
Posting Yak Master

194 Posts

Posted - 2004-01-15 : 12:28:49

Nothing there. I had disabled a replication earlier this morning but I still see them at the subscriber.

All help appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-15 : 12:32:23

It sounds like replication was deleted improperly. You'll have to delete it using the replication stored procedures. Here is the script that I use for the delete for one of my publications:

use [GT]
GO

-- Dropping the transactional subscription
exec sp_dropsubscription @publication = N'GT_Reports', @article = N'all', @subscriber = N'SDDEVSQL3\GTW', @destination_db = N'GT_Reports'
GO

-- Dropping the transactional articles
exec sp_dropsubscription @publication = N'GT_Reports', @article = N'ASSETG_USERG_ASSOC', @subscriber = N'all', @destination_db = N'all'
exec sp_droparticle @publication = N'GT_Reports', @article = N'ASSETG_USERG_ASSOC', @force_invalidate_snapshot = 1
GO

exec sp_dropsubscription @publication = N'GT_Reports', @article = N'ASSETGROUP', @subscriber = N'all', @destination_db = N'all'
exec sp_droparticle @publication = N'GT_Reports', @article = N'ASSETGROUP', @force_invalidate_snapshot = 1
GO

-- Dropping the transactional publication
exec sp_droppublication @publication = N'GT_Reports'
GO

Take a look at BOL for information on these stored procedures.

Tara

rohans
Posting Yak Master

194 Posts

Posted - 2004-01-23 : 16:29:04

I have been trying at deleting the replications but not much luck. What does the N infront the literal mean?
eg @publication=N'GT_Reports'

All help appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-23 : 16:32:37

"Unicode constants are specified with a leading N: N'A Unicode string'."

You can remove it though as it isn't needed. I generated the script from the replication wizard.

Here's a SQL Server 2000 article from MS that shows how to manually delete replication.

http://support.microsoft.com/default.aspx?scid=kb;en-us;324401

Tara

rohans
Posting Yak Master

194 Posts

Posted - 2004-01-27 : 09:22:35

The replication that I am trying to delet is filling up my application log on the server very quickly. I followed the instructions at the microsoft link but it tells me that the publication does not exist But I am seeing it from Enterprise manager. How can I get rid of the publication by force. I should tell you that the publication DB is the same as the distribution.

exec sp_dropsubscription @publication=N'Pubblication_name:DB_name', @article=N'all',@subscriber=N'all',@destination_db=N'all';

All help appreciated.

rohans
Posting Yak Master

194 Posts

Posted - 2004-01-27 : 11:53:35

Seems the name was wrong. Now I have a different error whe I use this
exec sp_dropsubscription @publication='Pub_name', @article='all', @subscriber='all', @destination_db='all';

error msg--->>
Server: Msg 823, Level 24, State 1, Procedure sp_changesubstatus, Line 424
I/O error 38(Reached end of file.) detected during read of BUF pointer = 0x11b70f40, page ptr = 0x4423a000, pageid = (0x1:0xc7f), dbid = 10, status = 0x801, file = C:\MSSQL7\data\db_name.mdf.

I want to run DBCC CHECKDB but it say I should use single user mode so I did that by using the command prompt to run "sqlservr.exe -c -m"
However I do not know where I should type the DBCC CHECKDB as I cannot get into query analyser and the command prompt is running the SQL SERVER so it is not free for my typing.

Need the help badly.

All help appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 12:26:24

sqlservr.exe -c -m means that you are running master in single user mode. Why do you need to do this? You do not need to do this in order to run DBCC CHECKDB on master (you do for certain repair levels though). But anyway, yes you can use Query Analyzer or Enterprise Manager even though you are running SQL Server for the command prompt. But you have to be the first one to connect so that you are the single user in. So if someone else connected, you'll have to wait til they disconnect. Or try starting it again through the command prompt and be fast trying to connect in QA.

And why did you name the publication and distribution the same? Typically, people use distribution as the name of distribution database.

Tara

rohans
Posting Yak Master

194 Posts

Posted - 2004-01-27 : 14:05:54

When I try this statement
dbcc checkdb ('HS2000CS_replica','REPAIR__FAST')

I get this error>>>>
Server: Msg 7919, Level 16, State 2, Line 2
Repair statement not processed. Database needs to be in single user mode.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

All help appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 14:09:59

But why are you running REPAIR_FAST option? Why is this needed? Run DBCC CHECKDB(HS2000CS_replica) without repair option. And sqlserver -c -m is for master database being in single user mode. It is not for HS2000CS_replica. Use ALTER DATABASE to put that database into single user mode. But you don't need REPAIR_FAST option if the database isn't corrupt. So run DBCC CHECKDB without repair option first. Then if it is corrupt, run repair option.

Also, what is the point of DBCC CHECKDB here? The fact that you can't delete replication doesn't mean that the database is corrupt. It means that someone has improperly deleted some replication rows causing the replication deletion to fail.

Tara

rohans
Posting Yak Master

194 Posts

Posted - 2004-01-27 : 14:35:47

So we continue from the I/O message 823. According to microsoft it is usually a disk error but that is hard for me to check as the box is a netfinity card running on an AS/400 (iSeries).
The reason for the running DBCC CHECKDB came from the following ideas at microsoft
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_2a0j.asp

All help appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-27 : 14:41:27

You've got SQL Server running on an AS/400? With a Windows partition?

Anyway, it sounds like you've got a corrupt database now. Run DBCC CHECKDB with the repair option after you have put the database in single user mode using ALTER DATABASE (do not do the sqlserver.exe way as that is for master only).

I would contact your AS/400 server administrator to see what disk problems there are. You should do this before trying to fix the database because if there is a disk problem, then you database might become corrupt again anyway.

Tara

rohans
Posting Yak Master

194 Posts

Posted - 2004-01-28 : 14:17:32

If I delete the subscription database will that stop the application log on the distributor/publication server from filling up with replication events.

All help appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-28 : 14:18:55

I doubt it. As a matter of fact, if you do that, you'll probably see more events as it will be filling up with errors.

Tara

rohans
Posting Yak Master

194 Posts

Posted - 2004-01-28 : 14:25:10

I need to stop the log from filling up. What is the quickest way to stop the replication events until I get them deleted properly?

All help appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-28 : 14:26:56

Stop the replication services. You could also set the event log so that it overwrites itself as needed.

Tara

rohans
Posting Yak Master

194 Posts

Posted - 2004-01-28 : 15:11:31

When it is stopped the replication monitor has a white X in a red circle on the monitor icon?
That is the case now but still I see event's in the log that implying that the replication is still running.

If not, where do I stop it from?

All help appreciated.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources