Deleted store procedure
-
Someone deleted bunch of store procedured, is the anyway I can fin out who did it?
-
You can try the default trace, and see if it's still in there. http://www.sqlservercentral.com/articles/64547/
otherwise, no. You need to be looking for this activity with a trace to catch it.
-
It happend yesterday, how long trace would keep what happend in past?
-
The default trace keeps a certain amount of data. It's not based on time, but does it matter? Try it and see if the data is there.
The only other alternative, and I'm not sure if this would be there, would be to buy a log reader tool, like Apex log from apexsql.com. That can read your transaction log or log backup.
-
the SSMS GUI has a built in report that taps the default trace , filtered by database name; it's a bit easier to use that querying the default trace directly:
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! -
Nice suggestion Lowell.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Very cool, Lowell. Certainly easier than scripts to start with.
-
I only have CustomReports option, do you know why? Thank you
-
I had the same issue, I am downloading and installing now.
-- Cory
-
I did theonly thing I don't know how to do Run the Setup.sql file, how can I run it?Thank you
-
Krasavita (5/18/2010)
I only have CustomReports option, do you know why? Thank youWhat version are your client tools - and what version are you connecting to? Those reports are not available if you are connecting to a 2000 instance and my not be available on earlier versions of 2005.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Jeffrey Williams-493691 (5/18/2010)
Krasavita (5/18/2010)
I only have CustomReports option, do you know why? Thank youWhat version are your client tools - and what version are you connecting to? Those reports are not available if you are connecting to a 2000 instance and my not be available on earlier versions of 2005.
I have had issues with those reports on Pre-SP1 2005, so I would recommend at least being on 2005 SP1.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Krasavita (5/18/2010)
It happend yesterday, how long trace would keep what happend in past?The default trace is limited to 20MB, but the last 5 trace files are kept. Hopefully there haven't been too many schema changes or server restarts as these also roll the files over.
This code will help you get the info:
1) Find the Trace File location and number:
select * from ::fn_trace_getinfo(0)
Returns among other things, something like this:
C:\Program Files\Microsoft SQL Server\MSSQL9.SQL2005\MSSQL\Log\log_40.trc
2) Use this result in the code below, available logs based on the above result should be log_36 - Log_40.
Also change "MyDatabase" as appropriate.
SELECT loginname, loginsid, spid, hostname, applicationname, servername, databasename, objectName, e.category_id,
cat.name as [CategoryName], textdata, starttime, eventclass,
eventsubclass,--0=begin,1=commit
e.name as EventName
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL9.SQL2005\MSSQL\Log\log_40.trc',0)
INNER JOIN sys.trace_events e ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id
WHERE databasename = 'MyDatabase' AND objectname IS not NULL
Leo
Striving to provide a better service.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated. -
it is 2000.Thank you for your help
-
Default trace doesn't capture Stored Proc create & drop ... according to my test just now. The default trace never seems to have what I need, so I set up my own server side trace to track things.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply