SELECT * FROM ::fn_dblog(DEFAULT, DEFAULT) AS l -- more examples, and details, below
During the Spring of 2008, for an auditing project, our requirement was be able to read the active log files, as well as those that were archived. The archived logs gave us the option, with the help of third party tools (or thanks to in part the query above), to effectively interrogate the log file as if it were one mega table. The way that Lumigent Log Explorer's documentation describes it: 'to assist you in solving or recovering from problems that may occur in a typical database system' - gets the heart of what could be a potential point-in-time restore (please use Full recovery model if this is your requirement).
My view is that you may end up with a mystery transaction at one point that no logic can explain, and thus your database integrity is in question - not a place where any DBA wants to be naturally. Empowering yourself to dig into the log file and resolve these types of mysteries is the main point of this post, because you will be able to find out what the values were before/after a change to the database, and who/what application has committed the change, whereas before one typically disregards anomalies (at least I was most of the time before). This gives us motivation to ensure that log files are archived, since we're following Erasmus' proverb to 'leave no stone unturned,' with respect to resolving such mysteries.
Given that you can query the log file (you will see transactions as BEGIN_XACT , COMMIT_XACT, please see full reference list below), it is therefore possible to raise alerts for undesirable activity, e.g. someone executing data definition language in production. Combined with Database Mail and SQL Server Agent this can be automated too, or in the case of Lumigent Log Explorer, one can configure an alert for each DDL/DML command, which is perhaps useful - to filter out problems in development. The approach of monitoring objects during manipulation or creation will allow you to take control of an environment progressively and proactively. Here is how to query the log file or some typical unwanted incidents:
USE [master] GO ALTER DATABASE [DBname] SET RECOVERY SIMPLE WITH NO_WAIT GO -- if you leave something in Simple, the rows after checkpoint -- will be recycled, therefore I suggest FULL or at least Bulk_logged USE [master] GO ALTER DATABASE [DBname] SET RECOVERY FULL WITH NO_WAIT GO -- if you need to clean up the space quickly for testing USE [dbname] GO DBCC SHRINKFILE (N'DBname, 0, TRUNCATEONLY) GO -- truncate a table or perform undesireable activity, etc. SELECT Operation, Context, [Transaction ID], [Begin Time], [End Time], AllocUnitName, [Description], [UID], [Server UID], SPID, [Transaction Name], [Number of Locks], [Lock Information] , * -- shows the rest of the columns, I put the most interesting first FROM ::fn_dblog(DEFAULT, DEFAULT) AS l where operation='mark_ddl' -- this will show rows where there is data definition language -- operation='LOP_MODIFY_ROW' or operation='INSERT_ROWS' or operation='DELETE_ROWS' -- operation='LOP_BEGIN_XACT' -- means beginning of a transaction -- operation='LOP_COMMIT_XACT' -- means the end of a transaction order by [Current LSN] asc -- for the above Mark_DDL you can create a job step that checks your critical -- databases for undesireable activity and if there is an existence of a DDL change (use IF EXISTS with the above) declare @myfromname varchar(150) declare @alladdresses varchar(max) declare @myrecipients varchar(150) declare @mycurrentaddress varchar(max) declare @SubjectLocal varchar(200) declare @databasename varchar(100) set @databasename=(select top 1 name from sysfiles) BEGIN-- Name of current sender SET @myfromname = N'Message regarding Log file activity on ' + @@servername -- Get e-mail adresses of operators BEGIN SET @alladdresses = N'' DECLARE MAILResults_CURSOR CURSOR FORWARD_ONLY READ_ONLY FOR SELECT email_address FROM msdb.dbo.sysoperators where email_address IS NOT NULL OPEN MAILResults_CURSOR FETCH NEXT FROM MAILResults_CURSOR INTO @myrecipients WHILE @@FETCH_STATUS = 0 BEGIN SET @mycurrentaddress = @myrecipients + CHAR(59) SET @alladdresses = @alladdresses + @mycurrentaddress FETCH NEXT FROM MAILResults_CURSOR INTO @myrecipients END CLOSE MAILResults_CURSOR DEALLOCATE MAILResults_CURSOR IF @alladdresses <> N'' BEGIN SET @SubjectLocal = 'Log file undesireable activity in the ' + @databasename + ' DB on ' + @@servername EXEC msdb.dbo.sp_send_dbmail @profile_name = NULL ,@recipients = @alladdresses ,@copy_recipients = NULL ,@blind_copy_recipients = NULL ,@subject = @SubjectLocal ,@body = 'Please verify the log to find out what happened in '+ @databasename +' using select * FROM ::fn_dblog(DEFAULT, DEFAULT) AS l' ,@body_format = 'TEXT' ,@importance = 'High' ,@sensitivity = 'Normal' END END END
**Posting ADDY: for a great similar story on this (added in June 2009, after my talk in Vancouver where I performed a similar demo also) Paul S. Randal takes a similar step here
Internally to the SQL Server Log File you'll have an operation code that is captured by the log record. Here are the most common ones taken from a combination of Microsoft and Lumigent Log Explorer's help files.