October 10, 2008 at 1:30 pm
Is there a way that anyone knows of to find out when the last time any given stored proc or view has been executed?
I have a report server, and I would like to identify reports which are no longer being used, so I can do some clean up. I was thinking it would be helpful to have a way to identify how long ago a given view or proc had been executed.
I have not found any obvious (to me at least) ways to do this, am I just missing it, or is there really no way to know?
Thanks!:alien:
October 10, 2008 at 2:27 pm
Not sure since I don't have a sql 2000 version of RS, but it seems to me you should be able to query your reportserver database (the one that contains all of your settings for the Rs instance) and be able to see last execution time.
In 2k5 it'd be something like
SELECT c.[Name], e.[TimeEnd]
FROM executionlog e
INNER JOIN [Catalog] c
ON e.[ReportID] = c.[ItemID]
-Luke.
October 11, 2008 at 8:40 am
No way to know directly. If you need that info, you'll have to add a piece of code to each proc to log when it last ran. Can't do that for a view though.
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
October 13, 2008 at 9:23 am
Thank you for the answers so far. It is actually not a reporting services server, just a data warehouse, so the first suggestion won't work.
Gila, I had considered a code snippet to log a run date sort of thing. I was having trouble thinking on how to do that with views, as you pointed out.
It seems to me such a question would be somewhat common, that is knowing when and how often various select statements occur. Of course, I have been wrong often enough before. 😀
October 13, 2008 at 9:35 am
You could use my method, which requires some goodwill.
Run Profiler for a few weeks, get a distinct list of procs being run. Now go through the other procs, note which ones you think aren't being used and rename a few of them. I'd send out a note to all users that you're cleaning up the system and if there are issues, let you know, you'll restore the proc.
Do a few every week and eventually you'll know which ones are dead. I'd say that you need to keep them around for about 13 months since some things are run once a year.
October 13, 2008 at 9:39 am
Steve, that is the idea which was just going through my mind.
Thanks!
October 13, 2008 at 10:02 am
David Lester (10/13/2008)
It seems to me such a question would be somewhat common, that is knowing when and how often various select statements occur. Of course, I have been wrong often enough before. 😀
Very common. I think I answer it every month or so. 😉
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply