May 3, 2004 at 3:11 pm
Good day, all
I have an SP that runs on a scheduled basis. The SP is used to calculate and move large volumes of data from a staging table to aggregate tables in a data warehouse. Basically, a standard ETL procedure. The logic is such that the SP must not run if a prior run is still in progress.
I can use the following code to get the name of the SP currently running.
select @@procID ThisProcID, OBJECT_NAME(@@procID)
Is there a procedure or table that contains the name of all running SPs? I would like to do something like the following pseudocode:
if not exists (select 1 from SomeTable where SomeColumn = OBJECT_NAME(@@procID))
begin
-- do real work here
end
Thanks in advance for any help you can provide.
Cheers
May 3, 2004 at 4:41 pm
There are several ways. You can Run sp_who, or sp_who2 from Query Analyzer. The results however may be a bit cryptic. In Enterprise Manager, you can also expand the Management...Current Activity Folder and click on the Process Info Tab. Double-clicking an item here opens the Process Details Dialog and shows the T-SQL Batch associated with that process.
Good Luck,
Pete
May 3, 2004 at 4:50 pm
All excellent suggestions. Unfortunately, they are all interactive. The procedure is intended to function ina lights out environment. I am seeking a solution that I can embed in the stored procedure so that it exits cleanly. I can run DBCC InputBuffer(SPID) and store the results in a temp table as well. I can then query the temp table using "LIKE" and wrapping the current object name in "%". In a similar manner I can use dynamic SQL to achieve the same sort of search results. All of these seem rather kludgy.
One thought I had is that DBCC is probably reading the input buffer from some system table - I just need to figure out which table or view, and then join to it in my baseline query.
Thanks for the ideas. THey are all helpful.
May 3, 2004 at 5:51 pm
I see. fn_get_sql() is a new admin function included with SQL2K sp3. It returns the objectid of currently executing sql. It takes sql_handle, a binary from sysprocesses as its only param.
DECLARE @sql_handle-2 binary(20)
SET @sql_handle-2 = (select sql_handle from master.dbo.sysprocesses where status = 'runnable' and dbid = 8)
-- where spid in
-- (
-- select spid, * from syslocks
-- ))
SELECT
*
FROM ::fn_get_sql(@sql_handle)
Let me know how it goes.
Pete
May 4, 2004 at 1:04 am
If you wrote the procedure (or if you can modify it), an easier approach would be to write something in a table when the procedure starts and delete it when the procedure is done. Special care should be taken to delete it in case of an error. This could be done in the safest way by using a transaction. In this case, to read the table while the transaction is not committed we must use the NOLOCK hint (equivalent of READUNCOMMITTED). The following summarizes what I've said:
CREATE TABLE MyProcIsRunning (StartTime datetime)
GO
CREATE PROCEDURE MyProc AS
IF EXISTS (SELECT * FROM MyProcIsRunning WITH (NOLOCK)) BEGIN
RAISERROR('MyProc is already running',16,1)
RETURN 50000
END ELSE BEGIN
DECLARE @err int
BEGIN TRAN
INSERT INTO MyProcIsRunning VALUES (GETDATE())
-- do your stuff and after every line that could cause an error insert the following line: -- SET @err=@@ERROR IF @err<>0 BEGIN ROLLBACK RETURN @err END
WAITFOR DELAY '0:0:10' -- just for testing SET @err=@@ERROR IF @err<>0 BEGIN ROLLBACK RETURN @err END
DELETE MyProcIsRunning COMMIT END
Razvan
May 6, 2004 at 7:40 am
Peter: Your method works perfectly as long as I am logged in as SA. When I embed the process in the actual stored proc running as a non-priveleged account I get the following error.
Server: Msg 229, Level 14, State 5, Line 3
SELECT permission denied on object 'fn_get_sql', database 'master', owner 'system_function_schema'.
As a test I attempted to give "public" access to that function and was not permitted to do so. I am the SA on the box. Thoughts?
Razvan
We have currently implemented the semaphore table in a manner very similar to what you suggest. However certain long running procs, including the ETL proc, die silently. Because they die silently, we are unable to clear the semaphore. This is actually why we are looking for a more robust method of testing to see if the proc is running.
Current interim solution:
I like Peter's approach and will continue working through the permissions problem. Until then, here is what I have implemented as a workaround. It may help someone else:
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'WRL_GetActiveProcCount' AND type = 'P')
DROP PROCEDURE WRL_GetActiveProcCount
GO
CREATE PROCEDURE WRL_GetActiveProcCount
@InSPID NUMERIC,
@OutCurrentCount NUMERIC OUTPUT
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #thisProc (EventType VARCHAR(64),
Parameters NUMERIC,
EventInfo VARCHAR(256))
CREATE TABLE #ActiveSpids (GID int IDENTITY (1, 1) NOT NULL,
ActiveSpid NUMERIC null)
DECLARE @MinGID NUMERIC,
@MaxGID NUMERIC,
@thisSpid NUMERIC,
@QueryStr VARCHAR(100),
@IncomingEventInfo VARCHAR(500)
-- Get signature for @InSPID
SET @QueryStr = 'DBCC INPUTBUFFER(' + STR(@InSPID) + ')'
-- test & debug PRINT 'QueryStr = ' + @QueryStr
INSERT INTO #thisProc EXECUTE (@QueryStr)
SELECT @IncomingEventInfo = EventInfo FROM #thisProc WITH (NOLOCK)
-- test & debug PRINT 'IncomingEventInfo = ' + @IncomingEventInfo
INSERT INTO #ActiveSpids (ActiveSpid)
SELECT SPID FROM master.dbo.sysprocesses WITH (NOLOCK) WHERE KPID <> 0 AND SPID <> @InSPID
SELECT @MinGID = MIN(GID), @MaxGID = MAX(GID) FROM #ActiveSpids
WHILE @MinGID <= @MaxGID
BEGIN
SELECT @thisSpid = ActiveSpid FROM #ActiveSpids WHERE GID = @MinGID
SET @QueryStr = 'DBCC INPUTBUFFER(' + STR(@thisSPID) + ')'
INSERT INTO #thisProc EXECUTE (@QueryStr)
SET @MinGID = @MinGID + 1
END
-- test & debug SELECT * FROM #thisProc
SELECT @OutCurrentCount = COUNT(*) FROM #thisProc WHERE EventInfo = @IncomingEventInfo
END
GO
May 13, 2005 at 2:07 pm
I also came accross this code ,,,
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=768&lngWId=5
--
-- Name: Is SP running
-- Description:This user defined functio
-- n will tell you if a stored procedure is
-- currently running. When you have a long
-- running stored procedure executing you m
-- ay wish to stop a second instance of it
-- from being started. This function with t
-- he example will show you how. js
-- By: Jonathan Spinks
--
--This code is copyrighted and has
-- limited warranties.Please see http://
-- www.Planet-Source-Code.com/vb/scripts/Sh
-- owCode.asp?txtCodeId=768&lngWId=5
--for details.
--**************************************
--
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnSPisRunning]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnSPisRunning]
GO
create function fnSPisRunning (@objid int)
returns bit
/*
Developer:Jonathan Spinks
Date: 01/10/03
Description: Tells you if a stored procedure is currently running.
Inputs:
@objid = the object id of the stored procedure you wish to test
Return:
0 = stored procedure is not running (or does not exist)
1 = stored procedure is currently running
Example usage:
if dbo.fnSPisRunning(object_id('LongSP')) = 0
Exec LongSP
Else
Print 'LongSP is already running'
Copyright © 2003 I.S. Software Developments. WASH
*/
begin
declare @return bit
declare @syscacheobjects table (
cacheobjtype nvarchar(17) not null,
refcounts int not null
)
insert into @syscacheobjects
select cacheobjtype, refcounts
from master.dbo.syscacheobjects with (nolock)
where objid = @objid
anddbid = db_id()
if isnull((select refcounts
from @syscacheobjects
where cacheobjtype = 'Compiled Plan'),0)
=
(select count(*)
from @syscacheobjects)
set @return = 0 --Not running
else
set @return = 1 --Running
return @return
end
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply