Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Recently got a DBA who said the Log files was growing bigger than the data file size

  • Recently got a DBA who said the Log files was growing bigger than the data file size. On discussion he mentioned using CHECKPOINT to solve this problem. So what does CHECKPOINT do inside SQL Server? Does the behavior change based on Database recovery models?

    Posted on 01-16-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

8  Answers  

Subscribe to Notifications
  • Score
    8

    Checkpoint is a process which will write all dirty pages (modified page in buffer cache which is not written to disk) from buffer cache to disk. It helps in recovery time of the database so that when the database start’s up there is no need to do this for all dirty pages, so checkpoint happens in an regular interval.This minimizes the active portion of the log that must be processed during a full recovery of a database.

    For performance reasons, the Database Engine performs modifications to database pages in memory and does not write the page to disk after every change. However, periodically the Database Engine needs to perform a checkpoint to write these dirty pages to disk. Writing dirty pages to disk creates a known good point from which the Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

    When a checkpoint operation occurs, no matter how it's triggered (for instance through a manual CHECKPOINT, from a database or differential backup, or automatically) the same set of operations occurs:

    All dirty data file pages for the database are written to disk (all pages that have changed in memory since they were read from disk or since the last checkpoint), regardless of the state of the transaction that made the change.

    Before a page is written to disk, all log records up to and including the most recent log record describing a change to that page are written to disk (yes, log records can be cached in memory too). This guarantees recovery can work and is called write-ahead logging.

    Log records are written to the log sequentially, and log records from multiple transactions will be interspersed in the log. The log cannot be selectively written to disk, so writing a dirty page to disk that only has a single log record affecting it may mean writing many more previous log records to disk as well. Log records describing the checkpoint are generated.

    The LSN of the checkpoint is recorded in the database boot page in the dbi_checkptLSN field

    If in the SIMPLE recovery model, the VLFs in the log are checked to see whether they can be marked inactive (called clearing or truncating the log - both of which are terrible misnomers, as nothing is either physically cleared or truncated).

    The checkpoint behaviour differs as follows:

    If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

    If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

    The log becomes 70 percent full.
    
    The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.
    

    Automatic checkpoints truncate the unused section of the transaction log if the database is using the simple recovery model. However, if the database is using the full or bulk-logged recovery models, the log is not truncated by automatic checkpoints.

    Replied on Jan 16 2012 6:40AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    8

    CHECKPOINT ?

    When we done operation on SQL SERVER that is not commited directly to the database.All operation must be logged in to Transaction Log files after that they should be done on to the main database.CheckPoint are the point which alert Sql Server to save all the data to main database if no Check point is there then log files get full we can use Checkpoint command to commit all data in the SQL SERVER.When we stop the SQL Server it will take long time because Checkpoint is also fired.

    Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.

    Each time SQL Server 2005 checkpoint executes the calculated target establishes the max outstanding I/O level.
    Depending on the type of checkpoint this value is adjusted as the I/Os complete.

    SQL Server 2005 checkpoint adds duration tracking to the I/O requests. As each I/O completes the duration is used to adjust a running I/O average. A running average is used to accommodate the 'immediate' state of the I/O path so Checkpoint can react quickly to the current state and not the historical states.


    When a checkpoint operation occurs, no matter how it's triggered (for instance through a manual CHECKPOINT, from a database or differential backup, or automatically) the same set of operations occurs:

    •All dirty data file pages for the database are written to disk (all pages that have changed in memory since they were read from disk or since the last checkpoint), regardless of the state of the transaction that made the change.

    • Before a page is written to disk, all log records up to and including the most recent log record describing a change to that page are written to disk (yes, log records can be cached in memory too). This guarantees recovery can work and is called write-ahead logging. Log records are written to the log sequentially, and log records from multiple transactions will be interspersed in the log. The log cannot be selectively written to disk, so writing a dirty page to disk that only has a single log record affecting it may mean writing many more previous log records to disk as well.

    • Log records describing the checkpoint are generated.

    • The LSN of the checkpoint is recorded in the database boot page in the dbi_checkptLSN field

    • If in the SIMPLE recovery model, the VLFs in the log are checked to see whether they can be marked inactive (called clearing or truncating the log - both of which are terrible misnomers, as nothing is either physically cleared or truncated).

    Checkpoints are not really tracked in the transaction log - it just serves as a useful repository for information about which transactions are active at the time of the checkpoint.

    The LSN of the last checkpoint is recorded in the database boot page. This is where recovery starts, and if this page is inaccessible, the database cannot be attached, opened, or processed in any way - partly because it's the boot page that knows whether the database was cleanly shut down or not, and partly because it's the only place that records the LSN of the last checkpoint record. You may say, well it's recorded in the transaction log too, but what if the log is corrupt in some way?

    One area of confusion is that the checkpoint log records are overwritten by subsequent checkpoints. Absolutely not - once written, a log record is NEVER updated or overwritten - it will only be overwritten when the log wraps and the VLFs are re-used. This has led to further confusion about when checkpoint information is retrievable from the log, using commands such as fn_dblog.

    How Check Points Work :: http://sqlskills.com/BLOGS/PAUL/post/How-do-checkpoints-work.aspx


    Events That Cause Checkpoints

    • Before a database backup, the Database Engine automatically performs a checkpoint so that all changes to the database pages are contained in the backup. In addition, checkpoints occur automatically when either of the following conditions occur:

    • The active portion of the log exceeds the size that the server could recover in the amount of time specified in the recovery interval server configuration option.

    • The log becomes 70 percent full, and the database is in log-truncate mode.

    • A database is in log truncate mode when both these conditions are TRUE: the database is using the Simple recovery model, and, after execution of the last BACKUP DATABASE statement that referenced the database, one of the following events occurs:

    • A minimally logged operation is performed in the database, such as a minimally logged bulk copy operation or a minimally logged WRITETEXT statement is executed.

    • An ALTER DATABASE statement is executed that adds or deletes a file in the database.

    • Also, stopping a server issues a checkpoint in each database on the server. The following methods of stopping SQL Server perform checkpoints for each database:

    • Using SQL Server Configuration Manager.

    • Using SQL Server Management Studio.

    • Using the SHUTDOWN statement.

    • Bringing an instance offline in a cluster.


    Does the behavior change based on Database recovery models?

    Checkpoint behavior changes based on the recovery model. There are 3 different recovery models inside SQL Server

    Simple recovery model:- This uses minimum adiministrative overhead for the transaction log, the simple recovery model risks significant workloss exposure if the database is damaged. Data is recoverable only to the most recent backup.

    In simple recovery all transactions are truncating the log on each checkpoint.

    Buld-logged recovery model:- Used for logging transactions while not filling up the log during bulk operations. This is used for large scale operations such as buld import or index creation. Switching temporarily to the bull-logged recovery model increases performance and reduces log space consumption. Log backups are still required if you want to be able to restore up to the point of failure.

    Full recovery model:- The full recovery model gurantees the least risk of losing your work if a data file gets damaged. In this model, SQL Server fully logs all operations. In this recovery model, you can recover to any Point-in-time and it is the most recommended model for financial systems.



    Thanks

    Yogesh

    Replied on Jan 16 2012 7:33AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    2

    A checkpoint performs the following processess in the database 1. writes a record to the log file, making the start of the checkpoint 2. Stores information for the checkpoint in a chain of checkpoint log records.

    Checkpoint behavior changes based on the recovery model. There are 3 different recovery models inside SQL Server

    Simple recovery model:- This uses minimum adiministrative overhead for the transaction log, the simple recovery model risks significant workloss exposure if the database is damaged. Data is recoverable only to the most recent backup. In simple recovery all transactions are truncating the log on each checkpoint.

    Buld-logged recovery model:- Used for logging transactions while not filling up the log during bulk operations. This is used for large scale operations such as buld import or index creation. Switching temporarily to the bull-logged recovery model increases performance and reduces log space consumption. Log backups are still required if you want to be able to restore up to the point of failure.

    Full recovery model:- The full recovery model gurantees the least risk of losing your work if a data file gets damaged. In this model, SQL Server fully logs all operations. In this recovery model, you can recover to any Point-in-time and it is the most recommended model for financial systems.

    Replied on Jan 16 2012 7:37AM  . 
    prasaddvr
    1253 · 0% · 20
  • Score
    8

    CHECKPOINT
    Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk. Forces all dirty pages for the current database to be written to disk.

    Syntax

    CHECKPOINT [ checkpoint_duration ]
    

    Events That Cause Checkpoints

    • Before a database backup, the Database Engine automatically performs a checkpoint so that all changes to the database pages are contained in the backup. In addition, checkpoints occur automatically when either of the following conditions occur:

    • The active portion of the log exceeds the size that the server could recover in the amount of time specified in the recovery interval server configuration option.

    • The log becomes 70 percent full, and the database is in log-truncate mode.

    • A database is in log truncate mode when both these conditions are TRUE: the database is using the Simple recovery model, and, after execution of the last BACKUP DATABASE statement that referenced the database, one of the following events occurs:

    • A minimally logged operation is performed in the database, such as a minimally logged bulk copy operation or a minimally logged WRITETEXT statement is executed.

    • An ALTER DATABASE statement is executed that adds or deletes a file in the database.

    • Also, stopping a server issues a checkpoint in each database on the server. The following methods of stopping SQL Server perform checkpoints for each database:

    • Using SQL Server Configuration Manager.

    • Using SQL Server Management Studio.

    • Using the SHUTDOWN statement.

      • Note The SHUTDOWN WITH NOWAIT statement shuts down SQL Server without executing a checkpoint in each database. This may cause the subsequent restart to take a longer time than usual to recover the databases on the server. Using the net stop mssqlserver command in a command-prompt window.
    • Using Services in Control Panel, selecting mssqlserver, and clicking Stop.

    • Bringing an instance offline in a cluster.

    Behavior based on Database recovery models

    Full or Bulk-logged recovery model
    If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option. The log is not truncated by automatic checkpoints if the database is using the full or bulk-logged recovery models

    simple recovery model
    If the database is using the simple recovery model, Automatic checkpoints truncate the unused portion of the transaction log if the database is using the simple recovery model. An automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

    1. The log becomes 70 percent full.
    2. The number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.

      The log is not truncated by automatic checkpoints if the database is using the full or bulk-logged recovery models.

    Replied on Jan 16 2012 11:43AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    3

    Checkpoint is a process which will write all dirty pages (modified page in buffer cache which is not written to disk) from buffer cache to disk.

    If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

    If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

    The log becomes 70 percent full. The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

    Replied on Jan 16 2012 2:18PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    5

    Checkpoint creates a point from which the SQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

    For performance reasons, the Database Engine performs modifications to database pages in memory—in the buffer cache—and does not write these pages to disk after every change. Rather, the Database Engine periodically issues a checkpoint on each database. A checkpoint writes the current in-memory modified pages known as dirty pages and transaction log information from memory to disk and, also, records information about the transaction log.

    The Database Engine supports several types of checkpoints: automatic, indirect, manual, and internal.

    Automatic EXEC sp_configure 'recovery interval', 'seconds' Issued automatically in the background to meet the upper time limit suggested by the recovery interval server configuration option. Automatic checkpoints run to completion. Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database Engine detects an increase in write latency above 20 milliseconds.

    For more information, see recovery interval Server Option (SQL Server).

    Indirect ALTER DATABASE … SET TARGETRECOVERYTIME = targetrecoverytime { SECONDS | MINUTES } Issued in the background to meet a user-specified target recovery time for a given database. The default target recovery time is 0, which causes automatic checkpoint heuristics to be used on the database. If you have used ALTER DATABASE to set TARGETRECOVERYTIME to >0, this value is used, rather than the recovery interval specified for the server instance.

    For more information, see Change the Target Recovery Time of a Database (SQL Server).

    Manual CHECKPOINT [ checkpointduration ] Issued when you execute a Transact-SQL CHECKPOINT command. The manual checkpoint occurs in the current database for your connection. By default, manual checkpoints run to completion. Throttling works the same way as for automatic checkpoints. Optionally, the checkpointduration parameter specifies a requested amount of time, in seconds, for the checkpoint to complete.

    Internal None. Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.

    Replied on Jan 17 2012 3:02AM  . 
    sk2000
    515 · 0% · 73
  • Score
    5

    Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.

    CHECKPOINT [ checkpoint_duration ]

    Where checkpointduration specifies the requested amount of time, in seconds, for the checkpoint to complete. When checkpointduration is specified, the SQL Server Database Engine attempts to perform the checkpoint within the requested duration. The checkpoint_duration must be an expression of type int, and must be greater than zero. When this parameter is omitted, SQL Server Database Engine automatically adjusts the checkpoint duration to minimize the performance impact on database applications.

    For performance reasons, the Database Engine performs modifications to database pages in memory and does not write the page to disk after every change. However, periodically the Database Engine needs to perform a checkpoint to write these dirty pages to disk. Writing dirty pages to disk creates a known good point from which the Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

    Checkpoints can occur concurrently on any number of databases.

    The Database Engine cannot recover from an interrupted checkpoint. If a checkpoint is interrupted and a recovery required, then the Database Engine must start recovery from a previous, successful checkpoint.

    Checkpoint behavior with Database recovery models

    *Simple Recovery Model: *

    1. If the database uses the simple recovery model, marks for reuse the space that precedes the MinLSN.
    2. If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

      a) The log becomes 70 percent full.

      b) The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.

    3. Automatic checkpoints truncate the unused section of the transaction log if the database is using the simple recovery model

    4. The log might grow very large, because the log cannot be truncated past the MinLSN. This occurs even if the database is using the simple recovery model, in which the transaction log is generally truncated on each automatic checkpoint.

    Bulk-Logged Recovery Model

    1. A minimally logged operation is performed in the database; for example, a bulk-copy operation is performed on a database that is using the Bulk-Logged recovery model
    2. If the database is using bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option

    3. if the database is using the bulk-logged recovery models, the log is not truncated by automatic checkpoints

    Full recovery Model

    1. If the database is using full, an automatic checkpoint is generated whenever the number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option

    2. if the database is using the full recovery models, the log is not truncated by automatic checkpoints

    Replied on Jan 17 2012 5:45AM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    5

    CHECKPOINT
    Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk. Forces all dirty pages for the current database to be written to disk.

    A checkpoint performs the following processess in the database

    1. writes a record to the log file, making the start of the checkpoint
    2. Stores information for the checkpoint in a chain of checkpoint log records.

    Behavior based on Database recovery models

    Full or Bulk-logged recovery model
    If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option. The log is not truncated by automatic checkpoints if the database is using the full or bulk-logged recovery models

    simple recovery model
    If the database is using the simple recovery model, Automatic checkpoints truncate the unused portion of the transaction log if the database is using the simple recovery model. An automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

    The log becomes 70 percent full. The number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.

    The log is not truncated by automatic checkpoints if the database is using the full or bulk-logged recovery models.

    Replied on Feb 4 2012 3:14AM  . 
    Mitesh Modi
    18 · 10% · 3080

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.