Log all Activities using sp

Spread the love

It is not a new thing for a DBA to check what’s running currently on server in order to figure out issues. My personal favorite tool for finding out current activities is Adam Mechanic’s sp_WhoIsActive. Thanks to SQL Server communities like Brent Ozar Unlimited, we have lot of articles on how to use the proc sp_WhoIsActive. My combination of parameters for proc is as below:-

EXEC master..sp_WhoIsActive @get_plans=1, @get_full_inner_text=1, @get_transaction_info=1, 
@get_task_info=2, @get_locks=1, @get_avg_time=1, @get_additional_info=1,@find_block_leaders=1

1 2	EXEC master..sp_WhoIsActive @get_plans=1, @get_full_inner_text=1, @get_transaction_info=1, @get_task_info=2, @get_locks=1, @get_avg_time=1, @get_additional_info=1,@find_block_leaders=1

Now, it is often our requirement to log running activities to trap execution details for particular time frame. To log all activities of server using sp_WhoIsActive, one can visit Brent’s blog How to Log Activity Using sp_whoisactive in a Loop. I wish to do the same using SQL Agent Job. So below are the steps and queries.

Step 1: Create Your @destination_table
For me, below code from from Brent’s article works perfectly.

use master
go
	
DECLARE @destination_table VARCHAR(4000) ;
SET @destination_table = 'WhoIsActive_ResultSets';

DECLARE @schema VARCHAR(4000) ;
--	Specify all your proc parameters here
EXEC master..sp_WhoIsActive @get_plans=1, @get_full_inner_text=1, @get_transaction_info=1, @get_task_info=2, @get_locks=1, @get_avg_time=1, @get_additional_info=1,@find_block_leaders=1,
							@return_schema = 1,
							@schema = @schema OUTPUT ;

SET @schema = REPLACE(@schema, '<table_name>', @destination_table) ;

PRINT @schema
EXEC(@schema) ;
GO

use master

DECLARE @destination_table VARCHAR(4000) ;

SET @destination_table = 'WhoIsActive_ResultSets';

DECLARE @schema VARCHAR(4000) ;

-- Specify all your proc parameters here

EXEC master..sp_WhoIsActive @get_plans=1, @get_full_inner_text=1, @get_transaction_info=1, @get_task_info=2, @get_locks=1, @get_avg_time=1, @get_additional_info=1,@find_block_leaders=1,

@return_schema = 1,

@schema = @schema OUTPUT ;

SET @schema = REPLACE(@schema, '<table_name>', @destination_table) ;

PRINT @schema

EXEC(@schema) ;

Points to note here is that I am naming my destination table ‘WhoIsActive_ResultSets‘. The table structure will automatically get created based on passed parameters of sp_WhoIsActive. This will create table WhoIsActive_ResultSets in master database.

Step 2: Create Agent Job to Periodically Log Data
Below script can be used to create a job named ‘Log_With_sp_WhoIsActive’. It will be schedule to run every 3 seconds with no end date.

--	Step 2: Create Your Loop to Periodically Log Data
USE [msdb]
GO

/****** Object:  Job [Log_With_sp_WhoIsActive]    Script Date: 12/27/2016 4:55:22 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 12/27/2016 4:55:22 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Log_With_sp_WhoIsActive', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'This job will log activities using Adam Mechanic''s [sp_whoIsActive] stored procedure.

Results are saved into master..WhoIsActive_ResultSets table.

Job will run every 3 seconds once started.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Log activities with [sp_WhoIsActive]]    Script Date: 12/27/2016 4:55:22 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log activities with [sp_WhoIsActive]', 
		@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'DECLARE	@destination_table VARCHAR(4000);
SET @destination_table = ''WhoIsActive_ResultSets'';

EXEC master..sp_WhoIsActive @get_plans=1, @get_full_inner_text=1, @get_transaction_info=1, @get_task_info=2, @get_locks=1, @get_avg_time=1, @get_additional_info=1,@find_block_leaders=1,
            @destination_table = @destination_table ;', 
		@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'Log_Using_whoIsActive_Every_3_Seconds', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=2, 
		@freq_subday_interval=10, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20161227, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'f583e6cd-9431-4afc-94a3-e3ef9bfa0d27'
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

-- Step 2: Create Your Loop to Periodically Log Data

USE [msdb]

/****** Object: Job [Log_With_sp_WhoIsActive] Script Date: 12/27/2016 4:55:22 PM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 12/27/2016 4:55:22 PM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

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

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Log_With_sp_WhoIsActive',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'This job will log activities using Adam Mechanic''s [sp_whoIsActive] stored procedure.

Results are saved into master..WhoIsActive_ResultSets table.

Job will run every 3 seconds once started.',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

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

/****** Object: Step [Log activities with [sp_WhoIsActive]] Script Date: 12/27/2016 4:55:22 PM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log activities with [sp_WhoIsActive]',

@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'DECLARE @destination_table VARCHAR(4000);

SET @destination_table = ''WhoIsActive_ResultSets'';

EXEC master..sp_WhoIsActive @get_plans=1, @get_full_inner_text=1, @get_transaction_info=1, @get_task_info=2, @get_locks=1, @get_avg_time=1, @get_additional_info=1,@find_block_leaders=1,

@destination_table = @destination_table ;',

@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'Log_Using_whoIsActive_Every_3_Seconds',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=2,

@freq_subday_interval=10,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20161227,

@active_end_date=99991231,

@active_start_time=0,

@active_end_time=235959,

@schedule_uid=N'f583e6cd-9431-4afc-94a3-e3ef9bfa0d27'

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:

Step 3: Query the resultset
Below query can be used to fetch activities for current day.

--	Step 3: Query the resultset
SELECT * FROM WhoIsActive_ResultSets r
	WHERE CAST(r.collection_time AS DATE) = CAST(GETDATE() AS date);

-- Step 3: Query the resultset

SELECT * FROM WhoIsActive_ResultSets r

WHERE CAST(r.collection_time AS DATE) = CAST(GETDATE() AS date);

We can adjust recurring intervals from 3 Seconds to any desired time interval. Also, just don’t forgot to perform cleansing after work is done by disabling/removing the job, and truncating or dropping the table. Below is the job history on my machine.

Friends, please feel free to correct me by comments. Also, comment if you find the script useful. If you like the article, do Like & Share. Happy Coding 🙂

ads: https://installmentloanline.com

2 Comments

Abhishek


December 28, 2016, 10:31 am

Thank you Ajay… This was really helpful me in proceed work. 🙂

Loading...
Rynat


January 10, 2018, 10:04 pm

Thank you Ajay.
Suggest you to clean your Agent Job history right away:
EXEC msdb.dbo.sp_purge_jobhistory
@job_name=@YourJobName,
@oldest_date=@TillThisMomentThatYouWantToClean

Loading...

Log all Activities using sp_WhoIsActive

Like this:

Related

2 Comments

Leave a ReplyCancel reply

Share this:

Like this:

Related

2 Comments

Leave a ReplyCancel reply