-1

Lately I experienced a corrupt DB because of a defective harddisk. SQL Server 2005 detected the issue and put entries into Windows event log about inconsistent DB and invalid page checksums. But it just tried to deal with it. It kept on making daily backups of the already corrupt DB and after each full backup the transaction logs got deleted. Therefore I lost several days of DB changes.

The question is: is there an option somewhere which will just shut down the affected DB or even the whole SQL Server if a corruption is detected? It does not make sense to go on and makes things only worse.

René

Krumelur
  • 119
  • 4

2 Answers2

2

A full backup does not free the transaction log in full or bulk recovery mode. Only log backups do, or having the database in simple recovery mode.

The database is not taken offline in case of page checksum error (or any inconsistency error) intentionally. The database can still offer plenty of service, only queries that need to scan that particular page are affected. The page can be individually restored by performing a page restore (log backups are required). The database would be taken offline if the error occurs in one of the critical pages of the database, where any query would be affected.

Running backup does not replace the need to run database consistency checks. A backups takes the snapshot of the database as is and does not do any validation of the content. There are many ways to monitor for errors like page checksum errors. SQL Agent can be configured to send a mail or a pager notification when one occurs. WMI can be used. SCCM can do this at an enterprise level.

Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23
-1

CHECKSUM is an option for page verification that is available at the database instance level and we can see what level of verification each of our databases are currently using by the following query:

SELECT name, page_verify_option_desc FROM sys.databases

It doesn't make sense to shut down the whole SQL Server when corruption detected.

Nowadays the Page Verify option is set to CHECKSUM, and this is the default option. For Pre-SQL 2005 versions this was not the case, so occasionally when looking into database instances that have been migrated from earlier versions, we might see one of the other Page Verify options selected; they are TORN PAGE DETECTION or NONE.

Priyanka
  • 1
  • 1