沒有一個作業執行緒拾取了分配給節點 0 上的行程的新查詢。查詢被阻塞或長時間運行可能導致出現此情況，并且可能會延長客戶端回應時間。請使用 "最大作業執行緒數(max worker threads)" 配置選項增加允許的執行緒數，或者優化當前正運行的查詢。
sql連不上，服務沒有停止。不知道具體什么原因，查日志，提示以上的問題。請問是什么問題？
uj5u.com熱心網友回復：
發生死鎖了吧，跟蹤一下或查詢一下鎖情況uj5u.com熱心網友回復：
-- =============================================
-- Author: yng
-- Create date: 2014-11-18
-- Description: 阻塞預警
-- =============================================
CREATE PROCEDURE [dbo].[Proc_DBA_BlockingWarning]
@BlockingWarning INT = 100 --被阻塞的會話數大于@BlockingWarning就預警
AS
BEGIN
SET NOCOUNT ON
--1. 定義表變數，并將阻塞和被阻塞的資料放入表變數
DECLARE @t TABLE (
SPID SMALLINT,
DBName NVARCHAR(128),
Remark NCHAR(3),
[ProgramName] nchar(128),
[LoginName] nchar(128),
HostName nchar(128),
[Status] nchar(30),
BlockedBy SMALLINT,
LoginTime DATETIME,
QUERY nvarchar(max)
)
INSERT INTO @t (
SPID,
DBName,
Remark,
[ProgramName],
[LoginName],
HostName,
[Status],
BlockedBy,
LoginTime,
QUERY
)
SELECT
SPID=p.spid,
DBName = convert(VARCHAR(20),d.name),
Remark = CASE WHEN p.blocked>0 THEN '被阻塞' else '阻塞源' end,
ProgramName = program_name,
LoginName = convert(CHAR(20),l.name),
HostName = convert(CHAR(20),hostname),
Status = p.status,
BlockedBy = p.blocked,
LoginTime = login_time,
QUERY = TEXT
FROM MASTER.dbo.sysprocesses p
LEFT JOIN MASTER.dbo.sysdatabases d
ON p.dbid = d.dbid
LEFT JOIN MASTER.dbo.syslogins l
ON p.sid = l.sid
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE (p.blocked = 0
AND EXISTS (SELECT 1
FROM MASTER.dbo.sysprocesses p1
WHERE p1.blocked = p.spid))
OR (p.blocked>0)
--2. 如果被阻塞的數量大于設定引數，則
DECLARE @Warning INT
IF (SELECT COUNT(1) FROM @t WHERE BlockedBy>0)>=@BlockingWarning
BEGIN
SET @Warning = 1
END
ELSE
BEGIN
SET @Warning = 0
END
SELECT @Warning AS Warning,
SPID,
DBName,
Remark,
[ProgramName],
[LoginName],
HostName,
[Status],
BlockedBy,
LoginTime,
QUERY
FROM @t
SET NOCOUNT OFF
END
GO
執行了看下。
uj5u.com熱心網友回復：
查看死鎖具體產生的情況和來源然后干掉
create proc _proc_查看死鎖及ip
as
--1.新建存盤程序
--create proc prtest
--@spid int
--as
--dbcc inputbuffer (@spid)
--go
--2.將結果保存到臨時變數#tmp
SELECT [Session ID] AS 會話ID ,
[Login] AS 用戶名 ,
[Database] AS 資料庫 ,
[Task State] AS 狀態 ,
[Command] AS 命令 ,
[Application] AS 應用軟體 ,
[Wait Time (ms)] AS 等待時間 ,
[Wait Type] AS 等待型別 ,
[Host Name] AS 客戶機名 ,
[Net Address] AS IP地址 INTO #tmp
FROM ( SELECT [Session ID] = s.session_id ,
[User Process] = CONVERT(CHAR(1), s.is_user_process) ,
[Login] = s.login_name ,
[Database] = ISNULL(DB_NAME(p.dbid), N'') ,
[Task State] = ISNULL(t.task_state, N'') ,
[Command] = ISNULL(r.command, N'') ,
[Application] = ISNULL(s.program_name, N'') ,
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0) ,
[Wait Type] = ISNULL(w.wait_type, N'') ,
[Wait Resource] = ISNULL(w.resource_description, N'') ,
[Blocked By] = ISNULL(CONVERT (VARCHAR, w.blocking_session_id),
'') ,
[Head Blocker] = CASE
WHEN r2.session_id IS NOT NULL
AND ( r.blocking_session_id = 0
OR r.session_id IS NULL
) THEN '1'
ELSE ''
END ,
[Total CPU (ms)] = s.cpu_time ,
[Total Physical I/O (MB)] = ( s.reads + s.writes ) * 8
/ 1024 ,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024 ,
[Open Transactions] = ISNULL(r.open_transaction_count, 0) ,
[Login Time] = s.login_time ,
[Last Request Start Time] = s.last_request_start_time ,
[Host Name] = ISNULL(s.host_name, N'') ,
[Net Address] = ISNULL(c.client_net_address, N'') ,
[Execution Context ID] = ISNULL(t.exec_context_id, 0) ,
[Request ID] = ISNULL(r.request_id, 0) ,
[Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_connections c ON ( s.session_id = c.session_id )
LEFT OUTER JOIN sys.dm_exec_requests r ON ( s.session_id = r.session_id )
LEFT OUTER JOIN sys.dm_os_tasks t ON ( r.session_id = t.session_id
AND r.request_id = t.request_id
)
LEFT OUTER JOIN (
SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC ) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON ( t.task_address = w.waiting_task_address )
AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON ( s.session_id = r2.blocking_session_id )
LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON ( g.group_id = s.group_id )
LEFT OUTER JOIN sys.sysprocesses p ON ( s.session_id = p.spid )
) t
WHERE t.Command IN ( 'SELECT', 'UPDATE', 'DELETE' )
--3.創建保存結果的臨時表
CREATE TABLE #jttest(
[會話ID] [smallint] NOT NULL,
[用戶名] [nvarchar](128) NOT NULL,
[資料庫] [nvarchar](128) NOT NULL,
[狀態] [nvarchar](60) NOT NULL,
[命令] [nvarchar](16) NOT NULL,
[應用軟體] [nvarchar](128) NOT NULL,
[等待時間] [bigint] NOT NULL,
[等待型別] [nvarchar](60) NOT NULL,
[客戶機名] [nvarchar](128) NOT NULL,
[IP地址] [varchar](48) NOT NULL,
[TSQL] [varchar](4000) NULL
)
--4.將#tmp中值匯入到臨時表變數#jttest
INSERT INTO #jttest
([會話ID]
,[用戶名]
,[資料庫]
,[狀態]
,[命令]
,[應用軟體]
,[等待時間]
,[等待型別]
,[客戶機名]
,[IP地址]
)
SELECT
[會話ID]
,[用戶名]
,[資料庫]
,[狀態]
,[命令]
,[應用軟體]
,[等待時間]
,[等待型別]
,[客戶機名]
,[IP地址]
FROM #tmp
--5.獲得每個spid對應的TSQL陳述句
create table #tmp01(
EventType nvarchar(100),
Parameters Int,
EventInfo nvarchar(max)
)
declare @cursid int
declare cur cursor for select [會話ID] from #jttest
open cur
fetch next from cur into @cursid
while @@FETCH_STATUS=0
begin
insert into #tmp01 exec prtest @cursid
UPDATE #jttest SET TSQL=(SELECT EventInfo FROM #tmp01 )
WHERE 會話ID=@cursid
truncate table #tmp01
fetch next from cur into @cursid
end
close cur
deallocate cur
DROP TABLE #tmp01
--6.查看最終結果
SELECT * FROM #jttest
DROP TABLE #tmp
DROP TABLE #jttest
--dbcc inputbuffer (896)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/8401.html
標籤：基礎類
下一篇：一個從SQL SERVER2014中分離的資料庫，附加到一個SQL SERVER2012中時提示錯誤，如何在2014中分離此資料庫時可以設定兼容2012