Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
maquaro
Starting Member
6 Posts |
 Posted - 2005-07-05 : 16:22:12
|
| I am a DBA who "inherited" a large number of procedures when I first started. One goal my boss has is to lower the amount of procedures on our live database server and archive the unused procedures to another off-line / test database. Is there a way to obtain stored procedure usage information like:last time ran,last time modified,etcThanks in advance,Dale Offret Jr.maquaro@yahoo.com |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-07-05 : 16:41:41
|
| You can record the info using the profiler - or just place an insert into a trace table at the top of the sp.I create a tabletrace (id int identity, d datetime default getdate(), typ varchar(10), s varchar(2000))then in the sp at the topinsert trace (typ, s) select 'start', 'myspname'and at the endinsert trace (typ, s) select 'end', 'myspname'and often the parameters too.In that way you can also see how long they take.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-07-05 : 18:17:06
|
| there is now some auditing software that can track this kind of thing without needing to use profiler. This kind of software is now being sold for SOX auditing, but it could be used for what you want. I think that one might even be advertised here on SQLTeam. I think SOX auditing was discussed here not too long ago, you could do a quick search to find the links that were listed.-ec |
|
|
|
|
|
|
track stored procedure usage statistics