Transaction log - SQL Server Forums

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Transaction log

Author

Topic

carumuga
Posting Yak Master

174 Posts

Posted - 2008-08-07 : 06:36:22

Hi,

Please correct me if i'm wrong.
Before writing the data into the physical disk, it logs information in the transaction log and after that checkpoint is issued which propagates all the modified data/dirty pages from the log to the physical file. This is how the transaction log behaves i believe.

I heard that the dirty pages are pages which are changed after reading into the cache. I would like to know from where these pages are retrieved into the cache. Is it from transaction log or from the database file or this cache is in the transaction log per-se? I'm bit confused on this.

shyam.koti
Starting Member

1 Post

Posted - 2008-08-07 : 07:33:56

I hope the following url will clear your doubt....

http://sqlblogcasts.com/blogs/sureshbarathan/archive/2008/04/17/know-the-transaction-log-part-1.aspx

pootle_flump

1064 Posts

Posted - 2008-08-07 : 08:14:39

Just one thing - the transaction log (in SQL Server 2005 at least, not so in 2000) is only ever written to - it is never read from *. The dirty pages were originally read from the database.

* EDIT - I mean in normal functioning.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-07 : 22:48:42

>> the transaction log (in SQL Server 2005 at least, not so in 2000) is only ever written to

Same on sql2k.

pootle_flump

1064 Posts

Posted - 2008-08-08 : 03:29:03