HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

When was second log file created for database?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
filelogcreateddatabasesecondforwhenwas

Problem

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.

Solution

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;

Code Snippets

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;

Context

StackExchange Database Administrators Q#123846, answer score: 6

Revisions (0)

No revisions yet.