SQLSERVER2000中SLEEPING联接过多_SQL

SQLSERVER2000中SLEEPING连接过多

1. ? 如果条件许可, ? 首先把iis和sql ? server分到两台服务器中, ? 这样可以避免两者互相影响, ? 也有利于查找直接的原因.(例如, ? 由于IIS工作缓慢, ? 可能会导致不断的向sql发请求, ? 这样看起来似乎就是sql ? server的问题)

2. ? 如果要查询是否连接没有释放引起的, ? 你可以用查询分析器连接到你的数据库服务器, ? 执行下面的代码:

select   *   from   master.dbo.sysprocesses where   spid> 50 and   waittype   =   0x0000 and   waittime   =   0 and   status   =   'sleeping ' and   last_batch   <   dateadd(minute,   -10,   getdate()) and   login_time   <   dateadd(minute,   -10,   getdate())

?
如果这样的进程很多, ? 则说明连接确实有很多连接没有释放(上面的查询查询出已经超过10分钟都没有做任何动作的连接)

3. ? 如果确实是连接没有释放的问题, ? 你可以硬行释放连接, ? 不一定要改程序. ? 在sql ? server中, ? 创建一个job, ? 每10分钟一次, ? 执行下面的代码来定时检查并释放掉空连接就可以了:

declare   hcforeach   cursor   global for select   'kill   '   +   rtrim(spid)   from   master.dbo.sysprocesses where   spid> 50 and   waittype   =   0x0000 and   waittime   =   0 and   status   =   'sleeping ' and   last_batch   <   dateadd(minute,   -60,   getdate()) and   login_time   <   dateadd(minute,   -60,   getdate()) exec   sp_msforeach_worker   '? '

DBCP配置：

<Resource name="jdbc/SqlServer" auth="Container"  			  type="javax.sql.DataSource"               driverClassName="com.microsoft.jdbc.sqlserver.SQLServerDriver"  			  url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=dhdd_db"  			  username="sa"               password="sa"               maxActive="2"               maxIdle="1"                maxWait="10"              removeAbandoned="true"              removeAbandonedTimeout="20"              logAbandoned="true"????????????? validationQuery="select getDate()"            />

Apusic配置：

<datasource name="test"      jndi-name="jdbc/test"      driver-class="oracle.jdbc.OracleDriver"     url="jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))(LOAD_BALANCE = yes)(FAILOVER = ON)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = RAC_DB)(FAILOVER_MODE=(TYPE = SELECT)(METHOD = BASIC)(RETIRES = 20)(DELAY = 15))))"      min-spare-connections="10"      max-spare-connections="35"      max-connections="75"   >     <property name="user" value="scott"/>        <property name="password" value="tiger"/>     <property name="test-before-reused" value="false"/>     <property name="test-command" value="select 1 from dual"/>     <remote-acl>       <user>admin</user>       <user>j2ee</user>     </remote-acl>   </datasource>

List the queries running/blocking on SQL Server2011/12/27 12:41:57 | 阅读4次 There are various management views built into the product. On SQL 2000 you'd use sysprocesses. On SQL 2K5 there are more views like sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests.There are also procedures like sp_who that leverage these views. In 2K5 Management Studio you also get Activity Monitor.This will show you the longest running SPIDs on a SQL 2000 server:select p.spid , right(convert(varchar, dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 121), 12) as 'batch_duration' , P.program_name , P.hostname , P.loginame from master.dbo.sysprocesses P where P.spid > 50 and P.status not in ('background', 'sleeping') and P.cmd not in ('AWAITING COMMAND' ,'MIRROR HANDLER' ,'LAZY WRITER' ,'CHECKPOINT SLEEP' ,'RA MANAGER') order by batch_duration desc If you need to see the SQL running for a given spid from the results, use something like this:declare @spid int , @stmt_start int , @stmt_end int , @sql_handle binary(20) set @spid = XXX -- Fill this in select top 1 @sql_handle = sql_handle , @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end , @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end from master.dbo.sysprocesses where spid = @spid order by ecid SELECT SUBSTRING( text, COALESCE(NULLIF(@stmt_start, 0), 1), CASE @stmt_end WHEN -1 THEN DATALENGTH(text) ELSE (@stmt_end - @stmt_start) END ) FROM ::fn_get_sql(@sql_handle) here is a query that will show any queries that are blocking. I am not entirely sure if it will just show slow queries:SELECT p.spid ,convert(char(12), d.name) db_name , program_name , convert(char(12), l.name) login_name , convert(char(12), hostname) hostname , cmd , p.status , p.blocked , login_time , last_batch , p.spid FROM master..sysprocesses p JOIN master..sysdatabases d ON p.dbid = d.dbid JOIN master..syslogins l ON p.sid = l.sid WHERE p.blocked = 0 AND EXISTS ( SELECT 1 FROM master..sysprocesses p2 WHERE p2.blocked = p.spid ) if you're running SQL 2005 or 2008, you could use the DMV's to find this...SELECT * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) more about sys.dm_exec_requests more about sys.dm_exec_sql_text http://www.haogongju.net/art/1168212