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, Evt.name as Event_Description, Col.name 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


image

Example:

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

 image

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

Usage
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

    image
  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.

12 comments:

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

Anonymous 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

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.

Unknown said...

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

Unknown 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

24Layouts said...

Thanks For Sharing Such an Useful Information....

Vizag Real Estate

Unknown said...

thanks for sharing such an informative post...

Vijay Devarakonda Height

Subhagruha Projects said...

Excellent blog. Lots of useful information here, thanks for your effort!
Gated community Plots in Vizag

nani said...

Needed to compose you a very little word to thank you yet again
regarding the nice suggestions you’ve contributed here.
oracle training course in Chennai
ASP.NET Course In Chennai
Best C # .NET Training Institute in Chennai