Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Check who add datafile to a database Expand / Collapse
Posted Tuesday, July 8, 2014 11:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 10, 2014 4:41 AM
Points: 77, Visits: 99

Is there anyway to get who add a datafile to my database (Profiler, default traces, etc.)

Thanks and regards
Post #1590443
Posted Tuesday, July 8, 2014 11:40 AM



Group: General Forum Members
Last Login: Today @ 12:55 PM
Points: 11,297, Visits: 13,083
It is in the default trace as an Object:Altered event with an object type of 16964 which is database. The default trace doesn't tell you that the alteration was the addition of a file, you need to have an idea of when it happened.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1590450
Posted Tuesday, July 8, 2014 11:59 AM



Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 21,733, Visits: 15,424
This script should help you find it.

/* This has not been Tested on a CS Collation */
,@StartTime DATETIME

/* These date ranges will need to be changed */
SET @StartTime = '2014-06-13 11:00:00'
SET @EndTime = '2014-07-13 23:59:59'

CHARINDEX('\', REVERSE([path])), 260)) + N'LOG.trc'
FROM sys.traces
WHERE is_default = 1;

/* Check for database creation or drop */
If number_files is specified as default, fn_trace_gettable reads all rollover files until it reaches the end of the trace.
fn_trace_gettable returns a table with all the columns valid for the specified trace.
SELECT gt.StartTime AS EventTimeStamp,gt.DatabaseName, AS TraceEvent, AS EventCategory,spid
,CASE gt.EventClass
END AS EventClass
,gt.LoginName, NTUserName, NTDomainName, gt.HostName, ApplicationName
,'DATABASE' AS ObjectType
FROM ::fn_trace_gettable( @Path, DEFAULT ) gt
INNER JOIN sys.trace_events te
ON gt.EventClass = te.trace_event_id
INNER JOIN sys.trace_categories tc
ON te.category_id = tc.category_id
INNER JOIN sys.trace_subclass_values tv
ON gt.EventSubClass = tv.subclass_value
AND gt.EventClass = tv.trace_event_id
WHERE 1 = 1
AND ObjectType = 16964 /* Database */
AND gt.StartTime BETWEEN @StartTime AND @EndTime
AND gt.EventSubClass = 1 /* Committed */
AND IN ('Object:Created','Object:Deleted','Object:Altered')
ORDER BY gt.StartTime

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1590455
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse