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)
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.
Thanks
Ashish Jain
It is not possible to answer without trying this one out.
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 :)
Thanks
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