sqlserver 链接不上

[moqijunnu]

没有一个工作线程拾取了分配给节点 0 上的进程的新查询。查询被阻塞或长时间运行可能导致出现此情况，并且可能会延长客户端响应时间。请使用 "最大工作线程数(max worker threads)" 配置选项增加允许的线程数，或者优化当前正运行的查询。

sql连不上，服务没有停止。不知道具体什么原因，查日志，提示以上的问题。请问是什么问题？

[吉普赛的歌]

-- =============================================
-- 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

执行了看下。

[belsai]

发生死锁了吧，跟踪一下或查询一下锁情况

[appo_li]

查看死锁具体产生的情况和来源 然后干掉 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)

[泳智——高洋]

你最后是怎么解决的，你用的那个版本数据库