January 15, 2009 at 3:23 pm
hello,
i see few tables are been dropped from the db..i saw an article on this website..ran the script but there was no result..just blank column names..when i try to open the default trace says not accessible..i also did a schema change report on the db and didnot find any changes regarding those tables..someone please say me how to find who dropped the tables..please hlep me..
January 15, 2009 at 3:30 pm
If you don't have a log analyzer tool AND your db is in full recovery mode you are out of luck 🙁
* Noel
January 15, 2009 at 3:51 pm
Try to get the info from the trace...
1. RUN this Script to get the path of your trace file
SELECT * from ::fn_Trace_GETINFO(0)
2. Replace the following .trc with your TRACE files found in the above path, make sure you include the whole path and the filename.
Run the latest trc file file first, then going back..
SELECT textdata, ntusername, hostname, loginname FROM ::fn_trace_gettable('PUT YOUR TRACE FILENAME HERE.trc',0)
where textdata like '%drop%'
January 15, 2009 at 4:38 pm
You should be able to go through the default trace:
http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
January 15, 2009 at 6:12 pm
thanks...i will try this tomm and let you know.,..thanks..
January 15, 2009 at 7:05 pm
hey i tried on my laptop..it doesnt shows the table which has been dropped.....is there anyway to find the table name also..thnx
January 16, 2009 at 7:38 am
Nothing is found probably because the traces for those dropping operations have been cleaned up..which is bad.
Did you just try one trace file? there are usually at least 3 under the folder.
The trace files .trc under that path %\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ are cleaned up automatically.. suggest you copy all the available ones to a new folder before the historical traces get wiped out... and then put them into your ::fn_trace_getTable function one by one.
If you know which DB u are looking at, you could use its DBid in your WHERE clause to narrow down the search scope.
January 16, 2009 at 8:04 am
try this; it gets the current default trace file, and gives the objects/created/altered/deleted.
this might help you:
[font="Courier New"]
-- obtain file name for Default Trace
DECLARE @TraceFileName NVARCHAR(256)
SET @TraceFileName = (SELECT path FROM sys.traces WHERE is_default = 1)
-- get general name for Default Trace (remove rollover number)
SET @TraceFileName =
SUBSTRING (@Tracefilename, 1, (CHARINDEX ('\log_', @tracefilename) + 3)) + '.trc'
-- sample query: get info about recently added, deleted and modified
-- stored procedures in a database
SELECT
ev.name
,tr.StartTime
,tr.DatabaseID
,DB_NAME(tr.DatabaseID) AS dbname
,tr.DatabaseName
,tr.ObjectID
,tr.ObjectName AS 'Trace ObjectName'
,o.name AS 'Sysobjects Name'
,o.type_desc
,tr.ObjectType
/* see 'ObjectType Trace Event Column' in BOL to get a translation of these type codes */
,sv.subclass_name AS 'ObjectAbbrv'
,tr.LoginName
,tr.HostName
,tr.ApplicationName
FROM fn_trace_gettable(@TraceFileName, DEFAULT) tr
JOIN sys.trace_events ev
ON tr.eventclass = ev.trace_event_id
JOIN sys.trace_subclass_values sv
ON tr.eventclass = sv.trace_event_id
AND tr.ObjectType = sv.subclass_value
AND sv.trace_column_id = 28
LEFT JOIN sys.objects o
ON tr.ObjectID = o.OBJECT_ID
WHERE starttime > '2008-06-30 11:00'
--and databaseID = db_id(@dbname)
AND eventclass IN (46, 47, 164) -- object created, deleted or altered
AND eventsubclass = 1 -- only committed act [/font]
Lowell
January 16, 2009 at 8:29 am
I know this might not be the gearhead way to do this, but the Schema history report does the accessing of the default trace for you. Just simply right-click on the DB name in SSMS, go under reports, and voila.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 16, 2009 at 8:37 am
That's very cool! Matt. Didn't notice the report there until today. Thanks!
January 16, 2009 at 3:50 pm
thanks everyone for taking time and answering my question....when i right click on db and under custorm rpts/ schema schanges history..that gave me everything...it is just that i think it takes two days to get update...thanks again..
January 16, 2009 at 10:26 pm
iqtedar (1/15/2009)
hello,i see few tables are been dropped from the db..i saw an article on this website..ran the script but there was no result..just blank column names..when i try to open the default trace says not accessible..i also did a schema change report on the db and didnot find any changes regarding those tables..someone please say me how to find who dropped the tables..please hlep me..
Without Trace or DDL auditing u can find who and when it was dropped..
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply