DBA Handy Tools

SQL Server Activity Capture

Capture SQL Server Activity

If you are interested in any activity of your SQL Server, you may use the below query to find out.

USE [msdb]
GO


SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[P_CaptureSQLActivity]
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO CaptureSQLActivity
SELECT * 
--into CaptureSQLActivity
FROM
(
SELECT
@@servername AS 'SERVERNAME',
DB_NAME(SQLOPS_DMExecRequests.database_id) as 'DBNAME',
SQLOPS_DMExecSessions.login_name as 'LOGIN_NAME',
SQLOPS_DMExecSessions.host_name 'MACHINE_NAME',

SQLOPS_DMExecConnections.client_net_address as CLIENT_IP,
--SQLOPS_DMExecSessions.host_name as MACHINE_NAME,
SQLOPS_DMEXECSQLTEXT.text as COMMAND,

OBJECT_NAME(SQLOPS_DMExecSessionst.objectid,
SQLOPS_DMExecRequests.database_id)
AS 'OBJECT_NAME',
SQLOPS_DMExecRequests.command as 'OPERATION',
SQLOPS_DMExecSessions.session_id as 'SPID' ,
SQLOPS_DMExecSessions.status as 'SPID_STATUS',
SQLOPS_DMExecRequests.blocking_session_id as 'SPID_BEING_BLOCKED',
SQLOPS_DMExecSessions.cpu_time as 'CPU_USAGE_IN_MS',
SQLOPS_DMExecSessions.reads as 'TOTAL_READS',
SQLOPS_DMExecSessions.writes as 'TOTAL_WRITES',
SQLOPS_DMExecConnections.last_write as 'LAST_WRITE',
SQLOPS_DMExecSessions.[program_name] as 'APPLICATION_NAME',
SQLOPS_DMExecRequests.wait_type as 'TYPE_OF_WAIT',
SQLOPS_DMExecRequests.wait_time as 'WAITING_TIME',
SQLOPS_DMExecRequests.last_wait_type as 'PREVIOUS_WAIT_TYPE',
SQLOPS_DMExecRequests.wait_resource as 'WAITING_ON_RESOURCE',
CASE SQLOPS_DMExecSessions.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS 'ISOLATION_LEVEL',
SUBSTRING(SQLOPS_DMExecSessionst.text, SQLOPS_DMExecRequests.statement_start_offset / 2,
( CASE WHEN SQLOPS_DMExecRequests.statement_end_offset = -1
THEN DATALENGTH(SQLOPS_DMExecSessionst.text)
ELSE SQLOPS_DMExecRequests.statement_end_offset
END - SQLOPS_DMExecRequests.statement_start_offset ) / 2)
AS 'EXECUTING_STATEMENT' --,



FROM sys.dm_exec_sessions SQLOPS_DMExecSessions
LEFT JOIN sys.dm_exec_requests SQLOPS_DMExecRequests
ON SQLOPS_DMExecSessions.session_id = SQLOPS_DMExecRequests.session_id
LEFT JOIN sys.dm_exec_connections SQLOPS_DMExecConnections
ON SQLOPS_DMExecSessions.session_id = SQLOPS_DMExecConnections.session_id
CROSS APPLY sys.dm_exec_sql_text(SQLOPS_DMExecRequests.sql_handle) SQLOPS_DMExecSessionst
CROSS APPLY sys.dm_exec_query_plan(SQLOPS_DMExecRequests.plan_handle) SQLOPS_DMExecQueryPlan
CROSS APPLY sys.dm_exec_sql_text(SQLOPS_DMExecConnections.most_recent_sql_handle) SQLOPS_DMEXECSQLTEXT
WHERE SQLOPS_DMExecSessions.session_id <> @@SPID
)X
--WHERE  (X.EXECUTING_STATEMENT like '%Backup%' or X.OPERATION like '%Backup%')
END
GO


USE [msdb]
GO


SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CaptureSQLActivity](
	[SERVERNAME] [nvarchar](128) NULL,
	[DBNAME] [nvarchar](128) NULL,
	[LOGIN_NAME] [nvarchar](128) NOT NULL,
	[MACHINE_NAME] [nvarchar](128) NULL,
	[CLIENT_IP] [varchar](48) NULL,
	[COMMAND] [nvarchar](max) NULL,
	[OBJECT_NAME] [nvarchar](128) NULL,
	[OPERATION] [nvarchar](32) NULL,
	[SPID] [smallint] NOT NULL,
	[SPID_STATUS] [nvarchar](30) NOT NULL,
	[SPID_BEING_BLOCKED] [smallint] NULL,
	[CPU_USAGE_IN_MS] [int] NOT NULL,
	[TOTAL_READS] [bigint] NOT NULL,
	[TOTAL_WRITES] [bigint] NOT NULL,
	[LAST_WRITE] [datetime] NULL,
	[APPLICATION_NAME] [nvarchar](128) NULL,
	[TYPE_OF_WAIT] [nvarchar](60) NULL,
	[WAITING_TIME] [int] NULL,
	[PREVIOUS_WAIT_TYPE] [nvarchar](60) NULL,
	[WAITING_ON_RESOURCE] [nvarchar](256) NULL,
	[ISOLATION_LEVEL] [varchar](15) NULL,
	[EXECUTING_STATEMENT] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


USE MSDB
GO
WHILE 1=1
BEGIN
SET NOCOUNT ON;
EXECUTE P_CaptureSQLActivity
/*
select getdate()
PRINT (CONVERT( VARCHAR(24), GETDATE(), 121)) 
If you plan to run with every 1 second, the below is the wait delay. First 00 is hour Second 00 is Minutes and the third 00 is Seconds
*/
WAITFOR DELAY '00:00:01.000';
END

 

Leave a Reply

Your email address will not be published. Required fields are marked *