Last Store procedures used or executed

Question

Post reply

Last Store procedures used or executed

aluna

SSC Rookie

Points: 28
More actions
March 18, 2009 at 5:00 pm

#129901

Exist a way to know, What are the latest store procedure used or executed in the database? :-D.
I have a data base with 1781 store procedure's and some aren't used.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

SQL ORACLE

One Orange Chip

Points: 27807

March 18, 2009 at 6:39 pm

#962391

Set a trace profiler?

maechismo_8514 SSCoach Points: 17510 More actions · Answer 2

Do you know this wonderful tool Default trace?

You can enable it and script a table to load the trace files from the default trace to a custom created table. Then you can query your table when needed. It just makes your life easy.

About Default trace check this out:

http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

Lowell SSC Guru Points: 323518 More actions · Answer 3

default trace keeps track of when database objects, like tables/views/procs where created/modified or deleted.

it does not keep track of when they were executed or accessed.

you must create your own trace to do that.

it is not possible to discover without a trace in place.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

maechismo_8514 SSCoach Points: 17510 More actions · Answer 4

Oopps..thanks for correcting me Lowell. One of those days you see 🙁

Lowell SSC Guru Points: 323518 More actions · Answer 5

Keep at it Krishna;

I'd like to see a default Data Manipulation Language (DML) trace automatically running all the time as well as the Data Definition Language(DDL) trace;

just to be able to see who updated or deleted data would be a huge boon.

Something similar would be an automatic backup, so if a system is ignored for a week, it backs itself up. runs automatically unless someone stops it, same as the default trace.

yeah it'd eliminate and simplify a lot of jobs, but that's what progress is about. more time to do other things.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

aluna SSC Rookie Points: 28 More actions · Answer 6

Thank's

I activated Default trace, and I got information about the store procedure created, modified and deleted, buy this not is I need.

Then there isn't way to know the last store procedure executed :(.

Lowell SSC Guru Points: 323518 More actions · Answer 7

you need to add a different trace; a great example from Perry Wittle is found here:

http://www.sqlservercentral.com/Forums/Topic576752-146-1.aspx

AFTER that is in place, you will be able to determine who ran a SQL statement or called a procedure.

again, you cannot find out who did what PRIOR to this trace existing. it's nowhere to be found.

you will need to enable some extra columns to the trace to catch who ran what.

here's my suggestions, so you cna capture who,a dn also find long running/slow queries:

exec sp_trace_create @traceidout output, 2, N'C:\Data\mytrace', @maxfilesize, NULL

exec sp_trace_setevent @traceidout, 12, 1, @on --SQL:BatchCompleted,TextData Executed

exec sp_trace_setevent @traceidout, 12, 3, @on --SQL:BatchCompleted,DatabaseID

exec sp_trace_setevent @traceidout, 12, 6, @on --SQL:BatchCompleted,NTUserName

exec sp_trace_setevent @traceidout, 12, 7, @on --SQL:BatchCompleted,NTDomainName

exec sp_trace_setevent @traceidout, 12, 8, @on --SQL:BatchCompleted,HostName

exec sp_trace_setevent @traceidout, 12, 10, @on --SQL:BatchCompleted,HostName

exec sp_trace_setevent @traceidout, 12, 11, @on --SQL:BatchCompleted,LoginName

exec sp_trace_setevent @traceidout, 12, 12, @on --SQL:BatchCompleted,SPID

exec sp_trace_setevent @traceidout, 12, 13, @on --SQL:BatchCompleted,Duration

exec sp_trace_setevent @traceidout, 12, 14, @on --SQL:BatchCompleted,StartTime

exec sp_trace_setevent @traceidout, 12, 15, @on --SQL:BatchCompleted,EndTime

exec sp_trace_setevent @traceidout, 12, 16, @on --SQL:BatchCompleted,Reads

exec sp_trace_setevent @traceidout, 12, 17, @on --SQL:BatchCompleted,Writes

exec sp_trace_setevent @traceidout, 12, 18, @on --SQL:BatchCompleted,CPU

exec sp_trace_setevent @traceidout, 12, 26, @on --SQL:BatchCompleted,ServerName

exec sp_trace_setevent @traceidout, 12, 48, @on --SQL:BatchCompleted,RowCounts

exec sp_trace_setevent @traceidout, 12, 64, @on --SQL:BatchCompleted,SessionLoginName

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

maechismo_8514 SSCoach Points: 17510 More actions · Answer 8

Lowell (3/18/2009)
Keep at it Krishna;
I'd like to see a default Data Manipulation Language (DML) trace automatically running all the time as well as the Data Definition Language(DDL) trace;
just to be able to see who updated or deleted data would be a huge boon.
Something similar would be an automatic backup, so if a system is ignored for a week, it backs itself up. runs automatically unless someone stops it, same as the default trace.
yeah it'd eliminate and simplify a lot of jobs, but that's what progress is about. more time to do other things.

Ahh... that would be a Dream Machine which does everything for you and we should just sit and watch the dashboard:-)