Sql Server Materials: SQLDBA- Capturing t he sp

If we want analyze the blocking information, we can use sp_whoisactive for on going blocking. if we want analyze the pas happed blocking information. we can relay on third party toools (monitoring tools.). if we don't have any monitoring tool one of the method logging the sp_whoisactive.

Below is script for logging the sp_whoisactive.

Here we have created two table and creating one job. In that job there are 4 steps, first two step for loading data into above tables. remaining two steps for deleting the data older than 72 hours. if you want you change this.

Here place the DB ANME with your DBname

USE [DB ANME]

/****** Object: Table [dbo].[whoisactive_log] Script Date: 12/3/2025 1:31:16 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[whoisactive_log](

[whoisactive_id] [bigint] IDENTITY(1,1) NOT NULL,

[dd_hh_mm_ss_mss] [nvarchar](50) NULL,

[session_id] [smallint] NOT NULL,

[sql_text] [xml] NULL,

[login_name] [nvarchar](128) NOT NULL,

[wait_info] [nvarchar](4000) NULL,

[cpu] [varchar](30) NULL,

[tempdb_allocations] [varchar](30) NULL,

[tempdb_current] [varchar](30) NULL,

[blocking_session_id] [smallint] NULL,

[blocked_session_count] [smallint] NULL,

[reads] [varchar](30) NULL,

[writes] [varchar](30) NULL,

[physical_reads] [varchar](30) NULL,

[query_plan] [xml] NULL,

[used_memory] [varchar](30) NULL,

[status] [varchar](30) NOT NULL,

[open_tran_count] [varchar](30) NULL,

[percent_complete] [varchar](30) NULL,

[host_name] [nvarchar](128) NULL,

[database_name] [nvarchar](128) NULL,

[program_name] [nvarchar](128) NULL,

[start_time] [datetime] NOT NULL,

[login_time] [datetime] NULL,

[request_id] [int] NULL,

[collection_time] [datetime] NOT NULL,

CONSTRAINT [pk_whoisactive_log] PRIMARY KEY CLUSTERED

(

[whoisactive_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

USE [DB ANME]

/****** Object: Table [dbo].[WhoIsActive_transaction_outer_plans] Script Date: 12/3/2025 1:31:23 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[WhoIsActive_transaction_outer_plans](

[WhoIsActive_transaction_outer_plans_id] [bigint] IDENTITY(1,1) NOT NULL,

[dd hh:mm:ss.mss] [varchar](8000) NULL,

[session_id] [smallint] NOT NULL,

[sql_text] [xml] NULL,

[sql_command] [xml] NULL,

[login_name] [nvarchar](128) NOT NULL,

[wait_info] [nvarchar](4000) NULL,

[tran_log_writes] [nvarchar](4000) NULL,

[CPU] [varchar](30) NULL,

[tempdb_allocations] [varchar](30) NULL,

[tempdb_current] [varchar](30) NULL,

[blocking_session_id] [smallint] NULL,

[reads] [varchar](30) NULL,

[writes] [varchar](30) NULL,

[physical_reads] [varchar](30) NULL,

[query_plan] [xml] NULL,

[used_memory] [varchar](30) NULL,

[status] [varchar](30) NOT NULL,

[tran_start_time] [datetime] NULL,

[implicit_tran] [nvarchar](3) NULL,

[open_tran_count] [varchar](30) NULL,

[percent_complete] [varchar](30) NULL,

[host_name] [nvarchar](128) NULL,

[database_name] [nvarchar](128) NULL,

[program_name] [nvarchar](128) NULL,

[start_time] [datetime] NOT NULL,

[login_time] [datetime] NULL,

[request_id] [int] NULL,

[collection_time] [datetime] NOT NULL,

CONSTRAINT [pk_WhoIsActive_transaction_outer_plans] PRIMARY KEY CLUSTERED

(

[WhoIsActive_transaction_outer_plans_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

USE [msdb]

/****** Object: Job [ADM - SP_Whoisactive_log] Script Date: 12/3/2025 1:29:35 AM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [Administrative] Script Date: 12/3/2025 1:29:35 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Administrative' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Administrative'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ADM - SP_Whoisactive_log',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=2,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Log the Sp_shoisactive',

@category_name=N'Administrative',

@owner_login_name=N'sa',

@notify_email_operator_name=N'DBA ALERT', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Insert data intowhoisactive_log table] Script Date: 12/3/2025 1:29:35 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert data intowhoisactive_log table',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=3,

@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'USE DBA

EXEC sp_WhoIsActive

@get_plans = 1,

@find_block_leaders=1,

@destination_table = ''whoisactive_log''

--@get_full_inner_text = 1,

--@get_transaction_info = 1,',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Purgeing data older than 3 days.] Script Date: 12/3/2025 1:29:35 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purgeing data older than 3 days.',

@step_id=2,

@cmdexec_success_code=0,

@on_success_action=3,

@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'USE DBA

DELETE FROM DBA.dbo.whoisactive_log WHERE collection_time <DATEADD(HOUR, -72, GETDATE())',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Data loading into WhoIsActive_transaction_outer_plans] Script Date: 12/3/2025 1:29:35 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Data loading into WhoIsActive_transaction_outer_plans',

@step_id=3,

@cmdexec_success_code=0,

@on_success_action=3,

@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'USE DBA

EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,

@get_outer_command = 1,

@get_plans = 1,

@destination_table = ''WhoIsActive_transaction_outer_plans''

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Data purging from WhoIsActive_transaction_outer_plans table] Script Date: 12/3/2025 1:29:35 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Data purging from WhoIsActive_transaction_outer_plans table',

@step_id=4,

@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'USE DBA

DELETE FROM DBA.dbo.WhoIsActive_transaction_outer_plans WHERE collection_time <DATEADD(HOUR, -72, GETDATE())',

@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'sp_whoisactive_every 5Minutes',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=4,

@freq_subday_interval=5,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20250929,

@active_end_date=99991231,

@active_start_time=0,

@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:

Sql Server Materials

Tuesday, 2 December 2025

SQLDBA- Capturing t he sp_whoisactive for every 5 min

No comments:

Post a Comment

SQLDBA- Capturing t he sp_whoisactive for every 5 min

Report Abuse