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.

5 comments:

盈廖生家秀蔡 said...

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

Zebra 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