Find last time a stored procedure was used

Hi,

No, there is no way of doing this. See https://www.experts-exchange.com/questions/23957155/Show-date-stored-procedure-was-last-used-in-ms-sql-2005.html

Thanks,

Lee

>>The problem is, there are so many that I can't possible go through the whole application and find which stored procs are really being used<<
Why not?  How difficult can that be? In the time it took to compose your question you could have written a script to search through your application code and mark (optionally DROP) each Stored Procedure. And it is even easier if you are using a standard notation for your Stored Procedures.

There is nothing in the database storing that information.


you can set up a trace or SQL profiler to monitor the SP:Starting event (that is the Stored Procedure Starting event), or, with only 250 of them simply add a one liner at the beginning to create your own log.

for example:

--    insert log_sp_routines (sp_object) select OBJECT_NAME(@@PROCID)
 
-- for example :
--
-- step 1 - once off event - create a log table
--
create table log_sp_routines (id int identity primary key clustered, sp_object varchar(100), sp_rundate datetime default getdate())
go
 
--
-- step 2 - once off event - create a sample stored procedure using our "one-liner"
--
create procedure usp_test_sp_log
as
begin
    insert log_sp_routines (sp_object) select OBJECT_NAME(@@PROCID)
end
go
 
--
-- step 3 - execute that stored procedure
--
usp_test_sp_log
go
 
--
-- step 4 - check out what happened.
--
select * from log_sp_routines
go

Select allOpen in new window

as per posts above use logging code (or check logs if you have) ; sql profiler or 3rd party tools: such as Idera's sqldm:
but all these for future...
...
rename "unknown" sp: 1-by-1 wait some time: if there are no need in the proc :script it and delete;
also : you can open procs and see: if there is not some object anymore: and proc will for sure fail- you need script -delete:
but start with some email\question about the objects..

I would go with using SQL profiler to trace database usage over a period of time. Just track stored procedure calls and log it to a database table. Then later check the trace table for a list of distinct stored procedure calls.

Hi,

The trace could take a long time and also is not reliable as you may miss some sp's that are not so often called. You could optionally go through each sp and add a log out to a table to say what sp it is and when it was ran, but this suffers from the same problem as using a sql trace. You may miss some that are not used often. The only way is to search through the code and look for sp calls. Hopefully your code uses a standard function to call any sp. Even this option suffers from the problem whereby if one sp is not called directly from your code but is referenced by another sp.

As I said in my post, there is no way to do it without much leg work. As for the response by acperkins... well it would not be simple to do if tehre were many hundreds of sp's in the system spread across several applications. In the 2 minutes it took to write your question I would seriously doubt it could have been done as you have no idea what his code base is and how the system is written. At the end of the day SQL does not give you any method of seeing when an sp was last used. This would need to be written into each sp. A SQL trace would ineveitably miss some; or at least you cannot guarantee you would find all of them. Same goes for searching through the code using something like a shell script. Many systems I have come across do not have the SQL code stored on the filesystem as it is in the database.

The only real way you could do it is to do some manual work which would first involve you exporting a list of all stored procedure names. Something like this in SQL Management Studio:

select * from sysobjects where type = 'P'

You could further filer this by excluding system sp's beginning with dt_ with a where clause:

select * from sysobjects where type = 'P' and name not like 'dt_%'

The you could save this list as a text file and then use a Windows based grep tool http://www.wingrep.com/ (I'm assuming you're code is on a Windows machine) to search for all strings listed in the list you just got from the SQL statement. You would also need to search through the sp's to see if any of them are referenced by each other.

Create a comma separated list of sp names in the system and then issue this command in the database:

  select *
    from information_schema.routines
   where specific_catalog = 'preactcrm'
     and specific_name not like 'dt_%'
     and routine_definition like ('','')

replace the empty list, ('','') with your list of sp names.

Hope this helps.

Lee

Can still miss things that way as well - what about procedures called remotely ? Or applications and jobs existing on other servers ? There is no way to be certain that you have trapped all the different avenues which can fire a stored procedure.

In all reality the only way to be sure is to review each and every one. The way to minimise which ones to review are to exclude the ones that are run regularly, and for that either the audit or the trace will do fine - would be inclined to run through a complete month cycle to include those "end of period" items.

