l(convert(char(5),blocked),'0') When'0'Then' .'
Elseisnull(convert(char(5),blocked),'0') ENDBlkBy,BlockedSQLBuffer
,loginame Login
,db_name(dbid) DBName,convert(varchar,cpu) CPUTime
,convert(varchar,physical_io) DiskIO,Last_Batch LastBatch
,program_name ProgramName, cmd Command
from#Temp
orderbyBlkBy, spid
setnocount off
end
Go
USE[master]
GO
--预警SP
CREATEproc[dbo].[RecodeAndAlertInfo]
as
begin
declare@banch_idint
select@banch_id=isnull(MAX(batch_id),0)+1fromdc_info_SessionConn
--记录当前所有会话信息
insertintodc_info_SessionConn
SELECT@banch_id,S.session_id, R.blocking_session_id,
current_execute_sql =SUBSTRING(T.text,
R.statement_start_offset /2+1,
CASE
WHENstatement_end_offset =-1THENLEN(T.text)
ELSE(R.statement_end_offset -statement_start_offset) /2+1
END),
S.login_name,S.host_name,databaseName=DB_NAME(R.database_id),S.program_name,R.command,
S.status,S.cpu_time, memory_usage_kb =S.memory_usage *8, S.reads, S.writes,
S.transaction_isolation_level,C.connect |