September 28, 2015 at 2:42 pm
I have a server that has hundreds of spids connected to msdb that are sitting idle ( awaiting command) . I have a sister server with the same configuration which has none of these spids. My understanding is the msdb is used by the server for internal house keeping, alerts ,and email.
The server with the excessive spids is also no longer sending out any emails. I'm not sure if the two are connected.
Any thoughts would be appreciated.
September 29, 2015 at 7:43 am
Having you checked to see where the SPID's originate from, this query will identify that.
select Spid, Status, last_batch, hostname, program_name, nt_domain, nt_username, login_time
from sysprocesses
WHERE DBID = DB_ID('msdb')
MCITP SQL 2005, MCSA SQL 2012
September 29, 2015 at 11:45 am
ok so all the spids are comming from sql server itself. It looks like it is trying to update the status of a job .
Here is the last batch
SQLAgent - TSQL JobStep (Job 0x6BA04F19A20889429458B3A18F010E17 : Step 4)
this is what in the inputbuffer
(@P1 nvarchar(max),@P2 uniqueidentifier,@P3 int,@P4 int)DECLARE @logTextWithPreAndPost nvarchar(max) set @logTextWithPreAndPost = N'' + @P1 + N''; EXECUTE msdb.dbo.sp_write_sysjobstep_log @job_id = @P2, @step_id = @P3, @log_text=@logTextWithPreAndPost, @append_to_last=@P4
how do I translate the job id to the job id in the sysjobs table so I can figure out what job is causing these.
September 29, 2015 at 12:04 pm
You need something like this:
SELECT
'SQLAgent - TSQL JobStep (Job 0x'+
CONVERT(char(32),CAST(j.job_id AS binary(16)),2) +
' : Step ' +
CAST(js.step_id AS VARCHAR(3)) +
')', j.job_id, j.name
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
ON j.job_id = js.job_id;
From Jonathan Kehayias: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/75437c45-7233-4288-b48b-f64fab3d5097/using-jobid-in-string-format-to-get-job-name
September 29, 2015 at 12:17 pm
thx... found the job.. now to fix the issue.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply