Sql-server – Availability Groups Service Account has over 25000 sessions open

availability-groupshigh-availabilitysql serversql server 2014

I am running Availability Groups on SQL Server 2014 with one database in the group. Today, the server was responding very slowly and when I got on the server I found that the Availability Groups service account had over 25000 sessions open. Every session was to the MSDB database with the same query, The query was:

(@P1 nvarchar(max),
 @P2 uniqueidentifier,
 @P3 int,
 @P4 int,
 @P5 int)
DECLARE @logTextWithPreAndPost nvarchar(max) set @logTextWithPreAndPost = N'' + @P1 + N''; 
EXECUTE msdb.dbo.sp_agent_write_sysjobstep_log 
 @job_id = @P2, 
 @is_system = @P3, 
 @step_id = @P4, 
 @log_text = @logTextWithPreAndPost, 
 @append_to_last=@P5

Does anyone know why it would hold so many sessions open or what I can do to stop it.

Best Answer

This is most likely because you have enabled the Log to table option in the agent job step.

Run 

SELECT spid, 
       program_name,  
       db_name(dbid) AS databasename
FROM   sys.sysprocesses 
WHERE  spid > 50

or if you have sp_whoisactive installed (http://whoisactive.com/)

EXEC sp_whoisactive 
     @output_column_list = '[%dd%][session_id][sql_text][%database%][login_name][program_name]' /* if they are sleeping spids, add @show_sleeping_spids = 2*/

Get the uniqueidentifier of the job from the program_name, then run

SELECT name
FROM   msdb.dbo.sysjobs 
WHERE  CONVERT(BINARY(16), job_id)=0x[id]

Replace 0x[id] with your id. Once you get the job name, go to the job step, then un-check the log to table option.

Related Question