What is executing my Stored Procedure?

Question

Post reply

What is executing my Stored Procedure?

barryFS

SSC Eights!

Points: 972
More actions
October 6, 2010 at 4:55 am

#157239

I have a stored procedure, which is inserting rows into a particular table, I know that it runs once a day, but I cannot find the SQL Agent job that is running this SP.
How can I find out what is executing the SP?

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

If it is sqlagent have a look at the sysJobSteps table in msdb

http://msdn.microsoft.com/en-us/library/ms187387.aspx

Clear Sky SQL
My Blog[/url]

barryFS SSC Eights! Points: 972 More actions · Answer 2

Thanks for the reply Dave.

I have checked dbo.sysjobsteps where command contains the SP name, but I'm not getting any results back.

So if it is not an SQL Agent job, where else can I look?

Thanks

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Is it being called by another SP ?

take a look at the INFORMATION_SCHEMA.ROUTINES view.

Clear Sky SQL
My Blog[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 4

barry can you edit the procedure? I would add a simple but detailed auditstatement to dump the snippet below into a table in master or call sp_send_dbmail, so you could track down where and when this is occurring;

I'd try to gather this info from within the proc:

SELECT

getdate() as EventDate,

DB_NAME() As dbname,

HOST_NAME() As hostname,

APP_NAME() as applicationname,

OBJECT_NAME(@@PROCID) as procedurename,

USER_ID() as userid,

USER_NAME() as unsername,

SUSER_ID() as suserid,

SUSER_SNAME() as susername,

IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

IS_MEMBER('db_owner') AS [Is_DB_owner],

IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],

client_net_address as ipaddress

from sys.dm_exec_connections

where session_id = @@spid

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

What about setting up a server side trace to monitor for that query? You can capture who is calling it, when, from where, pretty much everything you need.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

grahamc SSCertifiable Points: 6762 More actions · Answer 6

possibly a windows scheduled task? Running a SQLCMD statement?

is it possible that the SP is triggered from something else?

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 7

barry.smallman (10/6/2010)
I have a stored procedure, which is inserting rows into a particular table, I know that it runs once a day, but I cannot find the SQL Agent job that is running this SP.
How can I find out what is executing the SP?

Hi

Check with depends(sp_depends), this sp might be inner sp for some other sp.If the primary sp executes the inner sp(yours) will also executes

Thanks

Parthi

Thanks
Parthi

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

parthi-1705 (10/6/2010)
barry.smallman (10/6/2010)
I have a stored procedure, which is inserting rows into a particular table, I know that it runs once a day, but I cannot find the SQL Agent job that is running this SP.
How can I find out what is executing the SP?
Hi
Check with depends(sp_depends), this sp might be inner sp for some other sp.If the primary sp executes the inner sp(yours) will also executes
Thanks
Parthi

That's an older approach to checking for dependencies and doesn't always work that well. Using sys.dm_sql_referencing_entities works better. Not perfectly, but better.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Derrick Smith SSChampion Points: 10833 More actions · Answer 9

Or do

select top 10 * from syscomments

where text like '%spNameHere%'

to see if it's called from any other SPs.

winash SSChampion Points: 11446 More actions · Answer 10

I'd recommend sys.sql_modules - something like

SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE charindex('spNameHere',definition)>0;

The text column in syscomments has a limit of nvarchar(4000) and SPs with definitions greater than that size will be trimmed to that limit...the definition column in sys.sql_modules is nvarchar(max)

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 11

If you right click on the store proc --> properties, under programmability.

See for exec as to check user or sql agent atleast then run the trace

I think helpful.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

barryFS SSC Eights! Points: 972 More actions · Answer 12

Hi All,

I have made a massive blunder here.

I have posted this query in the wrong forum.

My instance is a SQL Server 2000 instance NOT 2005.

Sorry.

With this in mind, which of the suggested methods still hold true?

winash SSChampion Points: 11446 More actions · Answer 13

Which of the steps did you try? The ones that fail will be SQL 2005 specific 🙂

Bad jokes aside - on SQL 2000 the recommendations that involve sys.dm_exec_connections, sys.sql_modules, sys.dm_sql_referencing_entities will not work as these objects are present in 2005.

Did you try Grant's suggestion of using a server side trace to capture how the SP is getting executed?

barryFS SSC Eights! Points: 972 More actions · Answer 14

Hi,

Yes, I tried amending the SP to query the sys.dm_exec_connections DMV as suggested in the reply by Lowell!!

When the stored procedure failed to run this morning, I started investigating why it couldn't find the DMV, only to discover that this is a SQL Server 2000 instance!

(I have an excuse - I've only been here a week, so I'm still learning what instances exist)

I shall have to look into using the server side trace approach, but that will take some investigation, as I have never set one up before.