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.

kaitlyn
  • 563
  • 2
  • 7
  • 14

1 Answers1

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;
Community
  • 1
Aaron Bertrand
  • 175,645
  • 25
  • 380
  • 589
  • 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
  • The only time I've ever heard is when your drive is filling up unexpectedly and you need to extend your log onto another drive. Even then it's best to fix the problem and remove the additional log. – Kenneth Fisher Dec 16 '15 at 17:54
  • Thanks for the query, that'll come in handy. Unfortunately, I think the trace file already got overwritten so I can't find any data that far back. – kaitlyn Dec 16 '15 at 18:44
  • @kaitlyn Unfortunately, I don't think there'll be any other way to find that information. If you can't ask your users, and you can't trust them to be honest about these things when asked, then it's time to start thinking about taking rights away. – Aaron Bertrand Dec 16 '15 at 18:49