Question about transaction log.

Question

Post reply

Question about transaction log.

enriarg

Old Hand

Points: 384
More actions
May 20, 2014 at 10:08 pm

#293478

I was reading about delay transaction log, durability, truncate and all that, someone on one of the documents mention that a service shutdown doesn't really commit the dirty pages, so I wonder is there a instance wide command that can persist all ongoing transaction logs?
Or did I miss understand the document.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259125 More actions · Answer 1

You mean like checkpoint?

It is not an instance wide command though.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

enriarg (5/20/2014)
I was reading about delay transaction log, durability, truncate and all that, someone on one of the documents mention that a service shutdown doesn't really commit the dirty pages, so I wonder is there a instance wide command that can persist all ongoing transaction logs?

I suspect there's a slight misunderstanding here. Dirty pages are not log.

The transaction log records are written to disk when a transaction commits, always (well, sometimes sooner). The changed data pages (dirty pages) however aren't necessarily written to the data file. They will be at some later point via the checkpoint or lazy writer background processes.

A service shutdown will try to checkpoint the databases (clean shut down), but can't always (imagine a power failure). If a database wasn't shut down cleanly (all dirty data pages written back to the data file), then when SQL restarts it runs crash recovery, replaying the transaction log records against the data file to redo those changes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

enriarg Old Hand Points: 384 More actions · Answer 3

oh I am aware, sorry I guess I write this a bit to late at night to make sense, still you guys did answer my doubts about the service shutdown and not so clean shutdown.

I appreciate the help

free_mascot One Orange Chip Points: 27164 More actions · Answer 4

In addition to above, when sql server started you may also see the messages for rollback transaction and rollforward transactions in the database. which is nothing but based on checkpoint and dirty pages.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."