July 8, 2014 at 11:13 am
Hello,
Is there anyway to get who add a datafile to my database (Profiler, default traces, etc.)
Thanks and regards
July 8, 2014 at 11:40 am
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
Consultant - Straight Path Solutions
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
July 8, 2014 at 11:59 am
This script should help you find it.
/* This has not been Tested on a CS Collation */
DECLARE @Path VARCHAR(512)
,@StartTimeDATETIME
,@EndTimeDATETIME
/* These date ranges will need to be changed */
SET @StartTime = '2014-06-13 11:00:00'
SET @EndTime = '2014-07-13 23:59:59'
SELECT @Path = REVERSE(SUBSTRING(REVERSE([PATH]),
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,te.name AS TraceEvent, tc.name AS EventCategory,spid
,tv.subclass_name
,CASE gt.EventClass
WHEN 46 THEN 'CREATE'
WHEN 47 THEN 'DROP'
ELSE 'OTHER'
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 te.name 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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply