Monitoring Auditing Active Current Running Processes, TSQL queries in SQL Server using SYSPROCESSES and storing results in table
So, we all know what sysprocesses and sp_who2 do. These gives us the current transactions/processes running on SQL Server Instance at a given moment of time.
Now, what if on a regular interval bases, I want to capture what all processes are running and what is the Query/Batch being running.
Below is a simple Stored procedure, which makes use of SysProcesses, sys.dm_exec_requests and SYS.DM_EXEC_SQL_TEXT and using this all you need to do is, simply call this Stored Procedure in any of the SQL Server Agent Job on the schedule you want, It retrieves all the data needed and stores in a Table under database specified by you, including the QueryText which was running for a particular process during that time. This data can be very handy for auditing, Monitoring, etc.
Use MyDatabase Go CREATE Procedure usp_ImportTSQL AS BEGIN IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TSQLLog]') AND type in (N'U')) CREATE TABLE TSQLLog ( ID int identity(1,1), SPID int, [command] varchar(50), [blocking_session_id] int, [LASTWAITTYPE] varchar(50), [DBNAME] varchar(50), [QueryText] varchar(max), [Batch] varchar(max), [CPU] bigint, [HOSTNAME] varchar(25), [LOGIN_TIME] datetime, [LOGINNAME] varchar(50), [Status] varchar(25), [PROGRAM_NAME] varchar(250), [NT_DOMAIN] varchar(50), [NT_USERNAME] varchar(50) ) INSERT INTO TSQLLog ( SPID, [command], [blocking_session_id], [LASTWAITTYPE], [DBNAME], [QueryText], [Batch], [CPU], [HOSTNAME], [LOGIN_TIME], [LOGINNAME], [Status], [PROGRAM_NAME], [NT_DOMAIN], [NT_USERNAME] ) SELECT SPID, ER.command,ER.blocking_session_id,LASTWAITTYPE, DB_NAME(SP.DBID) AS DBNAME, SUBSTRING(est.text, (ER.statement_start_offset/2)+1, ((CASE ER.statement_end_offset WHEN -1 THEN DATALENGTH(est.text) ELSE ER.statement_end_offset END - ER.statement_start_offset)/2) + 1) AS QueryText, TEXT as [Batch],CPU,HOSTNAME,LOGIN_TIME,LOGINAME, SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME FROM MASTER..SYSPROCESSES SP INNER JOIN sys.dm_exec_requests ER ON sp.spid = ER.session_id CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST END GO