Finding out who dropped a table
-
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?
-
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
--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! -
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.
-
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
--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! -
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