Tag Archives: MSDB

SQL Server Agent Job Owner

It is a best practice to keep the SQL Agent Job owner to SA or Service Account. Please don’t make the job owner to any user’s windows ID. If the employee left the company his ID will be terminated and access will be revoked.
You can find out the jobs owner by executing the below query.

SELECT 
j.name,
l.[name] as [JobOwner]
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]

You can change the job owner by executing the below query.

EXEC MSDB.dbo.sp_update_job 
@job_name = 'Job Name', 
@owner_login_name = 'sa'

Script to Monitor the Running Job Status

Think of environment where multiple jobs are running at the same time and you want to monitor the current status of each job. One option is to use the job activity monitor.

 

And another way is you can use the below system stored procedures by running it against the MSDB database.

 

exec msdb..sp_get_composite_job_info
@execution_status=1

exec msdb.dbo.sp_help_job
@execution_status = 1

 

You can check the job execution status parameter values as below.

0 = Not idle or suspended

1 = Executing

2 = Waiting For Thread

3 = Between Retries

4 = Idle

5 = Suspended

6 = Waiting For Step To Finish

7 = Performing Completion Actions

 

How to find database restore history from MSDB?

MSDB database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail. Again SQL Server is using MSDB for storing the history of backup, restore, log-shipping details and more…

You can query the MSDB as below to get the details about the SQL Server restore history.

SELECT TOP 10 *
FROM restorehistory WITH (nolock)WHERE (destination_database_name = 'Database Name')ORDER BY restore_date DESC
All Databases

SELECT TOP 10 * FROM restorehistory WITH (nolock)ORDER BY restore_date DESC