Checking what jobs are running using T-SQL 1

[drlex]

Greetings, all.
I'm using SQL Server 2005 as a small data warehouse for storing and processing information derived from our trading system and other sources (HR, Excel, csv), such operations being run from DTS jobs (dating from inception on SQL Server 2005) on a schedule starting at 5 a.m. and running for about four hours. The server reboots daily at 3 a.m., so no job will run over 24 hours.

I know that I should be migrating these jobs to SSIS, but there's a combination of "if it ain't broke" and lack of knowledge (self-taught SQL & SQL server admin).

Whilst things run mostly without hiccups, we're about to do a disaster recovery (DR) exercise with servers; restoring from tape-backups. This has resulted in the consideration of how to perform a forced re-run of jobs if the server has been inoperative over the time of 'reads' of the trading system.

I'm trying to compile a DTS job for DR that can be run at any time in the day, which will perform the following.
1) stop any running jobs
2) disable any jobs scheduled to run later that day
3) run all jobs not run that day consecutively in order

I think I can see how data extracted from msdb tables sysjobs, sysjobschedules, sysjobhistory and sysjobactivity will let me compile such a sequence, but what I want to check is the way to identify running jobs.

Since sysjobhistory does not log a job until it has completed (stopped, failed or succeeded), then it is better to use sysjobactivity, and test for jobs that have started today (run_requested_date or start_execution_date) but not stopped (NULL in stop_execution_date or job_history_id).

Does that sound the best way or have I overlooked something?

Apologies for the long question.

thanks,
lex

soi la, soi carré

 

[mrdenny]

You can use sp_help_job in the msdb database to see what jobs are running. This will show all jobs which are running.

exec sp_help_job @execution_status=1

I'm not sure why you reboot the server daily? SQL Server can be left running for days or months without rebooting. You are in fact hurting performance by rebooting the server daily.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[drlex]

Denny,
Thanks for that - ideal.
Whilst I can't use INSERT on the result set from the SP itself, putting OPENROWSET around it allows such.

select job_id, name, 0 , 'STOPPING'
from
openrowset ('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job') where current_execution_status = 1

AS to the daily reboot, the server is also host to some other applications, and not being a 24/7 operation, we have traditionally rebooted all boxen nightly to minimize process locks/memory leaks/application SNAFUs. IT ju-ju, I think...

soi la, soi carré