Commit, Checkpoint and Truncate.

Question

Post reply

Commit, Checkpoint and Truncate.

beejug1983

SSCertifiable

Points: 6491
More actions
September 30, 2010 at 3:06 am

#224570

Hi
Can you give me some through details about commit, checkpoint and truncate in sql server 2005.

Viewing 11 posts - 1 through 10 (of 10 total)

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

WellingtonSQL SSC Veteran Points: 240 More actions · Answer 1

Summarized.

- Commit: Operation that confirms (records) the transaction in log of transaction.

- Chackpoint: Process that generates a marking in log of transaction and transfers the data of the archive of log (LDF) to the data archive (MDF).

- Truncate: Operation that removes the register of a table and does not generate log, different of delete that it generates.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Google for "RECOVERY MODELS" , there you can find useful information about these three terms.:-)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 3

What are these interview questions? There is some relation between the three, but they're also somewhat different operations. What are you looking for specifically?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

doctom22 (9/30/2010)
- Commit: Operation that confirms (records) the transaction in log of transaction.

Kinda, but not quite.

- Chackpoint: Process that generates a marking in log of transaction and transfers the data of the archive of log (LDF) to the data archive (MDF).

Nope.

- Truncate: Operation that removes the register of a table and does not generate log, different of delete that it generates.

Nope.

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

beejug1983 SSCertifiable Points: 6491 More actions · Answer 5

I want to know what exactly happens internally when these command get executed.

beejug1983 SSCertifiable Points: 6491 More actions · Answer 6

I want to know what exactly happens internally when these command get executed.

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

Exactly as in step by step code walkthrough? Or do you just want a high level overview?

If high level overview:

For commit: http://msdn.microsoft.com/en-us/library/ms190295%28v=SQL.90%29.aspx and http://msdn.microsoft.com/en-us/library/ms175523%28v=SQL.90%29.aspx

For checkpoint, start here: http://sqlinthewild.co.za/index.php/2009/06/26/the-lazy-writer-and-the-checkpoint/

Which truncate are you asking about? truncate table or backup log ... truncate only? (or one of the other occurrences of truncate?)

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

beejug1983 SSCertifiable Points: 6491 More actions · Answer 8

Yes Gail, I want high level overview and here I am talking about truncate backup.

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

For commit and checkpoint the links I've given you should do. For the last, google "backup log" "truncate only" and I'm sure you'll have more reading than you'll need.

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

beejug1983 SSCertifiable Points: 6491 More actions · Answer 10

beejug1983

SSCertifiable

Points: 6491

October 1, 2010 at 2:43 am

#1230085

HI Gail

Thanks for your stuffs.