Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can i know which stored procedure is executed when and by which program? Expand / Collapse
Author
Message
Posted Monday, November 26, 2007 6:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 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
Posted Monday, November 26, 2007 6:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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)
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


Unless you were running a trace, you will not be able to find this out . 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
Posted Monday, November 26, 2007 6:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 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
Posted Monday, November 26, 2007 6:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Monday, November 26, 2007 7:49 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 15,629, Visits: 28,014
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
Posted Monday, November 26, 2007 8:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
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
Posted Monday, November 26, 2007 8:25 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 15,629, Visits: 28,014
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
Posted Saturday, November 17, 2012 12:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 82, Visits: 210
@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
Posted Saturday, May 11, 2013 12:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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'


Appu
Post #1451848
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse