Check who add datafile to a database

  • Hello,

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

    Thanks and regards

  • 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.

  • 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