SQL Server database CHECKSUM page varify option

In SQL Server 2005, there is a new page varify option, CHECKSUM, which can detect the page corruption between time a database page is written to disk and read from subquently by SQL Server. This option (CHECKSUM) is more restricter than TORN_PAGE_DETECTION, and you can have either one of then eabled. Therefore, it is recommended to use CHECKSUM as page varify option. You may has some performance concerns about this option, however, Linch Shea (Blog) bloged that there almost non/mimnum performance impact on the SQL Server based on his benchmarking tests (check here).

You can use the following script to get all the user databases that are not using CHCEKSUM page varify option, update them accordingly.

USE master;
GO

-- Check before update
SELECT	name,
		page_verify_option /* 0 = NONE, 1 = TORN_PAGE_DETECTION, 2 = CHECKSUM */
FROM	sys.databases
WHERE	database_id > 4 AND /* Exclude the system databases */
		name NOT IN ('ReportServer', 'ReportServerTempDB') AND /* Exclude the reporting services databases or any other*/
		page_verify_option <> 2;
		
		
-- Update the databases

SELECT	name,
		page_verify_option /* 0 = NONE, 1 = TORN_PAGE_DETECTION, 2 = CHECKSUM */
INTO	#temp
FROM	sys.databases
WHERE	database_id > 4 AND /* Exclude the system databases */
		name NOT IN ('ReportServer', 'ReportServerTempDB') AND /* Exclude the reporting services databases or any other*/
		page_verify_option <> 2;
		

DECLARE @Command NVARCHAR(MAX);
DECLARE @DatabaseName SYSNAME;

WHILE EXISTS(SELECT * FROM #temp WHERE page_verify_option <> 2)
BEGIN
	SELECT TOP(1) @DatabaseName = name FROM #temp WHERE page_verify_option <> 2;
	SET @Command = 'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + ' SET PAGE_VERIFY CHECKSUM WITH NO_WAIT';
	
	EXECUTE(@Command);
	
	UPDATE #temp SET page_verify_option = 2 WHERE name = @DatabaseName;
END;

DROP TABLE #temp;
GO


-- Check after update
SELECT	name,
		page_verify_option /* 0 = NONE, 1 = TORN_PAGE_DETECTION, 2 = CHECKSUM */
FROM	sys.databases
WHERE	database_id > 4 AND /* Exclude the system databases */
		name NOT IN ('ReportServer', 'ReportServerTempDB') /* Exclude the reporting services databases or any other */ AND
		page_verify_option <> 2;
Advertisements

About Alex Feng

SQL Server DBA in Alibaba Cloud Computing
This entry was posted in Database Engine, Database Options and tagged , . Bookmark the permalink.

One Response to SQL Server database CHECKSUM page varify option

  1. You are a very capable individual!

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