But, as I have, there can be monstrously important highly infrequent routines such as yearly routines - one of my customers has one that runs at the end of January, so has only been run 1 time in the past 23 months. And it is those types of procedures that inadvertantly get "cleansed" based on frequency that can be disasterous when needed to run.

For that reason, you will have to review all those procedures that are not immediately obvious, and in doing so, can add the much needed documentation to the top of each and every procuedure that makes this type of request a little less daunting.

Now, you have an audit example, so, here is a trace example - note you have to restart the trace when server is reset... and there are ways of doing it, but for now, just the basics...

-- first up see what traces are running...
SELECT * FROM sys.traces
 
-- now setup for a new trace
declare @traceid int
declare @max bigint
set @max = 10   -- set at 10 megabytes per trace file, the filecount = 5 says keep / rollover upto 5 of them.
 
-- Create a new Trace
exec sp_trace_create @traceid output,@options=2,@tracefile=N'c:\audit_files\sql_trace_stored_procedures', @maxfilesize=@max,@stopTime=NULL,@filecount=5
 
-- Audit SP events eventclass = 42 = SP:Starting might also be interested in 44 = SP:StmtStarting -- see sp_set_event in books online...
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 42, 6, @on    -- 6  = NTUserName
exec sp_trace_setevent @TraceID, 42, 7, @on    -- 7  = NTDomainName
exec sp_trace_setevent @TraceID, 42, 11, @on   -- 11 = Starttime
exec sp_trace_setevent @TraceID, 42, 13, @on   -- 13 = Duration
exec sp_trace_setevent @TraceID, 42, 34, @on   -- 34 = ObjectName
exec sp_trace_setevent @TraceID, 42, 35, @on   -- 35 = DatabaseName
exec sp_trace_setevent @TraceID, 42, 64, @on   -- 64 = SessionLoginName 
 
-- Filter out the SQL Profiler events
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
 
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
 
 
-- then later on...
 
declare @traceid int
declare @tracefile varchar(200)
SELECT @traceid=id, @tracefile=path FROM sys.traces WHERE path like '%trace_stored_procedures%'    -- or simply select * and fill in the values below for name and traceid
EXEC sp_trace_setstatus @traceid,@status=0           -- 0 = stop, 1 = start, 2 = close and delete trace
 
SELECT distinct objectname FROM fn_trace_gettable(@tracefile, default) where eventclass = 42
 
-- then clean up and delete
EXEC sp_trace_setstatus @traceid,@status=2           -- make sure it is stopped first.

Select allOpen in new window

Apologies the code snippet suggest reading sp_set_event which of course should be sp_trace_setevent : http://msdn.microsoft.com/en-us/library/ms186265.aspx

It seems to me that using profiler to cature SP calls to a trace table will do exactly what is required - ie identify the stored procedures that are invoked. Yes it could take a while. You are going to need to run the trace over an extended period as there is no capability to look back in time to a period before the trace. (if you want to know what as been run over a 6 month period then you are facing 6 months of tracing) Key will be tracing only those events that are relevant and minimising the column data that is retained.

I like the code snippet posted by Mark Wills (I usually use the gui) but I would still suggest sending the trace results to a SQL table rather than a file - just for ease of querying afterwards.

once you have a list of recently used stored procedures you can compare to the list of all stored procedures for your schema:

use {dbname}
select specific_name from Information_schema.routines where Routine_type = 'PROCEDURE'
go

Now about those unused procedures - just make sure you archive them before deleting them and some code might only be used once a year depending on the nature of the calling application. It would be a pity to see end of financial year operations grind to a halt owing top an "unused" stored procedure.

Wow.  Great posts & great information - it will take me a little bit to read through this.  Great stuff!  Thanks!

Br

In a different question, I was just reading about the default trace that always runs in SQL Server 2005.

Here is an associated article: http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/ that explains how to utilise it.

Now I don't know if stored procedure calls are included in events traced by this process but it would be worth checking. If so you could use a variation on Mark_Wills' code to query it. This would allow you to query past history so you wouldn't have to wait for data to accumulate.

I would awrd points to http:#a23290787 and http:#a23293054

hmmmm.... thanks volumeIII

I was hoping to receive some kind of acknowledgement - next time I will remember that you don't need code, just some high level thought :)

We can only hope that eridanix does the decent job of asking a Moderator that the points be re-allocated.

Well, I guess not.  That is too bad.

not surprising... BTW got my MVP :)

Yay!  Welcome to the club!