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)