SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Check who add datafile to a database


Check who add datafile to a database

Author
Message
Jorge Mendes
Jorge Mendes
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 114
Hello,

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

Thanks and regards
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45387 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67565 Visits: 18570
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','ObjectBigGrineleted','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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search