Month: August 2013

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 …

Best Practice No.34

Rebuild only those Indexes where the fragmentation level exceeds the threshold value of 30. Related posts: Best Practice No.12 Best Practice No.9

Best Practice No.33

Configure a dedicated NIC (network card) card for your Backups on your busy systems. Related posts: Best Practice No.14 Best Practice No.4 Best Practice No.20 Best Practice No.30 Best Practice No.7

Best Practice No.32

Always use SQL Server Configuration Manager to change Service account for any SQL Related Service. Related posts: Error while opening SQL Server Configuration Manager – Fixed SQL Tip-2 | Make Sure the startup type of your SQL Service is Automatic* What all changes require a restart of SQL Service? SQL Server AWE Enabled Option | …

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 …

Best Practice No.31

While referring objects in Stored Procedures, functions, views and Triggers always use Fully Qualified (Multi Part) Name of the object. Related posts: Best Practice No.4 SQL Server Management Objects (SMO)

Best Practice No.30

Enable “remote admin connections” on all your SQL Servers to be able to take DAC (Dedicated Admin Connection) remotely. Related posts: Best Practice No.13 Best Practice No.15 Best Practice No.24 Best Practice No.8 Best Practice No.26

SQL Tip-7 | Different ways to enable a Trace Flag

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. There are many documented and undocumented trace flags. For example Trace Flag 1222 that can be used to get Deadlock Graph. In Trace Flag 1222 you get a detailed information about all the processes running and participating in …