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
Author
Message
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: Friday, November 7, 2014 9:31 AM
Points: 77, Visits: 100
Hello,

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:13 PM
Points: 10,342, Visits: 13,351
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:18 PM
Points: 17,964, Visits: 15,968
This script should help you find it.

/* This has not been Tested on a CS Collation */
DECLARE @Path VARCHAR(512)
,@StartTime DATETIME
,@EndTime 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'

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
Post #1590455
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse