SQL Server Performance

Check Point and Batch processing

Discussion in 'SQL Server 2005 General Developer Questions' started by sswam, Jun 7, 2006.

  1. sswam New Member



    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
  2. merrillaldrich New Member

    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.
  3. sswam New Member

    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
  4. satya Moderator

    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.
  5. sswam New Member

    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
  6. satya Moderator

    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.
  7. merrillaldrich New Member

    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.
  8. sswam New Member

    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

Share This Page