SQLSERVER2000中SLEEPING连接过多
?
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()) ?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 '? '?<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()" /> ?<datasource name="test" jndi-name="jdbc/test" driver- 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
