RS First Dynamics NAV Blog


...from NAV 3.60 to NAV 2013
Archivio Posts
Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

Recovering Sql DB from "Suspect state"

I've my database, testdb, ended up in Suspect state. The SQL log shows " I/O error 38(Reached the end of the file.) detected during read at offset xxxxxxxxxx in file '\testdb_Data.MDF'" during recovery. I do not have backup to restore the database from.

So to run DBCC CHECKDB, I tried to put the database in emergency(bypass recovery) mode using
update sysdatabases set status = 32768 where name = 'testdb'

DBCC CHECKDB showed some allocation and consistency errors and suggested "repair_allow_data_loss" as minimum repair level.

Now to run
DBCC CHECKDB('testdb', repair_allow_data_loss)

I've to put database in SINGLE USER mode. For that I started SQL server by command
sqlservr.exe -c -m

Now when I try to run DBCC CHECKDB with repair option it says "Attempt to BEGIN TRANSACTION in database 'testdb' failed because database is in BYPASS RECOVERY mode."

COMANDI CHANGE STATUS

 USE [master]

GO

ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

And do remember to make the database MULTI_USER after the restore.

USE [master]

GO

ALTER DATABASE <dbname> SET MULTI_USER

Categoria: Sql Server ALL
giovedì, 10 set 2009 Ore. 10.59

Messaggi collegati


Statistiche
  • Views Home Page: 471.801
  • Views Posts: 889.159
  • Views Gallerie: 0
  • n° Posts: 343
  • n° Commenti: 0
Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003