Wednesday, July 14, 2010

How to find who dropped the database? Is that logged on SQL Server?


Are you one looking to find answer for questions like below.,

· How to find who dropped a database?

· How to find who created the database?

· How to find who altered the database?

· How to find who altered the database configurations?

· How to find who dropped the schema?

· How to find who altered the schema?

· How to find who altered the server configuration?

· How to find who modified the login?

· How to find who modified the table?

If it is then,  are you wondering is that possible on SQL Server?

Yes, SQL Server can beginning from version SQL Server 2005 onwards.

That is where SQL Server Default trace come into picture.

What is SQL Server Default Trace?

Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur.

To get the default trace options

SELECT * FROM ::fn_trace_getinfo(default)

To know what is audited on default trace

SELECT Trc.EventID, Trc.ColumnID, as Event_Description, as Column_Description

  FROM ::fn_trace_geteventinfo(1) Trc

    JOIN sys.trace_events Evt ON Trc.eventID = Evt.trace_event_id

    JOIN sys.trace_columns Col ON Trc.columnid = Col.trace_column_id



Below is the screenshot  “Schema Changes History” of Standard Reports at Server Level.

See that “it shows the event of Drop and Alter of the database


The same can be queried from SQL Server Default Trace like;

select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\log.trc',DEFAULT)
where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2

Default trace can be queried to find answers on

  1. To finmd the Create / Alter / Drop i.e., DDL changes at Database,Schema and Object level
  2. To find the configuration changes at SQL Server Instance, Database, Schema and Object Level
  3. To find Login creation , modifications and failures
  4. To find performance issues like missing of statistics, join predicate, hash or sort warnings
  5. Database file growth or shrinkage
  6. DBCC operations


Points to ponder

  1. Default trace is a Server Side trace and it is enable by default.
  2. Default trace properties can not be modified, other than enabling and disabling of this trace.
  3. Default trace by default enabled and stored on the “Log” folder

  4. Default trace file will be created newly on start of the SQL Server service.
  5. A single trace file can store  up to 20 MB, when it is full new file will be created.
  6. SQL Server would maintain 5 default trace file, where the file with highest number is the latest.
  7. Default trace is light weight and it is not replacement for DDL Trigger. Default trace log only basic information, when detailed information needed DDL trigger is the option.


Anonymous said...

很喜歡你的部落格 留言支持你 祝你人氣長紅~~~ ..................................................................

Anonymous said...

Very Useful one.

Siva said...

Good. But it is listing in query not in Report. how to know what operations are done on that particular object

select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_26.trc',DEFAULT)
where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2

FusionFuel said...

is it possible to find this even after deletion of log files?
if not whether any other way to find who dropped the db???

Priya Kannan said...

It's interesting that many of the bloggers to helped clarify a few things for me as well as giving.Most of ideas can be nice content.The people to give them a good shake to get your point and across the command .
SQL Server Training in Chennai

Gopal Ainavalli said...

Nice blog has been shared by you. before i read this blog i didn't have any knowledge about this but now i got some knowledge so keep on sharing such kind of an interesting blogs.

Weblogic Admin Training

Peter Johnson said...

Awe! What An Blog Very Helpful and interesting Really A great center for acquiring knowledge.Very Helpful Post And Explained Very Clearly About All the things.Very Helpful. Coming To Our Self We Provide Restaurant Equipment Parts .Really Thankfull For the blogger providing such a great information.Thank you. Have a Nice Day.

seo said...

Whatever We Are Gathering the information from the blogs,we have to keep on updating it,Urgent Care Clinic in Chicago.By getting them into one place.Really thanks for posting.Very Thankful for the Informative Post.

Lathika Honey said...

Good article. It is very useful for me to learn and understand easily USMLE Thanks for posting.

Lathika Honey said...

This Blog Provides Very Useful and Important Information. I just Want to share this blog with my friends and family members. Tibco Certification Training