sqlserver 消息 1912 诡异问题

[liu12qw]

sqlserver里是这样操作的，先删除索引，在加索引，删除操作很顺利,但是建索引报错："
消息 1912，级别 16，状态 1，第 3 行无法对 表 'dbo.EPI_JZJL' 继续执行索引 DDL 操作，因为它与已经对该对象进行的另一个并发操作冲突。该并发操作可能是对同一个对象的联机索引操作，也可能是另一个移动索引页(如DBCC SHRINKFILE)的并发操作。"
下面是语句

USE [SIPIS]
GO
/****** Object: Index [CHILDINFO_PART_PK] Script Date: 07/13/2016 17:15:16 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[EPI_CHILDINFO_PART]') AND name = N'CHILDINFO_PART_PK')
DROP INDEX [CHILDINFO_PART_PK] ON [dbo].[EPI_CHILDINFO_PART] WITH ( ONLINE = OFF )
GO
USE [SIPIS]
GO
/****** Object: Index [CHILDINFO_PART_PK] Script Date: 07/13/2016 17:15:16 ******/
CREATE UNIQUE NONCLUSTERED INDEX [CHILDINFO_PART_PK] ON [dbo].EPI_CHILDINFO_PARTINCLUDE
(FSTATUSTIME, FCREATEDEPT, FCREATEUSER, FIDCardType, FResidenceType31)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
USE [SIPIS]
GO
/****** Object: Index [EPI_CHILD_FCHILD] Script Date: 07/13/2016 17:17:37 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[EPI_CHILDINFO]') AND name = N'EPI_CHILD_FCHILD')
DROP INDEX [EPI_CHILD_FCHILD] ON [dbo].[EPI_CHILDINFO] WITH ( ONLINE = OFF )
GO
USE [SIPIS]
GO
/****** Object: Index [EPI_CHILD_FCHILD] Script Date: 07/13/2016 17:17:38 ******/
CREATE UNIQUE NONCLUSTERED INDEX [EPI_CHILD_FCHILD] ON [dbo].EPI_CHILDINFOINCLUDE(
IMUNO, NAME, BIRTH, SEX, REGTYPE, DWELLADDR, COMADDR, FATHERNAME, MOTHERNAME, MOBPHONE, OTHERPHONE, CARDDATE, BOOKINGDATE, ALLERGY, TABU, STATUS, TRANSTIME, REMARK, UPDATETIME, REGADDR, CID, STATIONCODE, BIRTHNO, ServerUpdatetime
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

表的数据量有点多，一亿条数据。。。

下面是sp_who

问题很可能是进程29，我看磁盘一直在使用3M/秒

[唐诗三百首]

新建一个查询窗口,查看是否有进程阻塞,

select spid,blocked from sys.sysprocesses where spid=[进程ID]

[卖水果的net]

等一下再试吧，看样子是一个收缩任务在正运行，等他跑完了，你再执行创建索引的语句；

[以学习为目的]

你后台有任务在运行

[唐诗三百首]

重建索引时, 用SQL Profiler工具跟踪一下是否有Deadlock发生,如有,则找到对应进程,等其运行结束或强制结束后再创建索引.

[liu12qw]

引用 1楼卖水果的net 的回复:

等一下再试吧，看样子是一个收缩任务在正运行，等他跑完了，你再执行创建索引的语句；

都三个小时了，还没好，坑。

[liu12qw]

引用 3 楼 ap0405140 的回复:

重建索引时, 用SQL Profiler工具跟踪一下是否有Deadlock发生,如有,则找到对应进程,等其运行结束或强制结束后再创建索引.

试过了，没有死锁，好想重启数据库，还有别的号办法么？

[liu12qw]

引用 2 楼 galenkeny 的回复:

你后台有任务在运行

5个小时了。。

[以学习为目的]

 declare @spid int,@bl int  
 DECLARE s_cur CURSOR FOR   select  0 ,blocked   from (select * from sysprocesses where  blocked>0 ) a  
   where not exists(select * from (select * from sysprocesses where  blocked>0 ) b   where a.blocked=spid)   

   union 
select spid,blocked from sysprocesses where  blocked>0   OPEN s_cur   FETCH NEXT FROM s_cur INTO @spid,@bl   WHILE @@FETCH_STATUS = 0  
    begin   if @spid =0             
      select ' 引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + ' 进程号, 其执行的SQL 语法如下'   else              
       select ' 进程号SPID ：'+ CAST(@spid AS VARCHAR(10))+ ' 被' + ' 进程号SPID ：'+ CAST(@bl AS VARCHAR(10)) +' 阻塞, 其当前进程执行的SQL 语法如下' 
         DBCC INPUTBUFFER (@bl )   FETCH NEXT FROM s_cur INTO @spid,@bl   end   CLOSE s_cur  
          DEALLOCATE s_cur    
       

--  kill  @spid
   

[以学习为目的]

先看有没有死锁，kill掉。不行重启下sqlserver