What are some of the ways of knowing how the 100 M rows of record table got wiped out. Database is in Full recovery mode. I am not sure how doing daily backups are happening but before the truncate or delete happened, it was 50 GB and now down to 4 GB.
Guess my question is.
- How to effectively and immediately find out what query caused it?
- How to avoid it in the future? Will having SQL audit run on the machine help?
Unfortunately, there is no easy way to determine the DML command used without having some kind of auditing setup prior to the action.
You might be able to infer whether the action was a
DELETE or a
TRUNCATE by looking at the transaction log to see if pages were deallocated or if row data was written to the log. If the trnasaction log shows pages were deallocated you can be certain that the table was truncated.
If you had SQL Server trace or an Extended Events session running on the server at the time the delete occurred, and that trace/session is capturing DML statements, you’d be able to see exactly what happened.
Probably the more important consideration would be to ensure you have a Recovery Point Objective that serves the business by limiting the amount of time that can go by without a backup happening that you can use for recovery. So, with full recovery, you could be running log backups every 5 minutes, which would mean you could recover the table to a point just before the delete took place.
This blog post by Paul Randal has an excellent tutorial for inspecting a log backup.