Systems Engineering and RDBMS

Page Verification and Emergency State in SQL Server 2008

Posted by decipherinfosys on December 18, 2008

Prior to SQL Server 2008, we could do torn page detections in SQL Server by setting up the option for the database or also by using the ALTER DATABASE command. In SQL Server 2008, we can also have a checksum based data page verification done by using the same command. It can not only detect the failures that you can otherwisse detect with torn page detection but also hardware related failures which go un-noticed by the torn-page detection feature. But since it does all that, it is also more resource intensive. First, you should check whether anything is already set for the database – you can do so using this query:

select page_verify_option from sys.databases where name = ‘DecipherTest’;

If the value is 1 then it is set for torn page detection, if it is 2, then it is set for checksum and if it is 0, then nothing has been set. This should also tell you that you can have only either torn page set or the checksum option set.

In order to set the checksum option, you can use the ALTER DATABASE command:

ALTER DATABASE DECIPHERTEST SET PAGE_VERIFY CHECKSUM;

And if you want to set it to 0, you can just replace CHECKSUM with NONE in the command above. In addition to this, in SQL Server 2008, we can also set a database in an emergency state. It essentially makes the database read only and only the members of the sysadmin fixed server role can then access the database. It is especially useful if you have encountered a “suspect” database state before. You can now set it in an emergency mode and fix errors.  One can set it easily using the ALTER DATABASE command:

ALTER DATABASE DECIPHERTEST SET EMERGENCY;

And in order to turn the emergency state off, just execute:

ALTER DATABASE DECIPHERTEST SET ONLINE;

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

 
%d bloggers like this: