SELECT 
db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count

FROM sys.dm_db_index_physical_stats(DB_ID(N'db_name'), OBJECT_ID(N'table_name'), NULL, NULL , 'DETAILED');

在创建索引，数据库恢复等耗时操作时，可通过以下方法查看剩余时间

1. SELECT
2.     d.PERCENT_COMPLETE AS [%Complete],
3.     d.TOTAL_ELAPSED_TIME/60000 AS ElapsedTimeMin,
4.     d.ESTIMATED_COMPLETION_TIME/60000   AS TimeRemainingMin,
5.     d.TOTAL_ELAPSED_TIME*0.00000024 AS ElapsedTimeHours,
6.     d.ESTIMATED_COMPLETION_TIME*0.00000024  AS TimeRemainingHours,
7.     s.text AS Command
8. FROM    sys.dm_exec_requests d
9. CROSS APPLY sys.dm_exec_sql_text(d.sql_handle)as s
10. WHERE  d.COMMAND LIKE 'RESTORE DATABASE%'
11. ORDER   BY 2 desc, 3 DESC    

12. 可统计为以下命令完成的工作的百分比：

    • ALTER INDEX REORGANIZE
       
    • AUTO_SHRINK 选项（带 ALTER DATABASE）
       
    • BACKUP DATABASE
       
    • CREATE INDEX
       
    • DBCC CHECKDB
       
    • DBCC CHECKFILEGROUP
       
    • DBCC CHECKTABLE
       
    • DBCC INDEXDEFRAG
       
    • DBCC SHRINKDATABASE
       
    • DBCC SHRINKFILE
       
    • KILL (Transact-SQL)
       
    • RESTORE DATABASE,
       
    • UPDATE STATISTICS.

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

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

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

SELECT  DATABASEPROPERTYEX('mydb','version'),SERVERPROPERTY('Edition') [已安装产品版本], SERVERPROPERTY('ProductLevel') [SQL Server 实例的版本级别],SERVERPROPERTY('ProductVersion') [SQL Server 实例的版本]