Monday 11 April 2011

ASE : Dealing with a Corrupted Database

Hardware failures can result in databases that are corrupt and will not open
upon restart of the server. In some cases the database is marked suspect, and
then cannot be opened.
The best way to deal with a database in this state is to
nuke it and reload it from a backup. Here's a code snippet which will
force the drop to occur, when drop database fails.


/* note: X=the dbid of the database (from sysdatabases) */

use master
go
sp_configure "allow updates",1
go
begin tran
go
update sysdatabases set status = 320 where dbid = X
go


/* always make sure the status has been changed to 320 */

select dbid, status from sysdatabases where dbid = X
go
commit tran
go
sp_configure 'allow updates', 0
go
checkpoint
go


/* recycle the server */

dbcc dbrepair (database_name, dropdb)
go


/* now, recycle the server and rebuild the database */

No comments:

Post a Comment