0

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.

  • At a guess, you've got the same service account for the Server Agent as for the SQL Server service. If so, I suggest taking a look at the scheduled jobs on your instances (the msdb proc you reference is called by the Agent during job execution). Runing sp_blitz can also tell you if you've got a busy agent – Peter Vandivier May 23 '18 at 6:51
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.