Microsoft has recently provided two interesting features within the SQL Server database engine:
Verifying a Database’s Pages
The ALTER DATABASE statement includes syntax for page verification, with two options: one to enable torn-page detection and a newer checksum verification option. The checksum verification can detect most of the same types of failures as torn-page detection, as well as various hardware-related failures that torn-page detection cannot. However, it is more resource intensive than the older option, so as with any other change, you should test it carefully before rolling it out to production environments.
Enabling the checksum-based page verification scheme for a database called Sales could be done with the following T-SQL:ALTER DATABASE Sales
To enable torn-page detection, use the TORN_PAGE_DETECTION option:ALTER DATABASE Sales
Note that only one of the two page-verification types can be enabled at any given time. To turn off page verification altogether, use the NONE option:ALTER DATABASE Sales
To determine the current page-verification setting for a given database, use the page_verify_option column of sys.databases:SELECT page_verify_option
The column will have a value of 0 if the NONE option is set, 1 for the TORN_PAGE_DETECTION option, and 2 for the CHECKSUM option.
Putting a Database into an Emergency State
Unfortunately, even with data page verification and a very stable database management system like SQL Server, problems do sometimes occur. Should a problem arise, the DBA can set the database to the new emergency state.
This state makes the database read-only and restricts access to members of the sysadmin fixed server role. Although this sounds like a combination of the read-only and restricted user modes, there is a very important enhancement available with the emergency state option. This option can be set on databases marked suspect, thereby allowing the DBA to get in and fix errors or pull out vital data if errors cannot be fixed.
To set the database to the emergency state, use the EMERGENCY option of ALTER DATABASE. To set this mode for a database called Sales, the following T-SQL would be used:ALTER DATABASE Sales
To turn off the emergency state, use the ONLINE option:ALTER DATABASE Sales
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.