Does transaction log contain data or commands

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Does transaction log contain data or commands

Author

Topic

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2014-07-25 : 10:18:37

Hi all,

What does the transaction log actually contain?

Is it each individual data row that is inserted/modified?

Or the command that modified the data?

I'm guessing it's the former

Thanks

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-25 : 10:34:19

It contains information about everything done in your db for rollback and roll forward purposes

-- take a look
SELECT *
FROM fn_dblog (null, null);

-- some good links
http://www.sqlskills.com/blogs/paul/understanding-data-vs-log-usage-for-spills-in-tempdb/
http://rusanu.com/2014/03/10/how-to-read-and-interpret-the-sql-server-log/
http://raresql.com/2013/04/15/sql-server-undocumented-function-fn_dblog/
http://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-07-26 : 03:04:39

According to SQL Server BOL;supplied the SQL Server Books Online :
1) The start and end of each transaction
2) Every data modification (insert, update, or delete). This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.
3) Every extent and page allocation or deallocation.
4) Creating or dropping a table or index
5) Rollback operations
6) Transaction SID

Also , check:
http://www.sqlserver-dba.com/2012/12/sql-server-inside-the-sql-transaction-logs-with-fn_dblog-and-current-transaction_id.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources