Home > A to Z SQL Command, Link, Query, SQL > BISQL # 104 : ‘A to Z’ SQL Command with Syntax, Description, Usage and Example # 3 : Checkpoints

BISQL # 104 : ‘A to Z’ SQL Command with Syntax, Description, Usage and Example # 3 : Checkpoints

Hi Folks,

Continuing from last post on ‘A to Z’ SQL Command Series, In this post we are going to cover Alter User command

Pervious links of this series are as follows

    Our list for ‘A to Z’ SQL Command Series is as follows :-

ALTER DATABASE , ALTER GROUP, ALTER TABLE, ALTER TRIGGER, ALTER USER  BEGIN, CHECKPOINT (This Post), CLOSE, COMMIT, CREATE AGGREGATE, CREATE CAST, CREATE image_thumb1CONSTRAINT TRIGGER, CREATE CONVERSION, CREATE DATABASE, CREATE DOMAIN, CREATE FUNCTION, CREATE GROUP, CREATE INDEX, CREATE LANGUAGE, CREATE OPERATOR, CREATE OPERATOR CLASS, CREATE RULE, CREATE SCHEMA, CREATE SEQUENCE, CREATE TABLE, CREATE TABLE AS, CREATE TRIGGER, CREATE TYPE, CREATE USER, CREATE VIEW, DEALLOCATE, DECLARE, DELETE, DROP AGGREGATE, DROP CAST, DROP CONVERSION, DROP DATABASE, DROP DOMAIN, DROP FUNCTION, DROP GROUP, DROP INDEX, DROP LANGUAGE, DROP OPERATOR, DROP OPERATOR CLASS, DROP RULE, DROP SCHEMA, DROP SEQUENCE, DROP TABLE, DROP TRIGGER, DROP TYPE, DROP USER, DROP VIEW, END, EXECUTE, FETCH, GRANT, INSERT, LOAD, MOVE, RESET, REVOKE, ROLLBACK, SELECT, SELECT INTO, SET, SET CONSTRAINTS, SET SESSION AUTHORIZATION, SET TRANSACTION, START TRANSACTION, TRUNCATE, UPDATE

In this article we are going to cover for ALTER USER with following points

  • Introduction to Checkpoints Command
  • Syntax for Checkpoints Command
  • Description of Checkpoints Command
  • Use of Checkpoints Command 
  • What are event that are caused by check points .
  • Example of Checkpoints Command
  • Input and Output for Checkpoints Command

Introduction to Checkpoints Command

Checkpoint are most important aspect in any programming language or must important where we are processing very critical process this are nothing but force a transaction log checkpoint.

checkpoint are This minimizes the active portion of the log that must be processed during a full recovery of a database.

Syntax for Checkpoints Command

CHECKPOINT [ checkpoint_duration ]

Description of Checkpoints Command

A checkpoint is a point in the transaction log sequence at which all data files have been updated to reflect the information in the log. All data files will be flushed to disk.

Specifies the requested amount of time, in seconds, for the manual checkpoint to complete. When checkpoint_duration 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, the Database Engine adjusts the checkpoint duration to minimize the performance impact on database applications. checkpoint_duration is an advanced option.

Use of Checkpoints Command

Checkpoints also occur:

When a database option is changed with ALTER DATABASE. A checkpoint is executed in the database in which the option is changed.

When a server is stopped, a checkpoint is executed in each database on the server. These methods of stopping Microsoft® SQL Server™ 2000 checkpoint each database:

  • Using SQL Server Service Manager.
  • Using SQL Server Enterprise Manager.
  • Using the SHUTDOWN statement.
  • Using the Windows NT command net stop mssqlserver on the command prompt.

Using the services icon in the Windows NT control panel, selecting the mssqlserver service, and clicking the stop button.

A database is in log truncate mode when both these conditions are TRUE:

  • The database is using the simple recovery model.

One of these events has occurred after the last BACKUP DATABASE statement referencing the database was executed:

  • A BACKUP LOG statement referencing the database is executed with either the NO_LOG or TRUNCATE_ONLY clauses.
  • A nonlogged operation is performed in the database, such as a nonlogged bulk copy operation or a nonlogged WRITETEXT statement is executed.
  • An ALTER DATABASE statement that adds or deletes a file in the database is executed

    What are the Events that causes CHECKPOINT?

1. Before Database Backup

Before a database backup, the Database Engine automatically performs a checkpoint so that all changes to the database pages are contained in the backup.

2. Active Log exceeds recovery interval

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.

3. The log becomes 70 per cent 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:

BULK_LOG Recovery: 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

4. Stopping a SQL Server issues a checkpoint in each database on the server

5. CHECKPOINT:
A CHECKPOINT statement is explicitly executed. A checkpoint occurs in the current database for the connection.

6. Database Shutdown
An activity requiring a database shutdown is performed. For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

    Example of Checkpoints Command

Select 1
CHECKPOINT1
Select2

    Input and Output for Checkpoints Command

image

For more information on SQL server visit any of my post

Hope this helps and you got how to use Checkpoints Command !!

Those who have not ye subscribe my Blog yet they can subscribe it !So that I can post you @ real time and all sort of knowledge in your mail without Zero spamming !!

Happy Learning and Sharing !!

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

Link Resource Website

For More information related to BI World visit my Mentalist Blog

Link Resource Blog >> Daily Interesting links

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS |

Categories: A to Z SQL Command, Link, Query, SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,115 other followers

%d bloggers like this: