Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
zeeshan malik-467318
zeeshan malik-467318
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Andras Belokosztolszki
Andras Belokosztolszki
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 1585
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 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
zeeshan malik-467318
zeeshan malik-467318
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Andras Belokosztolszki
Andras Belokosztolszki
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 1585
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
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17647 Visits: 32271
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47427 Visits: 44405
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, MVP, M.Sc (Comp Sci)
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


Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17647 Visits: 32271
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
prabhu.st
prabhu.st
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 309
@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
appuraj
appuraj
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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'


CoolAppu
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search