Sql-server – When was second log file created for database

logssql server

Is it possible to see when a second log file was created for a database and by who? I have found an additional log file that was created for one of our databases in our environment and I'm trying to see who and when it was created. We are using SQL server 2012 SP2 Enterprise edition.

I'm trying to find out who and when the second ldf file was created for a database.

Best Answer

This will query the default trace for any changes to the database, which will include the addition of files (but it will also include other things that you can't differentiate, like changing a database's compatibility level). It will also only include data that is currently in the default trace files (they age away; see this answer for details, but basically, how far back the history goes depends largely on how many trace events are captured), and requires that the default trace has not been disabled.

With all those caveats out of the way, this may at least narrow down the set of logins that have made changes to your database recently, and then you can ask them directly (assuming you can map logins to humans):

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM sys.trace WHERE is_default = 1;

SELECT LoginName, NTUserName, HostName, StartTime
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 164                 -- Object:Altered
  AND DatabaseName = N'my_database_name' -- ***CHANGE THIS***
  AND ObjectID IS NULL                   -- not an object
  AND ObjectType = 16964                 -- database
  AND EventSubClass = 1                  -- only need one of the row pair
  ORDER BY StartTime DESC;
Related Question