SQL Server Mentalist

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

• BISQL # 103 : ‘A to Z’ SQL Command with Syntax, Description  # 2 : ALTER USER
• BISQL # 101 : ‘A to Z’ SQL Command with Syntax, Description  # 1 : ALTER DATABASE
• BISQL # 99 – ‘A to Z’ SQL Command # 1 – List  – Server 2008 R2 AND SQL Server 2012
• Starting of Brand New Series – ‘A to Z’ SQL Command Series

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 CONSTRAINT 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

For more information on SQL server visit any of my post

• BISQL # 99 – ‘A to Z’ SQL Command # 1 – List of Command which are present in SQL Server 2008 R2 AND SQL Server 2012
• Starting of Brand New Series – ‘A to Z’ SQL Command Series
• List of Monthly post of MS BI,SQL & Link Blog – May 2012
• BISQL # 98 – Introduction of Private Cloud with SQL Server and Hyper-V, Resource Link
• Security Guide for SAP on SQL Server 2012 – Whitepaper Released
• BISQL #97 – Laymen to SQL Developer # 9 – Assignment #2 – Relational Model, Schema, Attribute, Database, Key & Relational Query

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 |