CREATE PROCEDURE [dbo].[sp_chi_who]
@spid VARCHAR(5) = NULL,
@status VARCHAR(50) = NULL,
@loginame VARCHAR(50) = NULL,
@command VARCHAR(50) = NULL,
@dbname VARCHAR(50) = NULL,
@hostname VARCHAR(50) = NULL,
@waittime VARCHAR(10) = 'All',
@program VARCHAR(100) = NULL,
@detail CHAR(1) = 'Y',
@transactionlevel INTEGER = NULL
AS
/* Creation Date: 11/18/2002
** Modification date: 12/11/2002
** Author: Richard Ding
** Features (Compared with sp_who or sp_who2):
** 1. Provide more parameters including spid, status, loginame, dbname, hostname and program;
** 2. Sqeeze more useful info into one window with less scrolling back and forth.
** Test code:
** sp_chi_who
** sp_chi_who 1378
** sp_chi_who @spid = 29
** sp_chi_who @status = runnable
** sp_chi_who @waittime = '> 0'
** sp_chi_who @program = 'SQL Query Analyzer'
*/
DECLARE @db_length INT
DECLARE @loginame_length INT
DECLARE @hostname_length INT
DECLARE @program_length INT
DECLARE @sql_string VARCHAR(4000)
SET NOCOUNT ON
SELECT @db_length = MAX(LEN(DB_NAME(dbid))),
@loginame_length = MAX(LEN(loginame)),
@hostname_length = MAX(LEN(hostname)),
@program_length = MAX(LEN(program_name))
FROM sys.sysprocesses
IF (@detail = 'Y')
BEGIN
SET @sql_string =
'SELECT CONVERT(VARCHAR(4), s.spid) AS [spid],
CONVERT(VARCHAR(10), s.status) AS [status],
CONVERT(VARCHAR(' + CAST(@loginame_length AS VARCHAR(3)) + '), s.loginame) AS [loginame],
LEFT(DB_NAME(s.dbid), ' + CAST(@db_length AS VARCHAR(3)) + ') AS [dbname],
CONVERT(VARCHAR(16), s.cmd) AS [command],
LEFT(s.hostname, ' + CAST(@hostname_length AS VARCHAR(3)) + ') AS [hostname],
CONVERT(VARCHAR(8), s.memusage) AS [memusg],
CONVERT(VARCHAR(10), s.physical_io) AS [phys_io],
LEFT(SUBSTRING(CONVERT(VARCHAR(25), s.login_time, 101), 1, 5) + '' '' + CONVERT(VARCHAR(25), s.login_time, 8), 15) AS [login_time],
LEFT(SUBSTRING(CONVERT(VARCHAR(25), s.last_batch, 101), 1, 5) + '' '' + CONVERT(VARCHAR(25), s.last_batch, 8), 15) AS [last_batch],
LEFT(s.program_name, ' + CAST(@program_length AS VARCHAR(3)) + ') AS [program],
CONVERT(VARCHAR(4), s.cpu) AS [cpu],
CONVERT(CHAR(5),s.blocked) AS [blkBy],
CASE des.transaction_isolation_level
WHEN 0 THEN CAST(des.transaction_isolation_level AS VARCHAR(4)) + '' - Unspecified''
WHEN 1 THEN CAST(des.transaction_isolation_level AS VARCHAR(4)) + '' - ReadUncommitted''
WHEN 2 THEN CAST(des.transaction_isolation_level AS VARCHAR(4)) + '' - ReadCommitted''
WHEN 3 THEN CAST(des.transaction_isolation_level AS VARCHAR(4)) + '' - Repeatable''
WHEN 4 THEN CAST(des.transaction_isolation_level AS VARCHAR(4)) + '' - Serializable''
WHEN 5 THEN CAST(des.transaction_isolation_level AS VARCHAR(4)) + '' - Snapshot''
END AS transactionlevel,
s.waittype,
CONVERT(VARCHAR(15), s.waittime) AS [waittime],
CONVERT(VARCHAR(30), s.lastwaittype) AS [lastwaittype],
CONVERT(VARCHAR(17), s.waitresource) AS [waitresource],
OBJECT_NAME([objectid],s.dbid) AS [objectname],
CAST(TEXT AS VARCHAR(MAX)) AS [definition]
FROM sys.sysprocesses s WITH (NOLOCK)
INNER JOIN sys.dm_exec_sessions des
ON des.session_id = s.spid '
IF @transactionlevel IS NOT NULL
SET @sql_string = @sql_string + '
AND des.transaction_isolation_level = ' + CAST (@transactionlevel AS VARCHAR(4))
SET @sql_string = @sql_string + '
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE 1=1 '
END
ELSE
BEGIN
SET @sql_string =
'SELECT CONVERT(VARCHAR(4), s.spid) AS [spid],
CONVERT(VARCHAR(10), s.status) AS [status],
CONVERT(VARCHAR(' + CAST(@loginame_length AS VARCHAR(3)) + '), s.loginame) AS [loginame],
LEFT(DB_NAME(s.dbid), ' + CAST(@db_length AS VARCHAR(3)) + ') AS [dbname],
CONVERT(VARCHAR(16), s.cmd) AS [command],
LEFT(s.hostname, ' + CAST(@hostname_length AS VARCHAR(3)) + ') AS [hostname],
CONVERT(VARCHAR(8), s.memusage) AS [memusg],
CONVERT(VARCHAR(10), s.physical_io) AS [phys_io],
LEFT(SUBSTRING(CONVERT(VARCHAR(25), s.login_time, 101), 1, 5) + '' '' + CONVERT(VARCHAR(25), s.login_time, 8), 15) AS [login_time],
LEFT(SUBSTRING(CONVERT(VARCHAR(25), s.last_batch, 101), 1, 5) + '' '' + CONVERT(VARCHAR(25), s.last_batch, 8), 15) AS [last_batch],
LEFT(s.program_name, ' + CAST(@program_length AS VARCHAR(3)) + ') AS [program],
CONVERT(VARCHAR(4), s.cpu) AS [cpu],
CONVERT(CHAR(5),s.blocked) AS [blkBy],
CASE des.transaction_isolation_level
WHEN 0 THEN CAST(des.transaction_isolation_level AS VARCHAR(4)) + '' - Unspecified''
WHEN 1 THEN CAST(des.transaction_isolation_level AS VARCHAR(4)) + '' - ReadUncommitted''
WHEN 2 THEN CAST(des.transaction_isolation_level AS VARCHAR(4)) + '' - ReadCommitted''
WHEN 3 THEN CAST(des.transaction_isolation_level AS VARCHAR(4)) + '' - Repeatable''
WHEN 4 THEN CAST(des.transaction_isolation_level AS VARCHAR(4)) + '' - Serializable''
WHEN 5 THEN CAST(des.transaction_isolation_level AS VARCHAR(4)) + '' - Snapshot''
END AS transactionlevel,
s.waittype,
CONVERT(VARCHAR(15), s.waittime) AS [waittime],
CONVERT(VARCHAR(30), s.lastwaittype) AS [lastwaittype],
CONVERT(VARCHAR(17), s.waitresource) AS [waitresource]
FROM sys.sysprocesses s (NOLOCK)
INNER JOIN sys.dm_exec_sessions des
ON des.session_id = s.spid '
IF @transactionlevel IS NOT NULL
SET @sql_string = @sql_string + '
AND des.transaction_isolation_level = ' + CAST (@transactionlevel AS VARCHAR(4))
SET @sql_string = @sql_string + '
WHERE 1=1 '
END
IF @spid IS NOT NULL
SET @sql_string = LTRIM(RTRIM(@sql_string)) + ' AND s.spid = ' + LTRIM(RTRIM(@spid))
IF @status IS NOT NULL
SET @sql_string = LTRIM(RTRIM(@sql_string)) + ' AND s.status = ''' + LTRIM(RTRIM(@status)) + ''''
IF @loginame IS NOT NULL
SET @sql_string = LTRIM(RTRIM(@sql_string)) + ' AND s.loginame = ''' + LTRIM(RTRIM(@loginame)) + ''''
IF @command IS NOT NULL
SET @sql_string = LTRIM(RTRIM(@sql_string)) + ' AND s.cmd = ''' + LTRIM(RTRIM(@command)) + ''''
IF @dbname IS NOT NULL
SET @sql_string = LTRIM(RTRIM(@sql_string)) + ' AND DB_NAME(s.dbid) = ''' + LTRIM(RTRIM(@dbname)) + ''''
IF @hostname IS NOT NULL
SET @sql_string = LTRIM(RTRIM(@sql_string)) + ' AND s.hostname = ''' + LTRIM(RTRIM(@hostname)) + ''''
IF @program IS NOT NULL
SET @sql_string = LTRIM(RTRIM(@sql_string)) + ' AND s.program_name LIKE ''%' + LTRIM(RTRIM(@program)) + '%'''
IF @waittime <> 'All'
SET @sql_string = LTRIM(RTRIM(@sql_string)) + ' AND s.waittime > 0'
-- Add the order by clause
IF @dbname IS NOT NULL
SET @sql_string = LTRIM(RTRIM(@sql_string)) + ' ORDER BY s.loginame ASC, s.status asc, DB_NAME(s.dbid) ASC'
ELSE
SET @sql_string = LTRIM(RTRIM(@sql_string)) + ' ORDER BY DB_NAME(s.dbid) ASC, s.loginame ASC, s.hostname ASC, s.status ASC'
-- Execute the sql
EXEC (@sql_string)
Schedule the job to get the alerts.
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Batch - H]] Script Date: 7/20/2025 9:46:12 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Batch - H]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Batch - H]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ADM - Longer DB OpenTran',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Batch - H]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Longer WM open tran] Script Date: 7/20/2025 9:46:13 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Longer WM open tran',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'---- Create table in DBA database - one time.
--CREATE TABLE dbo.DBOpenTranStatus (
-- ActiveTransaction VARCHAR(25) NOT NULL,
-- Details sql_variant NOT NULL
-- );
--ALTER TABLE dbo.DBOpenTranStatus
--ADD CONSTRAINT PK_DBOpenTranStatus PRIMARY KEY (ActiveTransaction);
USE tempdb
GO
DECLARE @DATE DATETIME
SET @DATE = GETDATE()
DECLARE @xml1 NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @emailattachment VARCHAR(200);
DECLARE @sp_id VARCHAR(10)
-- Execute the command, putting the results in the table.
TRUNCATE TABLE DBA.dbo.DBOpenTranStatus
INSERT INTO DBA.dbo.DBOpenTranStatus
EXEC (''DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS'');
IF EXISTS ( SELECT 1 FROM DBA.dbo.DBOpenTranStatus WHERE ActiveTransaction = ''OLDACT_STARTTIME'' AND DATEDIFF(MI, CAST(Details AS DATETIME), @DATE) >= 30 )
BEGIN
SET @xml1 =
''<html><body><H4>Longer DB OpenTran</H4>
<table border = 1>
<tr>
<th> ActiveTransaction </th> <th> Details </th></tr>'' +
CAST((SELECT [ActiveTransaction] AS ''td'',
'''',
[Details] AS ''td''
FROM DBA.dbo.DBOpenTranStatus
FOR XML PATH(''tr''), ELEMENTS) AS NVARCHAR(MAX)) +
N''</table>'';
SET @body = @xml1 + ''</body></html>''
SELECT TOP 1 @sp_id = CAST(Details AS VARCHAR(100) ) FROM DBA.dbo.DBOpenTranStatus WHERE ActiveTransaction = ''OLDACT_SPID''
SET @emailattachment = ''exec master.dbo.sp_chi_who '' + @sp_id
--SELECT @emailattachment
--SELECT @body
EXEC msdb.dbo.sp_send_dbmail
@subject = ''Longer DB OpenTran Report'',
@body = @body,
@body_format =''HTML'',
@recipients = ''ksubbareddy.sap@gmail.com'',
@query = @emailattachment,
@query_result_width = 1000,
@attach_query_result_as_file = 1,
@query_attachment_filename = ''DBOpenTran.txt'';
END
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Longer WM open tran',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=30,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20250718,
@active_end_date=99991231,
@active_start_time=10500,
@active_end_time=235959,
@schedule_uid=NULL;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
No comments:
Post a Comment