How can i know which stored procedure is executed when and by which program?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Strategies » How can i know which stored procedure is...

How can i know which stored procedure is executed when and by which program?

Rate Topic

Display Mode

Topic Options

Author

Message

zeeshan malik-467318

Posted Monday, November 26, 2007 6:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 03, 2008 10:43 AM
Points: 3, Visits: 13

I want to know which stored procedures are executed in the last 24 hour in my database and i wish if i could also know about the program who called the stored procedure?
OR
I can know about a specific stored procedure that when it was executed last time and by which program?

Please help...its urgent?

Regards,

Zeeshan Malik

Post #425713

Andras Belokosztolszki

Posted Monday, November 26, 2007 6:29 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585

zeeshan malik (11/26/2007)

Unless you were running a trace, you will not be able to find this out Sad

. Transaction logs contain a lot of information, but they do not store information about stored procedure executions.

Regards,
Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Post #425719

zeeshan malik-467318

Posted Monday, November 26, 2007 6:35 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, June 03, 2008 10:43 AM
Points: 3, Visits: 13

ok Andras please give me some idea how to run a trace i don't know any thing about it? If you can give me some site URL for help or any thing...thanks

Post #425725

Andras Belokosztolszki

Posted Monday, November 26, 2007 6:42 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585

zeeshan malik (11/26/2007)

ok Andras please give me some idea how to run a trace i don't know any thing about it? If you can give me some site URL for help or any thing...thanks

The easiest way to set it up is to use "SQL Server Profiler". This is a GUI tool that comes with SQL Server, you can access it via the SQL Server 2005->Performance Tools-> SQL Server Profiler.

Note that it does come at a price, depends on what events you filter, the logging will cost in terms of performance and space usage. However, if you set it up, it will log the selected events and you will be able it find out who is using what.

You can read more about it in Books Online; http://msdn2.microsoft.com/En-US/library/ms191443.aspx and http://msdn2.microsoft.com/EN-US/library/ms191006.aspx are good starting points.

Regards,
Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Post #425730

Grant Fritchey

Posted Monday, November 26, 2007 7:49 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,371, Visits: 25,144

You can automate the use of SQL Profiler (also called Trace) either by using the correct stored procedures (sp_trace_create is the first of many, look 'em up in the BOL) or by setting up the trace that you like within SQL Profiler and then generating the scripts from there. Either way, assuming you collect just the default information, which will give you want you want, you won't place much of a load on your system. If you add tons and tons of counters, you can bring the system to it's knees. Start with the defaults and make adjustments as needed.

Since you're operating in 2005, you can also take a look at the Object Execution Statistics report. It doesn't tell you who called, but you can see quite a bit of other information. This gathers information from the dynamic management views such as sys.dm_exec_query_stats and others. You can look at those directly if you like.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #425766

GilaMonster

Posted Monday, November 26, 2007 8:21 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 37,651, Visits: 29,903

n 2005, you can see which queries are in the plan cache, along with the time the plan was put into the cache and the last time it was executed. It's not by any means guaranteed to show you all the queries that have executed, as plans can be removed from the cache.

You can try this query, but as I said, it may not show you all the stored procs run in the last 24 hours, just some of them. Once a plan is removed from the cache, all info on it is lost. If you need this info, look at running a server side trace to capture it.

select last_execution_time, dbid, objectid, text
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)
where statement_start_offset = 0 and objectid is not null and last_execution_time between getdate() and DATEADD(dd,-1,getdate())

You can use the object_name(objectid) function to get the name from the db id. If you're running SP2, use this query to get the name directly. (object_name was changed in SP2 so that it could take a second parameter)

select last_execution_time, dbid, object_name(objectid,dbid) as ObjectName, text
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)
where statement_start_offset = 0 and objectid is not null and last_execution_time between getdate() and DATEADD(dd,-1,getdate())

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #425804

Grant Fritchey

Posted Monday, November 26, 2007 8:25 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,371, Visits: 25,144

Good one. I hadn't thought about looking at the cache.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #425813

prabhu.st

Posted Saturday, November 17, 2012 12:27 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 2:51 AM
Points: 10, Visits: 33

@Gila-Monster

Hi,

I would like to give you a small correction in your code which is the right one, it is a small thing but needed.

in your query you have given the end-date-range as the start-date-range (in between clause in the where clause ), i am just correcting it.

--1st query correction
select last_execution_time, dbid, objectid, text
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)
where statement_start_offset = 0
and objectid is not null
---- Correction is in this line.
and last_execution_time between DATEADD(dd,-1,getdate()) and getdate()

--same correction in the second query too

Post #1385911

appuraj

Posted Saturday, May 11, 2013 12:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:22 AM
Points: 8, Visits: 27

select *--b.name, a.last_execution_time
from sys.dm_exec_procedure_stats a
--inner join sys.objects b on a.object_id = b.object_id
where DB_NAME(database_ID) = 'SQL_Admin_DB'

Cool

Appu

Post #1451848

« Prev Topic | Next Topic »

Permissions