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.

  1. How to effectively and immediately find out what query caused it?
  2. How to avoid it in the future? Will having SQL audit run on the machine help?


  • Welcome to Database Administrators - you may want to take the tour. – Max Vernon Nov 7 '18 at 2:59
  • Are you jumping to the conclusion that somebody deleted rows based solely on the size of a backup file? It could just as easily be that one is a full backup and the other is a log/diff. Or that one contains multiple backups and the other was performed WITH INIT. – Aaron Bertrand Nov 7 '18 at 4:15
  • Hi @AaronBertrand, not assuming... the table had about 300M rows and now its down to 700 k . So data did get deleted. – Sarah Nov 7 '18 at 17:22
  • Great, that wasn't clear in your question. It is a common misconception that backup size has some direct correlation with data size. – Aaron Bertrand Nov 7 '18 at 17:26

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.

  • Thanks Max, how can I see the transaction log for Deletes and Truncates? – Sarah Nov 7 '18 at 3:04
  • There are quite a variety of tools for the job, but SQL Server has a built-in function, fn_dblog, for that purpose. This search should get you started. – Max Vernon Nov 7 '18 at 3:09
  • Bunch of potentially useful info here (you'll have to change the [transaction name]) and here. – Aaron Bertrand Nov 7 '18 at 4:17
  • Thanks so much @MaxVernon. I actually tried running the " FROM fn_dblog(NULL, NULL) " on production where this happened and it just keep running and running, Not sure how safe is it to run without getting actual results after 3 minutes. I killed the query after like 3 minutes, is it safe to run for longer than 5 minutes. Will it increase the transaction log in any way. I kind of don't want to lose the db that has all the transaction log on it now. Please advice. – Sarah Nov 7 '18 at 17:24
  • Make sure you have a known good backup of the database and the log file - restore them onto a test/development server and do your fn_dblog activity there. Use BACKUP LOG <dbname> TO DISK = '<path_goes_here>' WITH NO_TRUNCATE; to backup the log without truncating it in production. Use WITH COPY_ONLY to backup the database without breaking your recovery chain. – Max Vernon Nov 7 '18 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.