Userdatabase marked with suspect

[Daniel Schneider]

Hi all,
I have a big problem with a user Database on SQL-Server7.0. The Application which runs on this Database is Baan. So I handled datainput who creates many new tables. Short
before the end of this Process, the Database crashes. Now I can't get access to the Database neither with the application BaaN nor with the enterprise manager. The system is not able to rebuild the database.
So the main problem is, I have no Backup!
At the moment I have detached the Database, to install SP3. When I run the query exec sp_detach_db 'baandb' there was the error message" Errorcode 947, severity code 16, state 1, row 0".
Now I cannot atach the Database again, because of error 3624, severity code 20, state 1, row 1. Location: page.cpp:3008
expression: spaceneeded < spaceContig && spaceneeded <= space_usable.
This is exactliy the error, from the first crash.

Can anyone tell me, how to repair the database?

If there are some helpful tips, you will rescue my life!
Thanks for all help!

Daniel :-)

[Martin Schmidt_2]

Hi Daniel,

Make backup of your (probably corrupt) DB.
Put database in Emergency Mode.
Rebuild the log using "dbcc rebuild_log"
Ran "dbcc checkdb"
Any further errors?
If, post them.
Or run "dbcc checkdb repair_allow_data_loss"
Run "dbcc checkdb" again.

Found another entry in our database where they did the following for an SMS DB in SQL 7.0:

"- Had cust run dbcc rebuild_log
- Made sure that the status was set to 0 for this database 'SMS_XXX''
- Stopped the server and restarted successfully
- Looked into the errorlog and made sure there were no errrors

- sp_dboption 'mvdb', 'single user', true
go
dbcc checkdb('SMS_BSB', 'repair_allow_data_loss')
go

Ran without errors

dbcc checkdb('SMS_BSB')
go

Ran without errors.

Stopped SQL service
Removed single user mode parameter from registry

Started SQL service
Service started properly.
Dabase is up and running.

Started SMS services
All services started properly"

So please take this as a reference and do the appropriate steps.

Regards, Martin.

[Daniel Schneider]

thanks martin,
how can I backup the database? Do you mean to copy the *.mdf-File in Explorer?
The Database is detached. So I think, there is no way for the dbcc-Utilities?

Best regards
Daniel :-)

[Martin Schmidt_2]

Yes. Make a copy of the mdf and ldf files in \mssql7\data to another location.

You are probably right that you cannot run the commands if database is not attached (I am not the real SQL expert). Give it a try. If you still get the error, please let me know. I will further research.

[Daniel Schneider]

Hi Martin,
the error is: could not find database 'baandb' in sysdatabases.

Best regards Daniel :-)

[Martin Schmidt_2]

When you attach or when you run dbcc?

[Daniel Schneider]

When running sp_detach_db 'baandb' there was error 947: Error at properly closing database 'baandb'
When running sp_attach @dbname = N'baandb'
@filename1 = N'g:\baandata\baandb.mdf'
@filename2 = N'c:\mssql7\data\baandb.ldf'

Error 3624 in page.cpp:3008 spaceneeded <= spacecontig && spaceneeded <= space_usable
connection broken.

Thats what I wrote in my first Message too.

Best Regards
Daniel :-)

[Martin Schmidt_2]

Hi Daniel,

It's hard stuff. We have access to MS internal databases, but most of the people restored from Backup at this point of time. :-(

Have you tried to attach just the MDF file with sp_attach_single_file_db ?

Regards, Martin.

[Daniel Schneider]

Hi Martin,
its the same error as sp_attach_db.

Best Regards
Daniel :-)

[Martin Schmidt_2]

What is your phone number?

[Daniel Schneider]

Hi Martin,
thank you for helping me.
I was out of office yesterday, so I try to solve my problem today again.
My mainproblem is again, that the database 'baandb' isn't attached to the server. Your helpinformation is very interesting, but I cannot attach the database.
Whould you contakt me? My EMail is daniel.schneider@cmg.de.

Best regards
Daniel :-)

[Daniel Schneider]

Hi all,
the problem is now solved.

Thanks
Daniel :-)

[Daniel Schneider_3]

Hi all,

because of many questions in the past, I add the missing Information to this Thread.
It is the Information, how to attach the Database, when the standard attaching not runs:

To attach the Database, I stopped SQL-Server, copied the Databasefile (e.g: baandb.mdf & baandb.ldf), then I started SQL-Server again, and deleted the baandb-Database. Next I recreated it as an empty Database, with exact the same name and parameters as the old one. After that I stopped SQL-Server again, deleted the new baandb.mdf & .ldf in Windows Explorer and renamed the saved one to the new one. After that started SQL-Server again, and the Database is attached and not marked with suspect. Now you can run DBCC-Tools.

Best Regards
Daniel :-)