Systems Engineering and RDBMS

Archive for December 18th, 2008

SQL Server 2005 SP3 released

Posted by decipherinfosys on December 18, 2008

The much awaited SP3 has been released for SQL Server 2005. You can download it from here. And the list of the bugs that are fixed in SP3 are listed in this KB article. And also, here is an excellent post by Aaron Bertrand giving more information on the service pack.

Posted in SQL Server | Leave a Comment »

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;

Posted in SQL Server | Leave a Comment »