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

  • 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

  • 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

  • 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

  • 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

  • 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

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

  • 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
  • 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

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

  • @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

  • 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

Viewing 9 posts - 1 through 8 (of 8 total)

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