Blocking by sp_sqlagent_log_jobhistory

Hi ,
We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
morning hours when we have lot of jobs to be executed. When I do DBCC
INPUTBUFFER for the process id's blocking/blocked I am getting
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
Nothing changed with the job schedules and we never had the problem but
since couple of week things are running slower and processes are being
blocked .
Since all the blocked/blocking process id's are by
sp_sqlagent_log_jobhistory I think there is something going wrong with my
agent when it is trying to log the success/faliure of the job completion.
Any solutions/suggestions will be helpful.
Thanks,
Arshad
arshadmd-nospam@.gmail.com
Arshad wrote:
> Hi ,
> We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
> morning hours when we have lot of jobs to be executed. When I do DBCC
> INPUTBUFFER for the process id's blocking/blocked I am getting
> EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
> Nothing changed with the job schedules and we never had the problem but
> since couple of week things are running slower and processes are being
> blocked .
> Since all the blocked/blocking process id's are by
> sp_sqlagent_log_jobhistory I think there is something going wrong with my
> agent when it is trying to log the success/faliure of the job completion.
> Any solutions/suggestions will be helpful.
first, defrag the msdb database, especially the sysjobhistory table.
see if that fixes it.
next, go to sqlagent properties and see how many job histories it's
configured to keep. maybe you have it set too high, i think 1000 is the
default, i often run mine at 100,000 with 10,000 per job and have no
problems, but i rarely have 2 or 3 jobs running at the exact same time.
you could also try clearing the log on that same tab where you tell it
how many histories to keep.
maybe you have your msdb database on a bad/slow disk?

Blocking by sp_sqlagent_log_jobhistory

Hi ,
We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
morning hours when we have lot of jobs to be executed. When I do DBCC
INPUTBUFFER for the process id's blocking/blocked I am getting
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
Nothing changed with the job schedules and we never had the problem but
since couple of week things are running slower and processes are being
blocked .
Since all the blocked/blocking process id's are by
sp_sqlagent_log_jobhistory I think there is something going wrong with my
agent when it is trying to log the success/faliure of the job completion.
Any solutions/suggestions will be helpful.
Thanks,
--
Arshad
arshadmd-nospam@.gmail.comArshad wrote:
> Hi ,
> We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
> morning hours when we have lot of jobs to be executed. When I do DBCC
> INPUTBUFFER for the process id's blocking/blocked I am getting
> EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
> Nothing changed with the job schedules and we never had the problem but
> since couple of week things are running slower and processes are being
> blocked .
> Since all the blocked/blocking process id's are by
> sp_sqlagent_log_jobhistory I think there is something going wrong with my
> agent when it is trying to log the success/faliure of the job completion.
> Any solutions/suggestions will be helpful.
first, defrag the msdb database, especially the sysjobhistory table.
see if that fixes it.
next, go to sqlagent properties and see how many job histories it's
configured to keep. maybe you have it set too high, i think 1000 is the
default, i often run mine at 100,000 with 10,000 per job and have no
problems, but i rarely have 2 or 3 jobs running at the exact same time.
you could also try clearing the log on that same tab where you tell it
how many histories to keep.
maybe you have your msdb database on a bad/slow disk?

Blocking by sp_sqlagent_log_jobhistory

Hi ,
We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
morning hours when we have lot of jobs to be executed. When I do DBCC
INPUTBUFFER for the process id's blocking/blocked I am getting
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
Nothing changed with the job schedules and we never had the problem but
since couple of week things are running slower and processes are being
blocked .
Since all the blocked/blocking process id's are by
sp_sqlagent_log_jobhistory I think there is something going wrong with my
agent when it is trying to log the success/faliure of the job completion.
Any solutions/suggestions will be helpful.
Thanks,
--
Arshad
arshadmd-nospam@.gmail.comArshad wrote:
> Hi ,
> We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
> morning hours when we have lot of jobs to be executed. When I do DBCC
> INPUTBUFFER for the process id's blocking/blocked I am getting
> EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
> Nothing changed with the job schedules and we never had the problem but
> since couple of week things are running slower and processes are being
> blocked .
> Since all the blocked/blocking process id's are by
> sp_sqlagent_log_jobhistory I think there is something going wrong with my
> agent when it is trying to log the success/faliure of the job completion.
> Any solutions/suggestions will be helpful.
first, defrag the msdb database, especially the sysjobhistory table.
see if that fixes it.
next, go to sqlagent properties and see how many job histories it's
configured to keep. maybe you have it set too high, i think 1000 is the
default, i often run mine at 100,000 with 10,000 per job and have no
problems, but i rarely have 2 or 3 jobs running at the exact same time.
you could also try clearing the log on that same tab where you tell it
how many histories to keep.
maybe you have your msdb database on a bad/slow disk?

Blocked transactions - how to identify the sql commands?

Hi SQLServer gurus :)

Just wondering if anyone can give me some info on how to find out the full syntax of the commands executed by the blocked and blocking SPID's in a locking situation.

Using sp_who or sp_who2 will give basic info on the blocked trans (such as DELETE, SELECT etc), but not the actual statement. The blocking spid's command is only showing AWAITING COMMAND.

Not really an urgent problem, but any suggestions appreciated!

Cheers,
MeganDBCC INPUTBUFFER
Displays the last statement sent from a client to Microsoft SQL Server.

Syntax
DBCC INPUTBUFFER (spid)

You can use this command to see what is the longest running command which may point to the transaction that is causing the blocking.

DBCC OPENTRAN
Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information. An informational message is displayed if there are no active transactions.

Syntax
DBCC OPENTRAN
( { 'database_name' | database_id} )
[ WITH TABLERESULTS
[ , NO_INFOMSGS ]
]|||Thanks for your response, achorozy.