2013年2月 的存档
2013二月23

应用复制的命令时在订阅服务器上找不到该行

sql server 评论关闭

问题重现:在订阅服务器上执行删出某行,同时在发布服务器上更新此行。发布服务器更新成功后会将命令传到订阅服务器,但因订阅服务器此行已经删除,不能如发布服务器一样更新成功,就会报错。
解决方法: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
2013二月20

存储过程表变量

sql server 评论关闭

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

CREATE TYPE [dbo].[MyTable] AS TABLE
02.(
03.[id] [int],
04.[value] [nvarchar](128)
05.)
06.GO
07. 
08.CREATE PROCEDURE [dbo].[Capitalize]
09.(
10.@myTable [dbo].[MyTable] READONLY
11.)
12.AS
13.BEGIN
14.DECLARE @myOutput [dbo].[MyTable]
15. 
16.INSERT INTO @myOutput([id], [value])
17.SELECT [id], UPPER([value]) FROM @myTable
18. 
19.SELECT [id], [value] FROM @myOutput
20.END
21.GO
22. 
23.DECLARE @myTable [dbo].[MyTable]
24.DECLARE @myOutput [dbo].[MyTable]
25. 
26.INSERT INTO @myTable([id], [value])
27.VALUES
28.(1, 'one'),
29.(2, 'two'),
30.(3, 'three')
31. 
32.INSERT INTO @myOutput([id], [value])
33.EXEC [dbo].[Capitalize] @myTable
34. 
35.SELECT * from @myOutput
2013二月16

Drop Connections with ROLLBACK IMMEDIATE to allow a Detach database

sql server 评论关闭
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
2013二月1

网站连接数查看

IIS 评论关闭

一、运行–>输入“perfmon.msc”

二、在“系统监视器”图表区域里点击右键,然后点“添加计数器” 
三、在“添加计数器”窗口,“性能对象”选择Web Service,“从列表选择计数器”选中Current Connection,“从列表选择实例”选中你要统计的站点,最后点击“添加”按钮