Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need Minimally Logged Operation to Cause Checkpoint! Expand / Collapse
Author
Message
Posted Friday, April 5, 2013 8:59 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
I am preparing for a presentation next week and am digging for an example....

I need a minimally logged operation that will show the behavior that a checkpoint is ran when a minimally logged operation occurs while in SIMPLE or BULK_LOGGED recovery model.

I have tried rebuilding, creating and dropping indexes...I have created heaps using SELECT..INTO, but nothing seems to consistently generate a CHECKPOINT.

What am I doing wrong?

Here is where I read that they should cause checkpoints:
us/library/ms189573(v=sql.105).aspx

Here is the list I am using for minimally logged operations:
http://msdn.microsoft.com/en-us/library/ms191244(v=sql.105).aspx

I am using the following to look for checkpoints:
select * from fn_dblog(null,null) WHERE Operation IN ('LOP_BEGIN_CKPT','LOP_END_CKPT')

Any help would be greatly appreciated!
Post #1439270
Posted Friday, April 5, 2013 9:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:36 PM
Points: 12,965, Visits: 32,539
you can explicitly call CHECKPOINT yourself, would that help?

--UPDATE MyTABLE  ....
--INSERT INTO SomeTable
CHECKPOINT
--UPDATE MyTABLE2 ....
--INSERT INTO SomeTable2
CHECKPOINT



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1439281
Posted Friday, April 5, 2013 9:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:37 PM
Points: 40,662, Visits: 37,127
I would have some doubts about that BoL page, never seen a minimally logged operation trigger a checkpoint before and besides, it doesn't need to trigger a checkpoint. Any data modification that is minimally logged has to be written to the data file before the transaction completes, that's done by the thread that executes the minimally logged operation (called an eager write), so there would be no reason for a checkpoint afterwards.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1439284
Posted Friday, April 5, 2013 9:33 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
Just got back from a little walk and that is what I was thinking Gail (not the smart stuff, but that BoL is probably under stating the conditions)...

Oh well...

I will remove it from my presentation :)

Thanks for all the help!
-Dane
Post #1439299
Posted Friday, April 5, 2013 9:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:37 PM
Points: 40,662, Visits: 37,127
If you're doing a presentation on bulk-logged, maybe there's something in here for you?
http://www.sqlservercentral.com/articles/Recovery+Model/89664/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1439304
Posted Friday, April 5, 2013 9:43 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
Thanks Gail :)

I had found that on my hunt for more information...I am actually doing a presentation on the T-LOG...

Per Sean McCown, I purposefully selected a rough topic that would require a ton of research...typically when I present I would always pick something that I could naturally talk about for hours.

I am trying to learn enough so that I can pass the cert tests (w/o boot camps).

Thanks again!
-Dane
Post #1439312
Posted Friday, April 5, 2013 12:59 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:37 PM
Points: 40,662, Visits: 37,127
dkschill (4/5/2013)
Thanks Gail :)

I had found that on my hunt for more information...I am actually doing a presentation on the T-LOG...


You've seen the stairway article series on that topic?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1439433
Posted Friday, April 5, 2013 1:29 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 9:29 PM
Points: 459, Visits: 428
Found those as well :)

Using those to validate and flush out my points.

This presentation is going to be brutal for the listeners if I don't clean it up some this weekend...lots of content...

Any other references you could throw out would be much appreciated!

Thanks again for the help!
-Dane
Post #1439449
Posted Friday, April 5, 2013 2:06 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 2,355, Visits: 3,537
GilaMonster (4/5/2013)
Any data modification that is minimally logged has to be written to the data file before the transaction completes



I don't think that's true. If it is, it violates the write-ahead-logging model, which SQL Server uses; only log records should have to be hardened for a transaction to complete, not data blocks.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1439466
Posted Friday, April 5, 2013 2:17 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 20,896, Visits: 32,929
ScottPletcher (4/5/2013)
GilaMonster (4/5/2013)
Any data modification that is minimally logged has to be written to the data file before the transaction completes



I don't think that's true. If it is, it violates the write-ahead-logging model, which SQL Server uses; only log records should have to be hardened for a transaction to complete, not data blocks.


I'll go with Gail on this one having reread this article: http://www.sqlservercentral.com/articles/Recovery+Model/89664/.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1439471
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse