数据库被卡住一天了，停止数据库服务停不了，kill进程提示事务回滚0%

[mabailin]

被这个语句卡住了，这是什么东西

 SELECT
log.name 

 AS [Name],
log.principal_id AS [ID],
ISNULL(ak.name 

,N'') AS [AsymmetricKey],
ISNULL(cert.name 

,N'') AS [Certificate],
ISNULL(c.name 

,N'') AS [Credential],
CASE WHEN N'U' = log.type THEN 0 WHEN N'G' = log.type THEN 1 WHEN N'S' = log.type THEN 2 WHEN N'C' = log.type THEN 3 WHEN N'K' = log.type THEN 4 END AS [LoginType]
FROM
sys.server_principals AS log
LEFT OUTER JOIN master.sys.asymmetric_keys AS ak ON ak.sid = log.sid
LEFT OUTER JOIN master.sys.certificates AS cert ON cert.sid = log.sid
LEFT OUTER JOIN sys.credentials AS c ON c.credential_id = log.credential_id
WHERE
(log.type in ('U', 'G', 'S', 'C', 'K') AND log.principal_id not between 101 and 255 AND log.name 

 <> N'##MS_AgentSigningCertificate##')
ORDER BY
[Name] ASC 

[薛定谔的DBA]

确定是被这个卡住？？这应该不会啊，再找找

[日月路明]

可能不是这个语句卡住了，而是数据库已经卡了，执行这条语句，该语句在等待事务，造成的假象

[日月路明]

可以用sp_lock看看有哪些事务 kill spID杀掉 如果不清楚，把所有进程都kill掉

[吉普赛的歌]

SELECT [Spid] = session_Id, ecid, [Database] = DB_NAME(sp.dbid),
 [User] = nt_username, [Status] = er.status, 
 [Wait] = wait_type, 
 [Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2, (CASE WHEN er.statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) 
                      * 2 ELSE er.statement_end_offset END - er.statement_start_offset) / 2),
                       [Parent Query] = qt.text, 
                       Program = program_name, Hostname, 
                       nt_domain, start_time
FROM    
     sys.dm_exec_requests er INNER JOIN  sys.sysprocesses sp ON er.session_id = sp.spid 
     CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE     session_Id > 50 /* Ignore system spids.*/ AND session_Id NOT IN (@@SPID)

先执行了看下具体还有哪些语句在运行

[OwenZeng_DBA]

http://blog.csdn.net/z10843087/article/details/76040459 参考我博客中的语句，查看下真正阻塞的源头吧

[Q315054403]

"停止数据库服务停不了" 重启OS吧

[文盲老顾]

select spid,kpid,blocked,s.text,waittime,cpu,sp.dbid,cmd,db_name(sp.dbid) as dbname,lastwaittype,waittype,waitresource,status from sys.sysprocesses sp outer apply sys.dm_exec_sql_text(sp.sql_handle) s where blocked>0 and spid<>blocked order by cmd,1

用这个语句看看到底哪个进程卡的？

[xiaoxiangqing]

如果是这样的话，sql重新启动，估计还简单些