TimeFrames of previous executions of a particular stored procedure

• sweetheartcharan

  Grasshopper

  Points: 20

  More actions

  November 8, 2012 at 11:31 pm

  #393572

  I have a stored procedure called dbo.usp_CreateReport and if the previous execution timeframe of that stored procedure is 2012-10-24 16:11:07.443 and what is the query to retrieve the timeframe of 2nd,3rd,4th previous executions of same stored procedures?

• vinu512

  SSCoach

  Points: 15729

  More actions

  November 9, 2012 at 2:37 am

  #1557508

  You can run a trace using SQL Server profiler to get the execution times, but make sure you're not running the trace in the production environment.

  You can also do some manual Audit/Tracing as follows :

  --Creating an audit table

  Create Table Proc_audit

  (Id int Identity(1,1),

  Exec_Time DateTime )

  --Add the following code to your procedure

  ALTER Procedure dbo.usp_CreateReport

  As

  Begin

  DECLARE @start_time DATETIME, @end_time DATETIME, @exec_time DateTime

  SET @start_time = CURRENT_TIMESTAMP

  --

  SET @end_time = CURRENT_TIMESTAMP

  Select @exec_time = DATEDIFF(ms, @start_time, @end_time)

  Insert Into Proc_audit

  Values ( @exec_time)

  End

  Vinu Vijayan

  For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