Check Point and Batch processing | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Check Point and Batch processing

Hi,
can checkpoint be used within stored procedures? Or is it a server level? If it can be used in procedures, will setting it once when the procedure starts will hold good until it finishes or should I use it after every commit/rollback? Does it commit the transactions when it reaches a checkpoint ? Thanks!
SS
No need to manually checkpoint; the server handles this automatically. All you need to program transactions is begin transaction / commit transaction / rollback. There is nothing like "holding" a checkpoint.
Will the transaction log be cleared after the commit tran statement? Because I was running into issues of transaction log becoming full, I was asked to use checkpoint. SS
No until unless the CHECKPOINT is issues, it will be when you execute BACKUP LOG or CHECKPOINT explicitly. Also when the database is in SIMPLE recovery model the system will issue a checkpoin when the log reaches 87% of its size (I think). Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
So how can I avoid / clear the tranaction log from becoming full, because my procedure has a long running query and it fills up the log. So checkpoint shouldn’t be used in procedures. Is that right? SS
Perform frequent BACKUP LOG statements in order to reduce the virtual log size to write it. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
Make the log file bigger, or break your procedure down into smaller / more manageable transactions. A single huge transaction will take a lot of log space, and there’s not much way around that except to provide the space or make the transaction smaller.
I’ve broken the whole process into n iterations and running them and also doing a commit at the end of each iteration. It helps. Thanks merrillaldrich and Satya. SS
]]>