Sql-server – what are the events recorded in the transaction log

sql servertransaction-log

in regards to what events are recorded in the transaction log , is the below an accurate/comprehensive list?

How are things recorded? are the pages that are marked as modified written to the transaction log?

is there a way to see what is in the transaction log?

The start and end of each transaction
Every data modification 
Every extent allocation or deallocation
The creation or dropping of a table or index
Broker event category includes events produced by Service Broker.
Cursors event category includes cursor operations events.
CLR event category includes events fired by .Net CLR objects.
Database event category includes events of data.log files shrinking or growing on their own.
Errors and Warning event category includes SQL Server warnings and errors.
Full text event category include events occurred when text searches are started, interrupted, or stopped.
Locks event category includes events caused when a lock is acquired, released, or cancelled.
Object event category includes events of database objects being created, updated or deleted.
OLEDB event category includes events caused by OLEDB calls.
Performance event category includes events caused by DML operators.
Progress report event category includes Online index operation events.
Scans event category includes events notifying table/index scanning.
Security audit event category includes audit server activities.
Server event category includes server events.
Sessions event category includes connecting and disconnecting events of clients to SQL Server.
Stored procedures event category includes events of execution of Stored procedures.
Transactions event category includes events related to transactions.
TSQL event category includes events generated while executing TSQL statements.
User configurable event category includes user defined events.

Best Answer

Every transaction in SQL Server is logged in someway or other depending on recovery model. fn_dblog and fn_dumpdblog both undocumented command would give you nice view what is present in transaction log but the result could be massive. What I do is I run whatever transaction I need to do within begin tran and commit/rollback and then only capture logs I need to using below query

BEGIN TRANSACTION TRANSACTION_NAME
SQL QUERY
COMMIT TRANSACTION OR ROLLBACK TRANSACTION

--Then use below query to filter out records related to the transaction

USE DATABASE_NAME
GO
SELECT
OPERATION,
Context,
[Transaction Name],
[AllocUnitId],
[Page ID],
Description,
[Lock Information]
FROM FN_DBLOG(NULL,NULL)
WHERE [Transaction ID] in
(
select [Transaction ID] from
fn_dblog(Null,Null)
where [Transaction Name]='TRANSACTION_NAME'
)

You can run 

SELECT * FROM FN_DBLOG(NULL,NULL)

To see all column names which fn_dblog would produce

Example: I would show what all things are logged when simple update command is fired

Begin tran T1

update [dbo].[IXBUILD] set C2='XXXXXX' where C1=1

Commit

enter image description here

  1. Its has one row for begin tran one for commit and one regarding update operation. You can see LOP_BEGIN_XACT for beginning of transaction and COMMIT for commit of transaction.

  2. LOP_MODIFY_ROW says a row was modified like we did in update statement.

  3. LCK_Clustered_index is in picture because table had CI and row must have been exclusively locked for update

  4. Then comes hexadecimal page ID which actually says what page had row which was modified

  5. Then last column Includes the lock Information. The lock which was taken on HoBt 72057594041860096:ACQUIRE_LOCK_IX OBJECT: 8:613577224:0 ;ACQUIRE_LOCK_IX PAGE: 8:1:344 ;ACQUIRE_LOCK_X KEY: 8:72057594041860096 (8194443284a0)

    Database ID=8 file number 1 and page 334. You would also see KEY value

  6. There is also a log sequence number for every transaction and column Log Record Length which would tell you LSN and size of log record for particular operation

This was just a small demo. Remus Rusanu has explained how to read transaction log in details in This article please read it

Related Question