SQL server restart

Question

Post reply

SQL server restart

Arsh

SSCertifiable

Points: 6354
More actions
November 20, 2017 at 6:13 am

#340564

Dear Experts,
Where does SQL server store the contents of the memory buffer when a server is restarted. From where does it take the information of transaction status etc to rollforward or rollback , besides all other tasks it has to do ?
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

It doesn't. Anything in memory is lost on a restart.

Database recovery information comes from the transaction logs.

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

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 2

Arsh - Monday, November 20, 2017 6:13 AM
Dear Experts,
Where does SQL server store the contents of the memory buffer when a server is restarted. From where does it take the information of transaction status etc to rollforward or rollback , besides all other tasks it has to do ?
Thank you

Out of pure curiosity, why do you ask?
😎

Arsh SSCertifiable Points: 6354 More actions · Answer 3

GilaMonster - Monday, November 20, 2017 6:15 AM
It doesn't. Anything in memory is lost on a restart.
Database recovery information comes from the transaction logs.

But how about the transaction logs that are not check-pointed ?

Arsh SSCertifiable Points: 6354 More actions · Answer 4

Eirikur Eiriksson - Monday, November 20, 2017 7:22 AM
Arsh - Monday, November 20, 2017 6:13 AM
Dear Experts,
Where does SQL server store the contents of the memory buffer when a server is restarted. From where does it take the information of transaction status etc to rollforward or rollback , besides all other tasks it has to do ?
Thank you
Out of pure curiosity, why do you ask?
😎

Eirikur , the question flashed my mind. While working on Oracle I knew it does instance recovery after restarts but didn't know about SQL server.

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

Arsh - Monday, November 20, 2017 8:51 AM
GilaMonster - Monday, November 20, 2017 6:15 AM
It doesn't. Anything in memory is lost on a restart.
Database recovery information comes from the transaction logs.
But how about the transaction logs that are not check-pointed ?

Checkpoint is about data pages, not the transaction log. Nothing in memory survives a restart, the database recovery is done entirely from the transaction log.

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

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 6

Arsh - Monday, November 20, 2017 8:53 AM
Eirikur Eiriksson - Monday, November 20, 2017 7:22 AM
Arsh - Monday, November 20, 2017 6:13 AM
Dear Experts,
Where does SQL server store the contents of the memory buffer when a server is restarted. From where does it take the information of transaction status etc to rollforward or rollback , besides all other tasks it has to do ?
Thank you
Out of pure curiosity, why do you ask?
😎
Eirikur , the question flashed my mind. While working on Oracle I knew it does instance recovery after restarts but didn't know about SQL server.

It's more or less the same on both Oracle and SQL Server, called redo logs on the former and transaction logs on the latter
😎
As to the ACID requirements, nothing is kept in memory as that's a volatile storage that cannot survive a restart.

Arsh SSCertifiable Points: 6354 More actions · Answer 7

Arsh

SSCertifiable

Points: 6354

November 20, 2017 at 11:21 am

#1968833

Thanks Gail and Eirikur. My question is answered.