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