Database marked Suspect

Question

Database marked Suspect

Doug Simmons

Ten Centuries

Points: 1373
More actions
November 21, 2006 at 8:07 pm

#368079

I have a database under development. I use computers in different locations and regularly copy the database's mdf and ldf files and overwrite the old files with the new as I arrive at each location.
This time after overwriting, the database is marked Suspect.
Is there any way to find out what exactly is wrong with the data?
(There are no problems with disk space or anything like that.)
Doug

Viewing 11 posts - 1 through 10 (of 10 total)

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

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 1

I would have favoured doing a backup of the original database to a device, copying the device across to the new location and then doing a restore rather than simply copying mdf and ldf files.

If we have a database that goes 'suspect' (very rare in our case) we do the following.

Stop and Start the SQL Server Services

(Stop: SQL Agent + MS SQL Server)

(Start:: MS SQL Server + SQL Agent)

All is OK then

Madame Artois

Doug Simmons Ten Centuries Points: 1373 More actions · Answer 2

Thanks very much for your reply.

Unfortunately I have tried this but there is no change.

Does anyone know if there is a way to check the data or know what has actually been detected by SQL Server to generate this error?

Doug

Al Cook SSCertifiable Points: 6735 More actions · Answer 3

Check this link to help you get out of your situation:

http://www.sqlservercentral.com/columnists/bknight/unmarksuspect.asp

Doug Simmons Ten Centuries Points: 1373 More actions · Answer 4

Using CheckDB I get the error:

The header for file 'C:\MSSQL7\data\mydb_Data.MDF' is not a valid database file header. The PageAudit property is incorrect.

Anyone know what PageAudit means? Can it be corrected. No other DBCC commands work, I suppose because it's the file header that is actually faulty. I always get the error - Can't open database.

Doug

Pankaj Khanna Say Hey Kid Points: 707 More actions · Answer 5

Hi,

Whenever SQL Server can not open anu file required for the proper functioning of the database, it mark that database "Suspect". In your case, as you have already run the CHECKDB and you happen to know that the PageAudit is incorrect, I would suggest you to run the follwoing command:

DBCC CHECKDB ('<database-name>', REPAIR_REBUILD) WITH ALL_ERRORMSGS

This command should check and repair the structural and allocation integrity of your database.

Good luck.

Best Regards,

Pankaj

Pankaj Khanna
Database Administrator - SQL Server 2000

Keep hope to keep you intact...

Doug Simmons Ten Centuries Points: 1373 More actions · Answer 6

When I try to run this I just get the message - Can't open the database.

I can't imagine why. It still works okay on the other computer, but copies of it all have this error on this computer. Yet I have copied this database backwards and forwards many times before. It's really odd.

Never mind, I'll rebuild the database and re-enter the test data.

Thanks very much for all your help.

Doug

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 7

Hi,

I have a solution to your problem...Just you have to send your *.mdf file to me and i will send to you a backup of it to you...

Thanks...

Amit Gupta...

***************

Solution is not always easy.....

***************

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 8

Oh!!,

I forgot to given my mail address ::

mailgupta_amit@yahoo.com

Amit.82.gupta@gmail.com

mailgupta_amit@indiatimes.com

Thanks... & Regards..

Amit Gupta...

MohammedU SSCoach Points: 19644 More actions · Answer 9

Looks like to me some kind of corruption...

Are you sure you are attaching the sql 2000 files to sql 2000 server?

Try to run sp_resetstatus and see happens.

Otherwise you can update the sysdatabases table to make db emergency mode and bcp out the data...

Use Master

Go

sp_configure 'allow updates', 1

reconfigure with override

Go

-- Set the database in Emergency (bypass recovery) mode:

-- note the value of the status column for later use

select * from sysdatabases where name = '<db_name>'

begin tran

update sysdatabases set status = 32768 where name = '<db_name>'

-- Verify one row is updated before committing

commit tran

go

sp_configure 'allow updates', 0

reconfigure with override

Go

MohammedU
Microsoft SQL Server MVP

Doug Simmons Ten Centuries Points: 1373 More actions · Answer 10

Thanks so much for your kind offers and advice, but I already rebuilt the database and will use a proper backup to transfer the data next time.

Thanks again,

Doug