Find a deleting table

Question

Find a deleting table

dba_pkashyap

Hall of Fame

Points: 3336
More actions
January 14, 2010 at 8:00 am

#144094

Is there any way I can find the details / info about a deleted table(s) other than by auditing or creating a Database trigger and logging them.
Something like info saved in a table in msdb / any system views.
Regards,
Pavan.

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

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

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 1

You can query the INFORMATION_SCHEMA.TABLES view and see if there is a difference in the count which would mean tables having been dropped. Not sure if you can exactly pin point to the table name that was dropped unless you saved the results periodically.

Thanks..

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

dba_pkashyap Hall of Fame Points: 3336 More actions · Answer 2

Thats Fine.

My scenario is we have a found a temp table being dropped on one of the databases.(the table is recreated and all fine)

I am trying to track who did it and when it was deleted.

Pavan.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You can query the default trace, provided it wasn't deleted too long ago. Default trace is max 5 files of 20MB each, found in the error log directory, can be opened with profiler.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dba_pkashyap Hall of Fame Points: 3336 More actions · Answer 4

Thanks

Hmm, I can find the object which was re-created but not when it was deleted!!

Pavan.

dba_pkashyap Hall of Fame Points: 3336 More actions · Answer 5

It looks like someone accidentally deleted thru executing a procedure or sort of (something like forgetting to comment that line) and no one remembers it now.

Pavan.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

dba_pkashyap (1/14/2010)
Thanks
Hmm, I can find the object which was re-created but not when it was deleted!!

Then it's likely it was too long ago. It will have been logged in the default trace however, as I mentioned, the default trace only keeps 5 files of max 20 MB each, so older data is discarded.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dba_pkashyap Hall of Fame Points: 3336 More actions · Answer 7

The trace and the data are available till the early days of this month and the table was deleted on 13th as the restored copy of 12th has the table and the app was fine during the health check on 13th and failed for the same today morning. (table missing on 13th backup)

When i tried searching on tmp_% <table_name>, I was able to find the table creation date with the event as object:created, where as searching for the object:deleted and other trials doesnt give me the desired result.

Any suggestions on searching the deletion operation.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 8

Try:

SELECT

*

FROM ::fn_trace_gettable('TraceFilePath\FileName.trc', DEFAULT)

where objectname = '%Dropped Table Name%'

and eventclass=47--deleted

MJ

rspskumar-704119 SSC Veteran Points: 232 More actions · Answer 9

Please create a view with schema binding option on problem table so that u came to know what is going on.