last run of stored proc

Question

Post reply

last run of stored proc

don farrell

Mr or Mrs. 500

Points: 575
More actions
April 6, 2005 at 8:03 am

#113639

Im working on a new project and have found a production db with 1200 stored procs which have been created by various waves of developers over the past five years...looks like there has not been any change control or archiving of procs once they have been replaced.
Im looking for a way to determine the last execution of a stored procedure.
Assume I can get this from master, msdb ? Btw its sql 2k
Any recommendations most welcome

Viewing 11 posts - 1 through 10 (of 10 total)

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

nurit hadari Grasshopper Points: 13 More actions · Answer 1

you need to open a table called sysobjects in your database. it has 2 date columns: create date and last update date.

for every stored procedure last update date is the date it was last used.

regards

n. hadari

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 2

I'm loath to say WRONG...but I think it applies here....given that there is no "last update date" column in sysobjects (in SQL2K anyway). There is a refdate column, but I just ran a SP and it's value didn't change in sysobjects.

My assertion is also given on the basis that previous advice I've seen suggested strongly (from people I trust), that it can't be done (without effort).

However one way to do it (other than to execute Profiler over a prolonged time-frame)...is to create a 'generic log table' to hold details of each SP that get's executed. And edit each SP to INSERT a record in this table, detailing the name of the SP and the date/time is currently is being executed.

While this might seem like overkill, you MAY be able to write some DMO process to 'semi-automate' it for you. Given the scale of the task in front of you (and the houskeeping benefit afterewards) it could be worth it...I've seen code snippets before to do something similar that should be capable of being used as a starter kit. An additional upside would be that you would get a very accurate idea of the HOT SP's in your application, and thus be able to concentrate on fine-tuning them properly (something that can be got from Profiler as well)

don farrell Mr or Mrs. 500 Points: 575 More actions · Answer 3

Thanks for the posts guys...I had already confirmed that the refdate does not change b4 my initial post. I was hoping that I would not have to use the log table approach as there would be almost 1200 procs to update. And thats just one db, I have about 8 to work through.

Aslo, would have to go through a new long and drawn out change control process to implement.

I find it hard to believe that sql server 2k does not store a last run timestamp somewhere.

Again, any thoughts / comments most welcome.

Thanks in advance

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 4

"I find it hard to believe that sql server 2k does not store a last run timestamp somewhere."

Don't be.....

a) Microsoft software!

b) High activity systems would spend more time logging their actions than actually getting work done.

don farrell Mr or Mrs. 500 Points: 575 More actions · Answer 5

I edidnt want to mention point a on this board !

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

Since it's a potential audit/change control nightmare why not create one table with a few columns like:

sequence number (identity column unique)

database name

procedure namer

execution date

and just add one insert statement to this table at the beginning of each stored procedure in question. It might not take care of the omissions of the past, but may well satisfy auditing concerns of the future.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Calvin Lawson SSChampion Points: 11030 More actions · Answer 7

If you are only worried about procs that affect tables, you can set a trigger on each table that logs to another table based on the call "DBCC INPUTBUFFER(@@Spid) ", which lists the calling procedure for the spid (remember to use set nocount on before calling this and off after).

As for other types of procedures, you may just need to delete them all, then add them back when code breaks. In Dev, obviously...

Sounds like a nightmare...good luck!

cl

Signature is NULL

sqlemail@yahoo.com SSC Eights! Points: 919 More actions · Answer 8

I dont see the advantage of running "DBCC...". Can you please elaborate more.

paul

Doug Blystone Ten Centuries Points: 1144 More actions · Answer 9

I did something 'sort of' like this.

I profile and then suck that file into a table on a different server.

then I have a table with all the procedure names.

I join those in a view with a 'like' clause. (yuck), but then I index the view for speed...NICE...

This way I can count and group by procedurename. It can even show you 'infrequently' called procedures.

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 10

By my reading......if you have 1200 SP's and only 50 tables.....50 triggers might be a smaller task that would solve the same objective, than amending 1200 SP's.