My good friend Orson Weston (@weston12) posted a question to #sqlhelp on Twitter earlier: Is there a way out of the box to find when and who changed the max file size for a log file?
You can’t tell this from the default trace as that just logs that the database was altered, not the log file.
But yes, I can tell you who made a change to the configuration of the log file for a database, using transaction log analysis.
I just ran the following code to set up the demo:
CREATE DATABASE foo2; GO ALTER DATABASE foo2 MODIFY FILE (NAME = N'foo2_log', MAXSIZE = 100GB); GO
If I want to know who changed the log file properties, I can rely on the fact that the log file is file ID 2, and that the log file characteristics are stored in the sysprufiles hidden system table. Slot 0 in that table is the MDF, slot 1 is the LDF. Using fn_dblog to look in the transaction log for changes to that slot in that table, I can run the following code:
SELECT SUSER_SNAME ([Transaction SID]) AS [User], [Begin Time] AS [Change Time] FROM fn_dblog (NULL, NULL) WHERE [Transaction ID] = (SELECT [Transaction ID] FROM fn_dblog (NULL, NULL) WHERE [Operation] = N'LOP_MODIFY_ROW' AND [AllocUnitName] = N'sys.sysprufiles.clst' AND [Slot ID] = 1) AND [Operation] = N'LOP_BEGIN_XACT'; GO
User Change Time ------------------------------------------- ------------------------ APPLECROSS\paul 2013/09/18 14:01:25:310
It’s harder to tell exactly what changed, but you can work that out by looking at the structure of the sysprufiles records and matching the Offset column in the log record in the fn_dblog output. Not impossible, but tedious. In most cases it’s enough to know that someone changed the log file characteristics at all.
If you’re interested in a different log file (why do you have more than one?) then figure out which slot ID it is in sysprufiles and substitute that slot ID in the code.
And if you don’t have any information in the current transaction log, you can scan your log backups using the same code and using fn_dump_dblog instead (see here for details, syntax, and a usage warning).
Hope this helps!