5

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.

6

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;
  • It won't get you Who but can't you just check the When by looking at the create date of the additional log file? Then you could use that date/time to narrow down your query. – Kenneth Fisher Dec 16 '15 at 17:31
  • 1
    @Kenneth Yes, if you go look on the file system, that's why I included StartTime to match it to (the catalog views like sys.database_files don't have date/time data). However, I'm not sure it can be relied on, e.g. if the database has been restored with replace, or detached/copied and then re-attached, the file attributes may change. But if the create date is older than the oldest event in the trace file, yes, you could certainly rule out being able to get the who from there. – Aaron Bertrand Dec 16 '15 at 17:36
  • @AaronBertrand can we create multiple log file for a database? – AA.SC Dec 16 '15 at 17:39
  • @AA.SC yes, you can, but you should never except in an emergency: ALTER DATABASE foo ADD LOG FILE (name = N'foolog2', filename = N'C:\wherever\name.ldf'); – Aaron Bertrand Dec 16 '15 at 17:46
  • @AaronBertrand At what scenario we have to? – AA.SC Dec 16 '15 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.