Best Practice No.35

In your backup strategy avoid taking daily backups of your read-only databases, monthly full backup is more than enough.

SQLServer Day 14th September 2013 | Gurgaon

Hi Friends, cordially invites you to SQL Server Day, the monthly dose of SQL Server knowledge & learning. And this time we are coming to two cities in India; Gurgaon & Hyderabad. This is yet another opportunity to learn.

The Sessions are Troubleshooting Deadlocks, How to use SQLDiag and SQL Nexus, Hekaton (In-Memory) Database SQL Server 2014 & lesser known Enhancements of SQL Server 2012. Events are hosted at Microsoft premises in respective cities so that your experience is fabulous.

Register Fast and block your seat. It is absolutely free. Also forward this email to your friends and colleagues to spread awareness.


To Register visit:


See you at the event!

What is Checkpoint in SQL Server?

Checkpoint is an internal process that writes all dirty pages (modified pages) from Buffer Cache to Physical disk, apart from this it also writes the log records from log buffer to physical file. Writing of Dirty pages from buffer cache to data file is also known as Hardening of dirty pages.

It is a dedicated process and runs automatically by SQL Server at specific intervals. SQL Server runs checkpoint process for each Database individually.

Checkpoint helps to reduce the recovery time for SQL Server in the event of unexpected shutdown or system crash\Failure.


In SQL Server 2012 there are four types of Checkpoints:

Automatic: This is the most common checkpoint which runs as a process in the background to make sure SQL Server Database can be recovered in the time limit defined by the Recovery Interval – Server Configuration Option.

Indirect: This is new in SQL Server 2012. This also runs in the background but to meet a user-specified target recovery time for the specific Database where the option has been configured. Once the Target_Recovery_Time for a given database has been selected this will override the Recovery Interval specified for the server and avoid Automatic Checkpoint on such DB.

Manual: This one runs just like any other T-SQL statement, once you issue checkpoint command it will run to its completion. Manual Checkpoint runs for your current Database Only. You can also specify the Checkpoint_Duration which is optional, this duration specifies the time in which you want your checkpoint to complete.

Internal: As a user you can’t control Internal Checkpoint. Issued on specific operations such as:
1. Shutdown initiates a Checkpoint operation on all databases except when Shutdown is not clean (Shutdown with nowait)
2. If the recovery model gets changed from Full\Bulk-logged to Simple.
3. While taking Backup of the Database.
4. If your DB is in Simple Recovery model, checkpoint process executes automatically either when the log becomes 70% full, or based on Server option-Recovery Interval.
5. Alter Database command to add or remove a data\log file also initiates a checkpoint.
6. Checkpoint also takes place when the recovery model of the DB is Bulk-Logged and a minimally logged operation is performed.
7. DB Snapshot creation.

Happy Learning 🙂

Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox

Best Practice No.34

Rebuild only those Indexes where the fragmentation level exceeds the threshold value of 30.

Best Practice No.33

Configure a dedicated NIC (network card) card for your Backups on your busy systems.

Best Practice No.32

Always use SQL Server Configuration Manager to change Service account for any SQL Related Service.

SQL Tip-8 | How to script out multiple SQL Server jobs at once

Migrating SQL Server jobs from one server to another is a critical task and there are many alternatives available for the same, the easiest one available is to script out each and every job and re-create them on the destination server.

If there are a few jobs you can very well script out each and every job individually, but once the count of jobs or number of servers to be migrated increases this seems to be another challenge.

For this reason SQL Product team provided a very convenient (but not so popular) option to script out all the jobs.

All you need to do is select jobs in the object explorer and open the Object Explorer Details Tab (shortcut key is F7) – Once the object explorer details tab is in view, you can now select multiple jobs by holding Ctrl (control) key.

Once all the desired jobs are selected right click and select script job as from the context menu.  Please refer the attached Screenshot to get more idea.



Hope you enjoyed the post, feel free to leave a comment. 🙂

Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox

Best Practice No.31

While referring objects in Stored Procedures, functions, views and Triggers always use Fully Qualified (Multi Part) Name of the object.