January 26, 2012 at 7:45 am
I tried Paul Randall's suggested method:
http://sqlskills.com/BLOGS/PAUL/post/Finding-out-who-dropped-a-table-using-the-transaction-log.aspx
It uses this query to find the object_id of objects that were dropped.
SELECT [Transaction Id], [Begin Time], [UID], [SPID]
FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
GO
It didn't find anything in the active log. So I tried Paul's suggestion in the PS to turn on trace flag 2537,
allowing the function to see the entire log. Still no dice. Does anyone have any other ideas about how
I might be able to find the identity of my table-killer?
January 26, 2012 at 7:51 am
DROP TABLE counts as a DDL function,and would be in the default trace, if it didn't get rolled over:
fastest way is the built in reports in SSMS:
Lowell
January 26, 2012 at 7:53 am
Lowell (1/26/2012)
DROP TABLE counts as a DDL function,and would be in the default trace, if it didn't get rolled over:fastest way is the built in reports in SSMS:
Thanks, but how can I pick how far back that report goes? It's only showing since 8 AM this morning.
January 26, 2012 at 7:58 am
aurato (1/26/2012)
Thanks, but how can I pick how far back that report goes? It's only showing since 8 AM this morning.
server wide, it's only the last 100 meg of DDL changes(5 files of 20 meg each), so on a single server with a busy dev database that gets a lot of DDL events in one database, it can affect how much gets tracking across all databases.
unless someone has a better log reading suggestion, you might want to create your own traces to capture that int he future,and make sure they get recreated on server restarts.
Lowell
January 26, 2012 at 8:00 am
Alright. Thanks for your help. Have no idea how they did it either. Time to log it myself; lesson learned.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply