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 12345»»»

Managing Transaction Logs Expand / Collapse
Author
Message
Posted Thursday, October 30, 2008 10:16 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: Today @ 2:11 AM
Points: 40,431, Visits: 36,882
Comments posted to this topic are about the item Managing Transaction Logs


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 #594769
Posted Thursday, October 30, 2008 10:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 4:44 PM
Points: 6, Visits: 383
When do checkpoints happen? Are they automatic? Can they be invoked programmatically?

Besides simple recovery, are there other techniques to minimize the size of the transaction log?

Thank you,

Jake



Post #594770
Posted Thursday, October 30, 2008 10:57 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:47 AM
Points: 5,448, Visits: 1,401
Excellent discussion... Useful one


Post #594776
Posted Friday, October 31, 2008 12:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
Hi Gail

Nice article... presented in very easy to understand manner

One question may not be entirely related to this topic

Say there is a big data modification (update statement) going on and not all pages affected by the update stmt are in the buffer/memory. Will the pages not in the memory be first fetched into the memory and then changed ? Or will the change be in the log and then written to the disk without the pages being brought to the memory?

Thanks


"Keep Trying"
Post #594792
Posted Friday, October 31, 2008 1:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:21 AM
Points: 6,743, Visits: 8,517
100% demystification

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #594800
Posted Friday, October 31, 2008 2:34 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: Today @ 2:11 AM
Points: 40,431, Visits: 36,882
Jake (10/30/2008)
When do checkpoints happen?


At regular intervals depending on the number and frequency of data modifications.

Are they automatic?


Yup.

Can they be invoked programmatically?


Yes. The command is CHECKPOINT. It's not something that's often needed/

Besides simple recovery, are there other techniques to minimize the size of the transaction log?


Keep transactions short. The tran log can only be truncated to the beginning of the oldest active transaction. If you have transactions that remain open for long periods of time, it will prevent log truncation.



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 #594813
Posted Friday, October 31, 2008 2: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: Today @ 2:11 AM
Points: 40,431, Visits: 36,882
Chirag (10/31/2008)

Say there is a big data modification (update statement) going on and not all pages affected by the update stmt are in the buffer/memory. Will the pages not in the memory be first fetched into the memory and then changed ? Or will the change be in the log and then written to the disk without the pages being brought to the memory?


The query processor can only operate on pages in memory. If a query (read or change) requires a page that isn't in memory then the query processor requests that page and puts the query that needs it into a waiting state. The storage engine will go and fetch that page (issuing the appropriate IO requests to the OS), put it into memory and then signal the query processor that the page is there. The query that needed it will then be made runnable and can continue processing.

Bit of an over-simplification, but serves to explain.



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 #594816
Posted Friday, October 31, 2008 2:41 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: Today @ 2:11 AM
Points: 40,431, Visits: 36,882
Anirban Paul (10/30/2008)
Excellent discussion... Useful one


ALZDBA (10/31/2008)
100% demystification


Thank you. Glad you liked it.



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 #594817
Posted Friday, October 31, 2008 2:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
GilaMonster (10/31/2008)
Chirag (10/31/2008)

Say there is a big data modification (update statement) going on and not all pages affected by the update stmt are in the buffer/memory. Will the pages not in the memory be first fetched into the memory and then changed ? Or will the change be in the log and then written to the disk without the pages being brought to the memory?


The query processor can only operate on pages in memory. If a query (read or change) requires a page that isn't in memory then the query processor requests that page and puts the query that needs it into a waiting state. The storage engine will go and fetch that page (issuing the appropriate IO requests to the OS), put it into memory and then signal the query processor that the page is there. The query that needed it will then be made runnable and can continue processing.

Bit of an over-simplification, but serves to explain.


Thank you Gail. That explains things clearly. So would the same apply to BLOB data. Would BLOB data be brought into memory and then modified?


"Keep Trying"
Post #594818
Posted Friday, October 31, 2008 2:59 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: Today @ 2:11 AM
Points: 40,431, Visits: 36,882
Chirag (10/31/2008)

Thank you Gail. That explains things clearly. So would the same apply to BLOB data. Would BLOB data be brought into memory and then modified?


Exactly the same. The query processor can only operate on pages in memory. The query processor has no knowledge of the disks and no ability to interact with the disks. That's what the storage engine does.



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 #594826
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse