Finding info on jobs that are running

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding info on jobs that are running

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-03-11 : 04:59:40

Not sure if this is the right forum for this but:-

Some of our jobs run over (hours longer than they normally take) as they may depend on external stuff (could lose a link to a server or whatever).

My question is:-

How can I check when a job started running and if it is still running (how long it has run for).

Then based upon that how i would kill the job if it is running over.

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-11 : 05:04:31

[code]
EXEC sp_who2
[/code]
Is your first point of call. If you need to kill a job just issue[code]
KILL xxx
[/code]
WHERE xxx is the SPID of the process

This script can give you more info on the processes (what they are doing right now). You didn't mention if you were on sql server 2000 or not so this is one that can work on both 2000 and 2005 (and up)[code]
/* Jobs Details for a specific db (works on 2000 and 2005)
**
** Charlie (19/02/2008)
** Modified (11/06/2008)
*/

-- ==== Configuration =========================================================

-- Set this to the name of a DB if you only care about one db
-- Leave NULL if you want info on all DBS
DECLARE @dbName VARCHAR(50) SET @dbName = NULL

-- ==== ------------- =========================================================

USE MASTER
SET NOCOUNT ON

IF object_Id('tempdb..#spwho') IS NOT NULL DROP TABLE #spwho
IF object_Id('tempdb..#info') IS NOT NULL DROP TABLE #info
IF object_Id('tempdb..#dbccInput') IS NOT NULL DROP TABLE #dbccInput

DECLARE
@spid INT
, @sqlHandle BINARY(20)
, @sqlVersion INT

-- Which version of SQL server are we running.
SET @sqlVersion = CASE
WHEN @@VERSION LIKE '%2005 - 9.%' THEN 2005
WHEN @@VERSION LIKE '%2000 - 8.%' THEN 2000
END

CREATE TABLE #spWho (
[SPID] INT NOT NULL
, [Status] VARCHAR (255) NOT NULL
, [Login] VARCHAR (255) NOT NULL
, [HostName] VARCHAR (255) NOT NULL
, [BlkBy] VARCHAR(10) NOT NULL
, [DBName] VARCHAR (255) NULL
, [Command] VARCHAR (255) NOT NULL
, [CPUTime] INT NOT NULL
, [DiskIO] INT NOT NULL
, [LastBatch] VARCHAR (255) NOT NULL
, [ProgramName] VARCHAR (255) NOT NULL
, [SPID2] INT NOT NULL
)

-- Add a column if it's 2005
IF @sqlVersion = 2005 ALTER TABLE #spWho ADD [REQUESTID] INT NOT NULL DEFAULT 0

CREATE TABLE #dbccInput (
eventType NVARCHAR(30)
, paramaters INT
, eventInfo NVARCHAR(4000)
)

CREATE TABLE #info (
[spid] INT NOT NULL
, [status] VARCHAR(20) NOT NULL
, [login] VARCHAR(255) NOT NULL
, [blkBy] VARCHAR(10) NOT NULL
, [command] VARCHAR(255) NOT NULL
, [CPUTime] INT NOT NULL
, [DiskIO] INT NOT NULL
, [LastBatch] VARCHAR (255) NOT NULL
, [ProgramName] VARCHAR (255) NOT NULL
, [sqlHandle] BINARY(20) NULL
, [eventInfo] NVARCHAR(4000) NULL
, [sql] TEXT NULL
)

INSERT INTO #spWho EXEC sp_who2

-- Get basic job info for the database we are intersted in
INSERT INTO #info (
[spid]
, [status]
, [login]
, [blkBy]
, [command]
, [CPUTime]
, [DiskIO]
, [LastBatch]
, [ProgramName]
, [sqlHandle]
)
SELECT
sw.[spid]
, sw.[status]
, sw.[login]
, sw.[blkBy]
, sw.[command]
, sw.[CPUTime]
, sw.[diskIO]
, sw.[lastBatch]
, sw.[programName]
, sp.[sql_handle]
FROM
#spWho sw
JOIN master.dbo.sysprocesses sp ON sp.[spid] = sw.[spid]
WHERE
[DBName] = @dbName OR @dbName IS NULL

-- Get the sql info for each spid
DECLARE sqlCursor CURSOR LOCAL READ_ONLY FOR
SELECT
i.[spid]
, i.[sqlHandle]
FROM
#info i

OPEN sqlCursor

FETCH NEXT FROM sqlCursor INTO
@spid
, @sqlHandle

WHILE (@@fetch_status = 0) BEGIN

INSERT INTO #dbccInput
EXEC ('DBCC INPUTBUFFER(' + @spid + ')')

UPDATE #info
SET
[eventInfo] = dbccI.[eventInfo]
FROM
#dbccInput dbccI
WHERE
[spid] = @spid

-- clear the input info
TRUNCATE TABLE #dbccInput

-- Get the sql full text (only does currently running process)
UPDATE #info
SET
[sql] = fgs.[text]
FROM
::fn_get_sql(@sqlHandle) fgs
WHERE
[spid] = @spid

FETCH NEXT FROM sqlCursor INTO
@spid
, @sqlHandle

END

CLOSE sqlCursor
DEALLOCATE sqlCursor

-- Display the results
SELECT
[spid]
, [status]
, [login]
, [blkBy]
, [command]
, [CPUTime]
, [DiskIO]
, [LastBatch]
, [ProgramName]
, [eventInfo] AS lastEvent
, [sql] AS currentSql
FROM
#info

IF object_Id('tempdb..#spwho') IS NOT NULL DROP TABLE #spwho
IF object_Id('tempdb..#info') IS NOT NULL DROP TABLE #info
IF object_Id('tempdb..#dbccInput') IS NOT NULL DROP TABLE #dbccInput
[/code]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2010-03-11 : 05:17:25

Thanks, it's 2005 at mo, but we're moving to 2008 shortly.

Some of these jobs run overnight so I'll be looking to automate the checking of their runs and act on them accordingly.

Presently I log in a table how long each job takes to run so I have a history of running times for all the jobs.
This was to be the next stage, monitor and kill if required.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources