SQL SERVER – Checkpoint

Checkpoint is the process that writes all dirty pages to disk for the current database. Checkpoint process help to minimize time during a later recovery by creating a point where all dirty pages have been written to disk.

• Checkpoints can be performed concurrently on the databases
• If a checkpoint fails or is interrupted and a recover is required, the database engine can not recover from the “failure” checkpoint, is necessary to recover from the last successful checkpoint.
• The database engine performs any modification to database pages in memory (for performance reasons) it cause dirty pages, those dirty pages are not written to disk on each modification performed, those are written to disk just when a checkpoint occur.
• When checkpoints occur?
• Before a backup, the database engine performs a checkpoint, in order that all the changes to database pages (dirty pages) are contained in the backup.
• Stopping the server using any of the following methods, they it cause a checkpoint.
• Using Shutdown statement,
• Stopping SQL Server through SQL Server configuration, SSMS, net stop mssqlserver and ControlPanel-> Services -> SQL Server Service.
• When the “SHUTDOWN WITH NOWAIT” is used, it does not execute checkpoint on the database.
• When the recovery internal server configuration is accomplished. This is when the active portion of logs exceeds the size that the server could recover in amount of time defined on the server configuration (recovery internal).
• When the transaction log is 70% full and the database is in truncation mode.
• The database is in truncation mode, when is in simple recovery model and after a backup statement has been executed.

• The time required to perform a checkpoint depends directly of the amount of dirty pages that the checkpoint must write.

[Questions & Answers]

Q: What is Dirty Page?.

A: Dirty pages are the data pages that exists on the buffer cache and have been modified, but not yet written to disk.

Q: What is the checkpoint syntax?

A:

Checkpoint [duration_time]

Duration_time:

• Time desired to perform the checkpoint process.
• Is specified in seconds.
• Must be greater than 0.
• When is omitted, the database engine adjust automatically the duration time to minimize the performance impact.
• Depending of the duration defined, is the amount of resources that SQL Servers assign to checkpoint process. Ex. If the duration_time is defined in 10 seconds but the checkpoints normally is going to take 20 seconds, SQL Server assigns more resources with performance impact in order to accomplish the 10 seconds defined. In the other hand, if the checkpoint is going to take 5 seconds, SQL Server assigns fewer resources than would be assigned by default. Checkpoint process usually takes more/less time than the specified.

SQL SERVER – Factors that can delay log truncation.

The process of log truncation frees space in the log files for reuse by transaction log. Exists factors that can delay lo truncation of the active portion by shrinking or truncated the log.

To check what are the factors that delay the log truncation we can check the sys.databases view, look into the columns: log_reuse_wait and log_reuse_wait_desc.

select log_reuse_wait, log_reuse_wait_desc from sys.databases

The following table shows a brief description of log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view.

log_reuse_wait value	log_reuse_wait_desc value	Description
0	NOTHING	Currently there are one or more reusable virtual log files.
1	CHECKPOINT	No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models).
2	LOG_BACKUP	A log backup is required to move the head of the log forward (full or bulk-logged recovery models only). Note: Log backups do not prevent truncation. When the log backup is completed, the head of the log is moved forward, and some log space might become reusable. Is necessary to take a log frequently
3	ACTIVE_BACKUP_OR_RESTORE	A data backup or a restore is in progress (all recovery models). A data backup works like an active transaction, and, when running, the backup prevents truncation.
4	ACTIVE_TRANSACTION	A transaction is active (all recovery models). A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup. A transaction is deferred (SQL Server 2005 Enterprise Edition and later versions only). A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource.
5	DATABASE_MIRRORING	Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only). .
6	REPLICATION	During transactional replications, transactions relevant to the publications are still undelivered to the distribution database (full recovery model only).
7	DATABASE_SNAPSHOT_CREATION	A database snapshot is being created (all recovery models). This is a routine, and typically brief, cause of delayed log truncation.
8	LOG_SCAN	A log scan is occurring (all recovery models). This is a routine, and typically brief, cause of delayed log truncation.
9	OTHER_TRANSIENT	This value is currently not used.

References.

MSDN. http://msdn.microsoft.com/en-us/library/ms345414.aspx

SQL SERVER – Database Mirroring. Error: 1475, Error: 1478, Error: 1412.

Problem.

When a database mirroring is configured, some error messages are not completely clear when we got it. The following errors are typical while a database mirroring configuration is performed:

Database Mirroring cannot be enabled because the database may have bulk logged changes that have not been backed up. The last log backup on the principal must be restored on the mirror. (Microsoft SQL Server, Error 1475)

The mirror database has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been restored on the mirror database. (Microsoft SQL Server, Error 1478)

The remote copy of database has not been rolled forwarded to a point in time that is encompassed in the local copy of the transactional log. . (Microsoft SQL Server, Error 1412)

I got the error mentioned above when I try to start the synchronization for database mirroring.

Solution / Fix / Workaround.

To start the synchronization successfully follow the next steps:

1. Principal Instance - Take a full backup and a log backup as well

2. Copy the full/log backups from Principal Instance to Mirror instance

3. Mirror Instance - Restore with NORECOVERY option the full backup

4. Mirror Instance – Apply the log backup

5. Principal Instance - Start synchronization

Enjoy!!

SQL SERVER - Error: 1418. The server network address can not be reached or does not exist. Check the network address name and that the ports for the l

Problem.

During the past days I was working in a Database Mirroring Configuration and one of the errors I got was the following:

Msg 1418, Level 16, State 1, Line 1

The server network address "%.*ls" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

It was presented when I run the command:

ALTER DATABASE TestMirror SET PARTNER = 'TCP://hostname:5050'

The server network endpoint did not respond because the specified server network address cannot be reached or does not exist.

Solution.

To solution my problem, I accomplished the following points:

1. The ENDPOINTS should have the status started.

select state_desc from sys.database_mirroring_endpoints

2. The system should not be behind a firewall.

3. Make sure that the principal server instance and mirror instance are listening on the right ports and whether are available.

4. Name pipes protocol enabled.

5. The same username and password were used to run SQL Server services on both machines.

6. The usernames to run SQL Server services have NT Auth Access between both machines.

7. Make sure that the username for the SQL Server services has rights on the SQL Server instance.

How long is running SQL Server

To know how much time my SQL Server is running, we can mention 3 ways:

1. Check on sys.sysprocesses the spid = 1, it is an internal process that is created when the SQL Server instance is started.

2. Check the creation date of the tempdb database. It can be checked in sys.databases on the column create_date.

3. Look into the ERRORLOG. The first line is logged when the SQL Server is started.

For test purpose, you can check the steps described below before and after restart the SQL Server service.

-- 0. -- Current date-time select getdate() ----------------------- 2009-06-03 04:15:27.513 (1 row(s) affected) /* 1. Using sys.sysprocesses Here we can see how much time the SQL Server using the login_time column and sys.sysprocesses */ select login_time as SQLStart, datediff(mi, login_time, getdate()) Running_in_Minutes, datediff(hh, login_time, getdate()) Running_in_Hours, datediff(dd, login_time, getdate()) Running_in_Days from sys.sysprocesses where spid = 1 Result SQLStart Running_in_Minutes Running_in_Hours Running_in_Days ----------------------- ------------------ ---------------- --------------- 2009-06-03 04:13:11.793 2 0 0 (1 row(s) affected) /* 2. Using sys.databases Here we can see the column create_date for the TEMPDB database. */ select create_date as SQLStart, datediff(mi, create_date, getdate()) Running_in_Minutes, datediff(hh, create_date, getdate()) Running_in_Hours, datediff(dd, create_date, getdate()) Running_in_Days from sys.databases where name = 'tempdb' Result. SQLStart Running_in_Minutes Running_in_Hours Running_in_Days ----------------------- ------------------ ---------------- --------------- 2009-06-03 04:13:11.763 2 0 0 (1 row(s) affected) /* 3. Using ERRORLOG Executing the SP xp_readerrorlog, we can see the date and time exactly that SQL Server starts to logged activity. */ exec master..xp_readerrorlog Result. LogDate ProcessInfo Text ----------------------- ------------ ---------------------- 2009-06-03 04:13:08.290 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation

Orphan MSI Entries during SQL Server Upgrade 2005 to 2008.

In the past days I was working and fixing a SQL Server upgrade from SQL 2005 to 2008.

Background of my environment.

1. Previous upgrades to MSSQL 2008 were performed unsuccessfully for someone else.

2. Some MSSQL 2008 new installations were performed unsuccessfully for someone else.

Because existed several attempts unsuccessful, the server has orphan entries on the windows registry about SQL Server 2008. Those entries were cleaned in manually way but the upgrade still having problems.

The error was:

MSI (s) (34:24) [19:02:40:273]: Specified instance

{FBD367D1-642F-47CF-B79B-9BE48FB34007} via transform

:InstID03.mst;:InstName01.mst is already installed. MSINEWINSTANCE

requires a new instance that is not installed.

It is because the MSI Inventory still has orphan entries of SQL Server 2008 for the installations unsuccessful, that’s way the upgrade can not be performed.

To fix the issue I use the following tools to clean the MSI Inventory:

• MSI (Microsoft/Windows Installer) Inventory.
• Windows Installer CleanUp Utility.

Steps

1. Get the MSI inventory for all the software installed on my box. Here I use the MSI Inventory utility.

Ex.

C:\tools\msi inventory\msiinv>msiinv.exe -v > msiinv_out.txt

2. Look into the outcome if exists entries for MSSQL 2008. In my case I found some entries for MSSQL 2008. Here we can see that the product code is the same than the error mentioned above.

Microsoft SQL Server 2008

Product code: {FBD367D1-642F-47CF-B79B-9BE48FB34007}

Product state: (5) Installed.

Package code: {11FE9DE4-380B-45FE-985E-3EC1EFF9D159}

Version: 10.00.xx

AssignmentType: 1

Publisher: Microsoft Corporation

Language: 1033

3. Windows Installer CleanUp Utility was used to clean the orphan entries on the MSI Inventory.

4. After clean the orphan entries, if you get again the MSI Inventory the SQL Server entries do not must be there. After I clean my orphan entries, the Upgrade to 2008 can be perfomed in the normal way.

Please refer to the following link to download the utilities.

To download MSI Inventory:

http://cid-27e6a35d1a492af7.skydrive.live.com/self.aspx/Blog%7C_Tools/msiinv.zip

To download Windows Installer CleanUp Utility:

http://support.microsoft.com/default.aspx?scid=kb;en-us;290301

For reference of MSI inventory please check the following blog:

http://blogs.msdn.com/astebner/archive/2005/07/01/using-msiinv-to-gather-information-about-what-is-installed-on-a-computer.aspx

Database Recovery Models.

Database Recovery Models.

The feature “database recovery model” was included by Microsoft since SQL Server 2000. It helps us to manage the transaction log. Database Recovery model is part of the recovery options that SQL Server offers. The other option besides of recovery model is PAGE_VERIFY (CHECKSUM or TORN_PAGE_DETECTION), this option detect corrupt pages of the database because I/O problems. PAGE_VERIFY is not explain in this article.

SQL Server 2000, 2005 and 2008 offers three recovery models:

• Simple Recovery Model
• Bulk-logged Recovery Model
• Full Recovery Model

The recovery model is defined by each database and it can be changed according to business necessities. The following information show us details of each recovery model.

Simple Recovery Model.

- Help to minimize the overhead over the transaction log, because transaction log is not backed up.

- Reduces the space used for transaction log on disk, because the transactions are not remaining on the transaction log, they are truncated.

- This recovery model logs minimal amount of information on the transaction log, logging only the information necessary to ensure the database consistency after a restore from a backup or after system crash.

- SQL Server truncates the log when a checkpoint occurs. Checkpoint truncates the log removing committed transactions, it is performed automatically by SQL Server at regular intervals. The intervals can be configured with Recovery Interval option using sp_configure. Is important to know that exists factors that can delay the log truncation.

- No log backup is supported. It means that if a disaster happens, a restore to point in time can not be performed and the database must be restore until the last full backup or differential backup. Therefore, the databases under this recovery model must be backed up frequently to avoid loss of data as much as possible.

- Simple recovery model is advisable to use for user databases, development and testing scenarios. Is not recommendable to use in Production systems. For production systems is recommendable Full Recovery Model.

Bulk-logged Recovery Model.

- Bulk-logged recovery model provide better protection of data than simple recovery model.

- Bulk-logged recovery model works in similar way than full recovery model. The only difference is the way to handle bulk operations. If there are not bulk operations running, bulk-logged recovery model works similar than full recovery model.

- This recovery model offers minimal logging for Bulk operations as BCP, SELECT INTO, BULK INSERT, CREATE INDEX, reducing the log space consumption.

- Microsoft recommends use bulk-logged recovery model for short periods of time. A best practice is switch the recovery model from full recovery model to bulk-logged recovery model before perform bulk operations and restore it to full recovery model when those operations are completed.

- This model is recommended for user database where bulk operations are performed when no users using the database.

- For log backup that contains bulk operations, the database can be recovered just to end of the log backup and the point in time (STOPAT) recovery is not allowed. If the log backup does not have bulk log operations logged, then the point in time recovery is allowed.

Full Recovery Model.

- This model is recommended for production databases, critical environments and in environment where you cannot afford to lose data.

- Support the recovery to point in time, assuming the log backups are complete up to the point in time desired.

- This recovery model offers fully logs for all the transaction on the database. Even logs any bulk operation.

- Transaction log, logs all the transactions of the database and the transactions are retain until a log backup is performed. Log backup is supported.

How to check the recovery model

To know the current recovery model the DBA can check it using TSQL or Graphical tool (SSMS).

Using TSQL:

SELECT DATABASEPROPERTYEX('SID', 'RECOVERY')

Or

select recovery_model, recovery_model_desc from sys.databases where database_name = 'SID'

How to switch the recovery model

The recovery model can be modified on TSQL and with Graphical tool (SSMS).

Using TSQL:

--Switch to simple ALTER DATABASE <SID> SET RECOVERY SIMPLE GO --Switch to Bulk logged ALTER DATABASE <SID> SET RECOVERY BULK_LOGGED GO --Switch to full ALTER DATABASE <SID> SET RECOVERY FULL GO

To switch the recovery model is necessary to have permissions to execute ALTER statement.

Important Considerations when switching recovery model

The recovery model should be defined depending the business necessities. But, is important to know the impact that has switching between one recover model to another.

Simple -> Full / Bulk logged

This switch is recommendable when is a productive database. For simple recovery model, the transaction log is truncated in every checkpoint, but, in full/bulk logged the transaction log is not truncate until a log backup is performed. When switch to bulk logged every transaction is fully logged except bulk transaction as SELECT INTO, but in full recovery model all the transaction are fully logged.

Bulk logged -> Full

When bulk task are finished is necessary switch to full recovery model then is recommendable take a log backup.

Full -> Bulk Logged

This switch is recommendable when a bulk task is going to be performed in order to minimize the log space used. A log backup should be performed before switch to Bulk logged and is recommendable no users connected when bulk logged recovery model is defined.

Full/Bulk logged -> Simple

This switch is not recommendable for productive databases. The log will be truncated in every checkpoint and the log backup is not supported. A log backup should be performed before switch to simple. You switch to simple before a full backup to decrease the file size of the backup.

Conclusion

The person in charge of the database should be necessary to understand the recovery models in SQL Server also the impact that has to use each of them; this article shows important information to understand that.