Issues with removing replication

SQL Server 2005 Standard replicating to an identical server.

My issue:

When we go into the database to remove a large number of rows, it says we are unable to delete since the database is in replication. Is there a way to pause, or stop replication temporarily? I stopped the log reader but it still gave me the same error.

The only way I have found around this is to delete the publication and re-create it when I'm ready. Now though, I'm unable to delete the publication. SQL Server Management Studio will freeze if I either try to delete something from the UI or command line. This happens with objects in SQL Server as well as rows and such. It will just freeze and I have to kill the process.

Please Help!

Can this be caused by a long running transaction which is not completed yet?

This posting is provided AS IS with no warranties, and confers no rights.

|||i don't think so since i tried deleting a single row and it sat there for 16 minutes. i had to kill it since no one could connect to the database. but before this situation deleting a couple hundred rows would take about 1 second.|||

Can you try to connect to "master" using 'sa' and do sp_who (sp_who2) and see "who" is accessing that database?

This posting is provided AS IS with no warranties, and confers no rights.

|||i did
USE master
EXEC sp_who

i returned 37 processes. i don't know how to display it on the forum.|||

Are you trying to use TRUNCATE? This isn't allowed as indicated by the error message. You'll have to use explicit DELETE command. If this isn't what you were doing, the post the command you were executing and the error message you received.

|||If you want to remove replication, and you have limited resources on your box, try stopping sql server agent service first, then do the uninstall. Depending on how many rows you have in MSrepl_commands, it may take a while.|||

Before you post the result from sp_who2, can you check the "blkby" value where "DBName" is equal to the target database? I just try to understand if any process to that database is blocked by some other processes?

This posting is provided AS IS with no warranties, and confers no rights.

|||

if u want to remove replication what u should do is

(a) Press right button on Replicaiton

(b) Select Disable Publication and distribution

(c) Go step by step

you can remove replication and there by Distribution and publicaiton also.

Madhu

|||

Greg Y wrote:

Are you trying to use TRUNCATE? This isn't allowed as indicated by the error message. You'll have to use explicit DELETE command. If this isn't what you were doing, the post the command you were executing and the error message you received.

It

doesn't matter which command I try. And even if I try to delete

something from the GUI it fails. It's not limited to data either. If I

try to delete an object, or try to disable replication I get the same

result; SQL lockup.

|||

Madhu K Nair wrote:

if u want to remove replication what u should do is

(a) Press right button on Replicaiton

(b) Select Disable Publication and distribution

(c) Go step by step

you can remove replication and there by Distribution and publicaiton also.

Madhu

I've tried this. SQL locks up. This only happens on 1 server. I've tried uninstalling and re-installing sql server 2005. i still get the same result.|||

Did you try stopping the sql server agent service first like I mentioned above? I also mentioned you might have hardware constraints, how much memory/cpu/disk spindles do you have? Also what kind of replication are you trying to remove, merge or transactional? How many publications, how many rows exist in distribution.dbo.MSrepl_commands table, how many rows exist in [published db].dbo.MSmerge_contents?

|||

Greg Y wrote:

Did you try stopping the sql server agent service first like I mentioned above? I also mentioned you might have hardware constraints, how much memory/cpu/disk spindles do you have? Also what kind of replication are you trying to remove, merge or transactional? How many publications, how many rows exist in distribution.dbo.MSrepl_commands table, how many rows exist in [published db].dbo.MSmerge_contents?

i'm actually unable to try stopping the service until this weekend. it's our main production server so i can't interrupt service. the only hardware constraints we may run into is HDD read/write. we only have a pair of 15K SCSI drives in RAID1. we plan on upgrading to 8 drives in RAID10. but other than that the servers are 4 way Intel dual cores with 8 physical cores and 8 logical cores, and 16GB of RAM.

It's transactional replication with a single publication. i'm not sure how to find the number of rows in the locations you asked for though?|||

You can query the tables I mentioned above.

Also, I highly suggest running permon to debug OS performance bottleneck/issues. If publisher/distributor are on the same machine, and given you only have two disks, I will guess that disk might be bottlenecking. But you need to do some investigation on your part first, start with perfmon.

|||

Greg Y wrote:

You can query the tables I mentioned above.

Also, I highly suggest running permon to debug OS performance bottleneck/issues. If publisher/distributor are on the same machine, and given you only have two disks, I will guess that disk might be bottlenecking. But you need to do some investigation on your part first, start with perfmon.

We've done performance monitoring analysis for other reasons and the disks weren't even a bottleneck. They are the only part of the system that was really stressed, but no issues. We even had a consulting firm come in and spend a couple months scripting out a load test which went really well.

Is it possible that corrupt data in the database could be causing this issue?