Is the DELETION of a SQL Server 2008R2 database logged anwhere?

Question

Is the DELETION of a SQL Server 2008R2 database logged anwhere?

Stephen Rybacki

Hall of Fame

Points: 3402
More actions
January 28, 2015 at 8:33 am

#294750

Had an instance where a development database was deleted by "persons unknown" last Friday while I was home sick. Is there anywhere such an event would be logged to show who (or what event) did that, and if so -- where?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Should be in the default trace, but that's only 5 files of 20MB, it gets overwritten quickly.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhushan Kulkarni SSCrazy Points: 2829 More actions · Answer 2

you can check the log file of master database. U can use

Select [Transaction SID],* from ::fn_dblog(null,null) where [Transaction Name] ='dbdestroy'

Stephen Rybacki Hall of Fame Points: 3402 More actions · Answer 3

GilaMonster (1/28/2015)
Should be in the default trace, but that's only 5 files of 20MB, it gets overwritten quickly.

Pardon my ignorance (I'm still learning) but what is the "default trace", or do you mean the SQL Logs?

Lowell SSC Guru Points: 323518 More actions · Answer 4

Siberian Khatru (1/28/2015)
GilaMonster (1/28/2015)
Should be in the default trace, but that's only 5 files of 20MB, it gets overwritten quickly.
Pardon my ignorance (I'm still learning) but what is the "default trace", or do you mean the SQL Logs?

a trace tracking Data Definition Language(DDL) statements is enabled by default on any SQL instance. you'd have to explicitly turn it off to lose it.

the default trace will have the whodunnit information, where the fn_log function will not have that.

but as Gail mentioned, the info in the trace is a rolling last 100 meg of changes...it can get aged off quickly.

there's an easy to get to GUI from within SQL server Management Studio, and you can query it directly as well.

--SELECT * from sys.traces

declare @TraceIDToReview int

declare @path varchar(255)

SET @TraceIDToReview = 1 --1 is the default trace, this is the trace you want to review!

SELECT @path = path from sys.traces WHERE id = @TraceIDToReview

SELECT

TE.name As EventClassDescrip,

v.subclass_name As EventSubClassDescrip,

T.*

FROM ::fn_trace_gettable(@path, default) T

LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id

LEFT OUTER JOIN sys.trace_subclass_values V

ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Stephen Rybacki Hall of Fame Points: 3402 More actions · Answer 5

Hey, thanks very much for this. I never knew, but I do now. Since it only came to my attention some 5 days later, whomever it was probably "got away with it" I guess. Wondering if it's possible to make the default bigger (longer) or create my own now so I guess I'll look into that. Thanks again to you and Gail.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 6

@Siberian - I've got an article being published next week here on SSC that you can use to keep track of those events - I believe it comes out on Monday, February 2nd. It outlines the process to permanently track specific events from the default trace files across all servers in your organization, storing them in a centralized table. I think it might be what you are looking for.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Siberian Khatru (1/28/2015)
Wondering if it's possible to make the default bigger (longer)

Nope.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Bhushan Kulkarni (1/28/2015)
you can check the log file of master database. U can use
Select [Transaction SID],* from ::fn_dblog(null,null) where [Transaction Name] ='dbdestroy'

Master is in simple recovery model, meaning the log gets truncated (marked inactive and able to be overwritten) regularly. Not much chance of picking up a log entry 5 days later.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass