Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Archive for August, 2011

Posted by rahmanagoro on August 12, 2011


I was working with SQL 2008 replication and on checking replication monitor, I noticed that on the distribution agent for one of the databases, it had a retrying command status. I further looked into it to see what the problem was, here I can see that the distribution agent had become stucked whilst trying to send commands to the subscriber. The first thing that came to my mind was, “what is happening with the subscriber ? ”

I ran the following on the subscriber

use master
GO
sp_who2 active

I was able to see that there is a blocking spid at the moment, which is coming from my distributor. So what must have happened here is that, as a snapshot was being delivered to the subscriber, objects such as stored procedures and functions were also being delivered as well, thereby causing a deadlock to occur.

To confirm this, I can the following

from distributor

Read the rest of this entry »

Posted in SQL replication, Tips | Leave a Comment »

Drop SQL server replication after a server name change

Posted by rahmanagoro on August 4, 2011


After spending some time brushing up my photography skills, its back to the real world of work, coming in to work I had a request from a developer to setup SQL server replication on a publisher with one subscriber, I have a routine of powershell scripts that I normally run to do this. After completing the steps, I noticed some errors, upon investigating it became apparent that the identity of the server isn’t what it is expected to be. The servername is different for some reason.

After running the select command to view the server name,  I find that its different from the server in which I have logged in from.

I get the error

SELECT @@SERVERNAMEsp_dropserver WRONGSERVER
Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 31
Cannot drop server WRONGSERVER because it is used as a Publisher in replication.

–Trying to drop SQL replication I get the following errors

 

exec master..sp_dropdistributor @no_checks = 1

Msg 208, Level 16, State 1, Procedure sp_MSrepl_dropsubscription, Line 103
Invalid object name 'dbo.syssubscriptions'.
Msg 266, Level 16, State 2, Procedure sp_MSrepl_dropsubscription, Line 103
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Procedure sp_dropsubscription, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Procedure sp_MSpublishdb, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Procedure sp_replicationdboption, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 266, Level 16, State 2, Procedure sp_dropdistributor, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

In this case, I can tell that there has been an exception within the execution of the stored procedures. What needs doing is for the replication markers on the databases to be removed so that replication can be dropped cleanly on the database.

Step 1

 

-- Remove the replication marker on the databases

EXEC master..sp_MSforeachdb '
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
    SELECT ''?''

 IF OBJECT_ID(''tempdb..#dbproperty'') is not null
 drop table #dbproperty

 create table #dbproperty (OptionName   varchar(20), CurrentSetting varchar(10))

 insert into #dbproperty
 exec sp_dboption ''?'',''published''

 IF EXISTS (Select * from #dbproperty where CurrentSetting = ''ON''  )
 exec sp_removedbreplication @dbname = ''?'', @type = ''tran''

 IF OBJECT_ID(''tempdb..#dbproperty'') is not null
 drop table #dbproperty

END
'

This will now drop SQL replication.


exec master..sp_dropdistributor @no_checks = 1

It should now work.

Change the servername now.

sp_dropserver WRONGSERVER

— Create new server name

sp_addserver ‘RIGHTSERVER’,local

Restart the SQL service on the publisher.

The correct new servername should now be reflected on the SQL instance.

Now proceed to setting up SQL replication.

 

Posted in Scripts, SQL 2008, SQL replication, Tips | 2 Comments »