Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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é

share|improve this question
add comment

migrated from stackoverflow.com Nov 10 '10 at 6:49

This question came from our site for professional and enthusiast programmers.

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.