How to tell who changed a log file characteristic?

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!

5 thoughts on “How to tell who changed a log file characteristic?

  1. Thanks, Paul, I like this approach of mining the transaction log for details that are not easily accessible elsewhere in the product.

    A different approach (along the lines of a sql trace, really a poor man’s sql trace) is to monitor the ALTER_DATABASE event using, for example, Powershell:

    # Assuming your sql server is a named instance something like MYSERVER\MYINSTANCE
    $computer = “MYSERVER”
    $query = “SELECT * FROM ALTER_DATABASE”

    # Set up the action that should be taken when the event is raised – in this case
    # writing to a CSV file – this command should be all on one line

    $action = { $event.SourceEventArgs.NewEvent | select-object -property * | export-csv “c:\temp\ALTER_DATABASEevents.csv” -NoTypeInformation -Append }

    # Register for the event – this command should be all on one line

    Register-WmiEvent -Namespace ‘root\Microsoft\SqlServer\ServerEvents\MYINSTANCE’ -Query $query -Action $action -Computer $computer -SourceIdentifier “ALTER_DATABASEevents $computer”

    # Check that the registration has been successful

    Get-EventSubscriber

    # Then change the max size of the transaction log (or any other database related attribute)
    # and examine the contents of the CSV file. You should see the full details including the
    # command that was run

    # When you’re finished unregister from the event

    Unregister-Event -SourceIdentifier “ALTER_DATABASEevents $computer”

  2. just want to convey my thanks to you. your articles are a pleasure to read. authentic and proper. thanks and keep up the good work.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.