Who Dropped Your Table? – SQL in Sixty Seconds #112

Who Dropped Your Table? - SQL in Sixty Seconds #112 112-whodroppedtable-ytcover-800x450 Who Dropped My Table? – I was recently asked this question in Comprehensive Database Performance Health Check. A very valid question indeed as my client was working on a table which was suddenly disappeared from the database. After a while, it was more shocking to see that their entire database where we were working disappeared. Thankfully, it was all happening on the replica server which we had created for the testing and development.

Now the problem which was in front of us was very unique. The table and database were indeed deleted by someone from their team only and probably they would have dropped it as they had no idea what this new database was actually for it. We were very confident that it was an innocent act by someone who had no idea we were working on their SQL Server Performance Tuning Project.

Now it was important that we figure out the person who dropped the table and inform them not to do that again. Here is a short video where I explain you can identify who dropped the table or database with the help of the standard reports available in the SQL Server.

There are two things to remember.

  • You need standard trace enabled on the server to capture information
  • If the server is very busy, the standard trace might get over-written

In this scenario, it is a good idea to keep on taking regular backup of the trace file.

If you find this information helpful, do subscribe to my youtube channel.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL in Sixty Seconds, SQL Reports, SQL Scripts, SQL Server, SQL Server Security
Previous Post
Single Column Single Row and TABLE SCAN – SQL in Sixty Seconds #111
Next Post
Execution Plan – Estimated vs Actual – SQL in Sixty Seconds #113

Related Posts

6 Comments. Leave new

  • HI @Pinal,
    I am big fan of your sqlauthority. I have a question.
    I am writing a procedure where I need to write some logic by using OUTER APPLY on Sub query. Here are the possible cases for that.

    1. OUTER APPLY (select Distinct 1 as ColAlias FROM Tabl1 INNER JOIN TABLE2 …) A
    2. OUTER APPLY (select Top 1 1 as ColAlias FROM Tabl1 INNER JOIN TABLE2 …) A
    3. OUTER APPLY (select 1 as ColAlias FROM Tabl1 INNER JOIN TABLE2 …) A

    So as per performance point of view, which of the above case is better. Looking for a quick response.

    Ashish Jain

  • Okay, it would be great if you can try for these possible cases as you are the right person who can do this POC for such cases.

    Thanks in advance :)

    • I actually can’t do that as it will be different for different schema and data. This one you will have to try out.

  • okay, Thanks I will try and will share the outputs :)


  • Mayur Aggarwal
    September 9, 2020 7:49 pm

    Hello , Is this possible to see who dropped your database instead of table. BY clicking on schema changes report it shows changes related to that database only


Leave a ReplyCancel reply

Exit mobile version