SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 deleting replication
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

rohans
Posting Yak Master

Jamaica
194 Posts

Posted - 01/14/2004 :  16:28:03  Show Profile  Visit rohans's Homepage  Reply with Quote
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

USA
992 Posts

Posted - 01/14/2004 :  16:38:39  Show Profile  Reply with Quote
Jamaica ... what I wouldn't give to be there right now. I'm in Madison, WI ...

Jonathan
Gaming will never be the same
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/14/2004 :  16:41:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

rohans
Posting Yak Master

Jamaica
194 Posts

Posted - 01/15/2004 :  09:43:26  Show Profile  Visit rohans's Homepage  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/15/2004 :  12:17:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

rohans
Posting Yak Master

Jamaica
194 Posts

Posted - 01/15/2004 :  12:28:49  Show Profile  Visit rohans's Homepage  Reply with Quote
Nothing there. I had disabled a replication earlier this morning but I still see them at the subscriber.

All help appreciated.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/15/2004 :  12:32:23  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 01/15/2004 12:33:03
Go to Top of Page

rohans
Posting Yak Master

Jamaica
194 Posts

Posted - 01/23/2004 :  16:29:04  Show Profile  Visit rohans's Homepage  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/23/2004 :  16:32:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
"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

Edited by - tkizer on 01/23/2004 16:33:25
Go to Top of Page

rohans
Posting Yak Master

Jamaica
194 Posts

Posted - 01/27/2004 :  09:22:35  Show Profile  Visit rohans's Homepage  Reply with Quote
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.
Go to Top of Page

rohans
Posting Yak Master

Jamaica
194 Posts

Posted - 01/27/2004 :  11:53:35  Show Profile  Visit rohans's Homepage  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/27/2004 :  12:26:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

rohans
Posting Yak Master

Jamaica
194 Posts

Posted - 01/27/2004 :  14:05:54  Show Profile  Visit rohans's Homepage  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/27/2004 :  14:09:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

rohans
Posting Yak Master

Jamaica
194 Posts

Posted - 01/27/2004 :  14:35:47  Show Profile  Visit rohans's Homepage  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/27/2004 :  14:41:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

rohans
Posting Yak Master

Jamaica
194 Posts

Posted - 01/28/2004 :  14:17:32  Show Profile  Visit rohans's Homepage  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/28/2004 :  14:18:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

rohans
Posting Yak Master

Jamaica
194 Posts

Posted - 01/28/2004 :  14:25:10  Show Profile  Visit rohans's Homepage  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 01/28/2004 :  14:26:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
Stop the replication services. You could also set the event log so that it overwrites itself as needed.

Tara
Go to Top of Page

rohans
Posting Yak Master

Jamaica
194 Posts

Posted - 01/28/2004 :  15:11:31  Show Profile  Visit rohans's Homepage  Reply with Quote
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.

Edited by - rohans on 01/28/2004 15:13:07
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000