SQL Server Performance Forum – Threads Archive

Is a procedure running?

Is there a way to check if a procedure is running through TSQL? I know that I can just pull up current activity and look. I need to write a procedure that runs every 5 minutes, but can’t have more than one instance of it running at a time. If anyone can point me in the right direction, I would appreciate it. Thanks in advance. Live to Throw
Throw to Live

You can use Profiler to check if your procedure is running.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Is there a way to check if a procedure is running through TSQL? Live to Throw
Throw to Live

YOu can schedule a job to run SP_WHO2 or adhoc query and output to a file every 5mins.
Open that output file every 5 mins to check the activity. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Maybe I ran it wrong, but sp_who2 doesn’t give me the procedure name, just what command is being ran. Live to Throw
Throw to Live

Use SP_WHO2 and see the SPID running the SP and use DBCC INPUTBUFFER to check what it is doing.
Ahttp://www.microsoft.com/technet/te…05/SystemTables/default.aspx?ShowStepTwo=true for your information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Refer this sp_who3 also
http://vyaskn.tripod.com/sp_who3.htm
Madhivanan Failing to plan is Planning to fail

Is there a reason that you don’t want to just use the SQL Server Job Agent and simply schedule whatever script you want to run every 5 minutes? Dalton

Assuming that you are purposely not using the SQL Server Job Agent, the following code elaborates on Satya’s suggestion. It isn’t exactly what you want, but I think it will show you how to use the SPID numbers and then pull the actual command that they are running. The @Command variable is what contains the actual command. Currently the code is just checking to see if there are any blocked processes, and then it will pull the actual text that was causing the blocking and insert the command into a table. (#BlockingProcesses is just a temp table created prior to this block of code.) You can loop through all of the sysprocesses and do the same thing, and if you find the command you are looking for you know it is running, if you don’t find it then you kick it off again. declare @count as int select @count = count(*) from #BlockingProcesses if @count > 0 begin declare @WhenHappened as datetime declare @spid as smallint declare @kpid as smallint declare @blocked as smallint declare @dbid as smallint declare @hostname as nchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> declare @commandtype as nvarchar(30) declare @command as nvarchar(255) declare curBlocking cursor for select * from #BlockingProcesses open curBlocking FETCH NEXT FROM curBlocking INTO @spid, @kpid, @blocked, @dbid, @hostname WHILE @@FETCH_STATUS = 0 BEGIN CREATE TABLE #inputbuffer (EventType nvarchar(30), Parameters int, EventInfo nvarchar(255)) declare @ExecStr as nvarchar(255) SET @ExecStr = ‘DBCC INPUTBUFFER(‘ + STR(@spid) + ‘)' INSERT INTO #inputbuffer EXEC (@ExecStr) select @CommandType = EventType, @Command = EventInfo from #inputbuffer drop table #inputbuffer insert into BlockingProcesses values (GetDate(), @spid, @kpid, @blocked, @dbid, @hostname, @CommandType, @Command) FETCH NEXT FROM curBlocking INTO @spid, @kpid, @blocked, @dbid, @hostname END CLOSE curBlocking DEALLOCATE curBlocking end go

]]>