Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

问题重现：在订阅服务器上执行删出某行，同时在发布服务器上更新此行。发布服务器更新成功后会将命令传到订阅服务器，但因订阅服务器此行已经删除，不能如发布服务器一样更新成功，就会报错。
解决方法：1，找到丢失的行，在订阅上恢复回来。2，跳过命令的执行。


1，查看错误
SELECT DISTINCT xact_seqno
FROM dbo.MSrepl_errors
WHERE time >='2013-02-24'

--查看错误明细，分发库
SELECT mse.time,mse.error_text,CAST(SUBSTRING(command, 7, 8000) AS NVARCHAR(MAX)) command ,
	   ms.article,ma.name,ma.publisher_db,mh.comments,mse.xact_seqno,mse.error_code,mh.duration
FROM  dbo.MSrepl_errors mse
JOIN MSdistribution_history mh ON mse.id = mh.error_id
JOIN MSdistribution_agents ma ON mh.agent_id = ma.id
JOIN MSrepl_commands msc ON mse.xact_seqno = msc.xact_seqno
LEFT JOIN  MSarticles ms ON msc.article_id = ms.article_id
WHERE mse.time>='2013-07-18 14:00'


2,查看错误
 EXEC Sp_browsereplcmds 
  @xact_seqno_start = '0x00015F2B00002A26000300000000', 
  @xact_seqno_end = '0x00015F2B00002A26000300000000' 

3，解决数据冲突，若不能解决且数据不重要，可以删除命令的执行
DELETE a
FROM dbo.MSrepl_transactions a
WHERE xact_seqno  IN(0x00046FBE00002BFB000400000000)

DELETE a
FROM dbo.MSrepl_commands a
WHERE xact_seqno  IN(0x00046FBE00002BFB000400000000)

DELETE a
FROM dbo.MSrepl_errors a
WHERE xact_seqno = 0x00046FBE00002BFB000400000000

4，查看相关命令

SELECT TOP 50 msc.xact_seqno,CAST(SUBSTRING(command, 7, 8000) AS NVARCHAR(MAX))  --INTO #
  FROM dbo.MSrepl_commands msc(NOLOCK)
  JOIN MSrepl_transactions a(NOLOCK) ON msc.xact_seqno = a.xact_seqno
  WHERE entry_time>='2013-04-26' AND CAST(SUBSTRING(command, 7, 8000) AS NVARCHAR(MAX)) LIKE '%sp_MSupd_dboHandlerAllocateOrder%'

5,查询命令相关的表数据


CREATE TABLE #t(xact_seqno VARBINARY(16),originator_srvname VARCHAR(50),
originator_db VARCHAR(50),article_id int,type int,partial_command int,
hashkey int,originator_publication_id int,originator_db_version int,
originator_lsn VARBINARY(16),command VARCHAR(1000),command_id int)


SELECT  msc.xact_seqno INTO #
FROM dbo.MSrepl_commands msc(NOLOCK)
JOIN MSrepl_transactions a(NOLOCK) ON msc.xact_seqno = a.xact_seqno
WHERE entry_time>='2013-04-26 14:30' AND CAST(SUBSTRING(command, 7, 8000) AS NVARCHAR(MAX)) LIKE '%sp_MSupd_dboHandlerAllocateOrder%'


DECLARE PCUR 
CURSOR FOR
SELECT xact_seqno
FROM #

DECLARE @V1 VARBINARY(22),@V2 VARCHAR(50)
OPEN PCUR
FETCH NEXT  FROM PCUR  INTO @V1

WHILE @@FETCH_STATUS = 0

BEGIN
	SET @v2 =  master.dbo.fn_varbintohexstr(@V1)

	INSERT INTO #T
	EXEC Sp_browsereplcmds 
	  @xact_seqno_start =@v2, 
	  @xact_seqno_end =@v2

	FETCH NEXT  FROM PCUR  INTO @V1
END
CLOSE PCUR
DEALLOCATE PCUR

SELECT t.originator_db,ms.article,
SUBSTRING(command,CHARINDEX(',{',command)+2,CHARINDEX('},',command,2)-CHARINDEX(',{',command)-2) rowkey,
SUBSTRING(command,CHARINDEX(' [',command)+1,CHARINDEX('] ',command,2)+1-CHARINDEX(' [',command)) sp, t.xact_seqno
FROM #T t
JOIN distribution.dbo.MSarticles ms(NOLOCK) ON t.article_id = ms.article_id

Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

2008开始存错过程可以带表参数，不过限制只读。

Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

If connections exist on a database , and you attempt to detach a database with EXEC master.dbo.sp_detach_db @dbname = N'database_name' the detach will fail.

Best practise is to identify the connection sources and communicate with the users, and closing the connection gracefully. Communicating with the users is not always possible .

Instead of using the KILL command ,use this command to set the database into SINGLE_USER mode. The SINGLE_USER mode indicates for only one user to access the database. The ROLLBACK IMMEDIATE option terminates ,rolling back all transactions not completed and disconnecting all other connections immediately.

 

 

USE [master]
GO
ALTER DATABASE [database_name] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'database_name'
GO