SUSPECT database

Question

SUSPECT database

Christo Botha

Old Hand

Points: 315
More actions
July 30, 2007 at 6:10 am

#179946

Hi,
My laptop hanged as I was running a large table update and when I restarted my database was marked as 'SUSPECT'.
Running sp_resetstatus @dbname = "mydb" says that the flag has allready been reset but does not make any diffference.
When I try
SP_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
UPDATE master..sysdatabases set status = -32768 WHERE name = 'mydb'
GO
SP_CONFIGURE 'allow updates', 0
RECONFIGURE WITH OVERRIDE
I get the error message
Update or insert of view or function 'master..sysdatabases' failed because it contains a derived or constant field.
Is there any other alternative to get into emergency mode to recover the data in the database?

Rgds and Thx
Christo

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

You did not mention how you restarted your server. If you turned off its power, or do something else without allowing your updates to rollback, your log file is damaged.

My suggestion is:

1. detach your database;

2. re-attach your data file, and create a new log file at the same time (your log file is most likely damaged if you restarting your services not gracefully).

Of course, your imcomplete transactions will be lost in the above procedures.

Hopefule, the above is helpful.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

BTW, your update to the sysdatabases table did not work because it is not a table in SQL 2005 anymore.

It is a view based on the sys.databases table that is made to look like the old table for backward compatibility.

If you do an sp_helptext sysdatabases you will see that the status field is now a calculated field in the view.

You need to update the underlying table, not the view.

Ivan Kurlat SSC Veteran Points: 284 More actions · Answer 3

I guess the issue was first open from SQL Server 7.0, but SQL 2005 for emergency mode you have to execute:

alter database mydb

set emergency

go

You don't need to restart SQL Server Service for this change.

To rollback this you exeecute

alter database mydb

set online

go

Paul Randal One Orange Chip Points: 29544 More actions · Answer 4

This is terrible advice. Most suspect databases WILL NOT reattach successfully.

Cristo - did the database/log run out of space? Why did it go suspect in the first place? You may be able to just expand the log or data files and then re-start recovery to get it out of suspect mode.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

nishantha0 Grasshopper Points: 21 More actions · Answer 5

Paul Randal (9/12/2007)
This is terrible advice. Most suspect databases WILL NOT reattach successfully.
Cristo - did the database/log run out of space? Why did it go suspect in the first place? You may be able to just expand the log or data files and then re-start recovery to get it out of suspect mode.

hi i am nishantha

my database suspect how can i recovary

thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

nishantha0 (2/19/2009)
hi i am nishantha
my database suspect how can i recovary
thanks

Please post your problem in a new thread and explain, in detail, what happened and what the problem is. Check the SQL error log for related errors.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Kaushalendra Singh Valued Member Points: 74 More actions · Answer 7

HI nishantha,

you run the this screep IN Query Analyzer ,

USE Master

GO

-- Determine the original database status

SELECT [Name], DBID, Status

FROM master.dbo.sysdatabases

GO

-- Enable system changes

sp_configure 'allow updates',1

GO

RECONFIGURE WITH OVERRIDE

GO

-- Update the database status

UPDATE master.dbo.sysdatabases

SET Status = 24

WHERE [Name] = 'DATABASE NAME'

GO

-- Disable system changes

sp_configure 'allow updates',0

GO

RECONFIGURE WITH OVERRIDE

GO

-- Determine the final database status

SELECT [Name], DBID, Status

FROM master.dbo.sysdatabases

GO

kaushalendra Singh