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?

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.

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.

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?

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.