Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Help : Who dropped objects from database?

Posted by blakhani on June 8, 2011


Sometime I have been asked that how to find out who dropped/altered/created table/stored procedure. Well, SQL 2005 has out-of-the-box solution to this problem. This works in SQL 2008 as well.

Schema Change History is the report which would give details. This report takes data from default trace which is enabled by default in SQL Server 2005 default installation. Below command will help you in finding current status.

USE master;
EXEC sp_configure ‘show advanced option’, ‘1’;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ‘default trace’;
GO

If its enabled you would see run_value as 1.

More about default trace can be read here

Schema Change History

If you want to see who dropped a database, you can right click on Server Icon and go to Reports > Standard Reports > Schema Changed History. This Reports output would changed based on where you right clicked it (on server icon or database name)

Since default trace will be maximum 100 MB in size you may not get the data which is pretty old. Every restart also creates new file. So, if your SQL Server has restarted five times after someone dropped the object, you will not be able to see it here.

SQL Server 2005 has many other “Standard Reports” which can be found in SQL Server Management Studio.

Enjoy!!!

<Tags> Who dropped my database, when database was dropped, someone deleted database objects, tables, procedures, list of dropped objects in SQL Server 2005, Schema Change history Report, see who dropped databases, how to find who dropped the database in SQL server 2005 objects dropped</Tags>

Cheers!
Balmukund

Advertisements

16 Responses to “Help : Who dropped objects from database?”

  1. Priyanka said

    Nice..article…can’t it possible to get deleted db name after restarted sql server more than five times.

    i want deleted db name my db may be deleted before a month??is it possible if yes then pls help me for this

    • blakhani said

      Hi Priyanka,
      Thanks for reading the post. (Sorry for delay as I was on vacation) As you already noted that default traces would not be an option. Here are the other ways I can think of.
      1> If you want name then you can look for “Starting up database” in ERRORLOGs and see if you have old file (mostly this would not work unless you have increased default number of errorlog files from 6 to higher number, because they are also like trace files
      2> “Starting up database” in also written in Event log and there are better chances to find it there.

      Hope this helps.
      Balmukund

  2. Krishna said

    Yes, Its really good article. Here i am facing one issue.

    It was already enabled the default trace flag and i have altered some command. These records are showing in Trace flag but it doesn’t show in Schema Changed History.

    I have tried to disabled and enabled the trace flag but still it is showing empty report

    Please can you help me.

  3. Mukesh said

    This is exactly what I wanted.
    Cheers

  4. Siva said

    I checked default trace is enabled and it is not listing any trace when I select from Server and Database level. run_value is 1.Thanks in advance.

    • blakhani said

      Traces run in rollover mode and recycles on SQL restart. If it’s old data or SQL is restarted multiple times then no data would be shown.

  5. Pradip nikalje said

    It works..Thanx…!!!

  6. Bhoj said

    It works fine. Thanks !!

  7. […] time ago I blogged about Who dropped objects from database and it was very useful for many readers. Few days back someone asked me via Facebook – “Is it […]

  8. sunppatel said

    It is inbuilt report for viewing a updated objects. It is good. Thanks !!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: