Hi guys,
On restarting our Baan server after it hung while creating sequential dump of the live company, the baan database was marked suspect. The drive where the mdf file is on has an excess of 19GB free while the drive where the backup file(7Gb) is being created has almost 85gb free and so it wasnt due to disk space problem..I rememeber once seeing a Suspect Database case on Baanboard but i couldnt find it anymore.

Does any one have any sort of procedures or ways to get the database back to normal or how to troubleshoot a suspect database?

I would very much appreciate any help on this as this is our Live production server...

Thanking you in advance...
sk

Since there were no replies, could i assume that once your Database gets into a suspect mode, there is no alternative than to restore the sql backup or else start from scratch(reinstall OS/DB/ERP all over again) and restore it from baan sequential dump?

sk

Did you try with google "sql suspect database"?

Yup tried that.
Some of the suggestion didnt match the error profile and as it is very well known, the combination of baan and sql throws off some exceptional errors which are hard to get over...
Didnt have too much time to investigate further as it was the begining of a week day and there was too much to do to bring it back on another server while the problematic server was prepared from ground level on...

But for future encounter with this error, i would like to be prepared. I know the first and foremost thing is to have a SQL db backup but other than that is there a way to tackle this situation(suspect db)?

sk

i experienced this problem twice. First time, after trying with disconnect
and reconnect database from SQL, it worked. But next time, it was very
difficult to recover. All i could do is to remove the database through SQL
Enterprise manager and restore the SQL backup.

Resetting the Suspect Status
Microsoft® SQL Server™ 2000 returns error 1105 and sets the status column of sysdatabases to suspect if SQL Server is unable to complete recovery on a database because the disk drive no longer has any free space. Follow these steps to resolve the problem:

Execute sp_resetstatus.


Use ALTER DATABASE to add a data file or log file to the database.


Stop and restart SQL Server.
With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.

Free disk space and rerun recovery.
sp_resetstatus turns off the suspect flag on a database, but leaves all other database options intact.



Caution Use sp_resetstatus only when directed by your primary support provider or as recommended in Troubleshooting. Otherwise, you might damage your database.


Because this procedure modifies the system tables, the system administrator must enable updates to the system tables before creating this procedure. To enable updates, use this procedure:

USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO

After the procedure is created, immediately disable updates to the system tables:

sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO

sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.

The syntax is:

sp_resetstatus database_name

This example turns off the suspect flag on the PRODUCTION database.

sp_resetstatus PRODUCTION

Here is the result set:

Database 'PRODUCTION' status reset!
WARNING: You must reboot SQL Server prior to accessing this database!

sp_resetstatus Stored Procedure Code
Here is the code of the sp_resetstatus stored procedure:

IF EXISTS ( SELECT * from sysobjects where name = 'sp_resetstatus' )
DROP PROCEDURE sp_resetstatus
GO

CREATE PROC sp_resetstatus @dbname varchar(30) AS
DECLARE @msg varchar(80)
IF @@trancount > 0
BEGIN
PRINT 'Can''t run sp_resetstatus from within a transaction.'
RETURN (1)
END
IF suser_id() != 1
BEGIN
SELECT @msg = 'You must be the System Administrator (SA)'
SELECT @msg = @msg + ' to execute this procedure.'
RETURN (1)
END
IF (SELECT COUNT(*) FROM master..sysdatabases
WHERE name = @dbname) != 1
BEGIN
SELECT @msg = 'Database ' + @dbname + ' does not exist!'
PRINT @msg
RETURN (1)
END
IF (SELECT COUNT(*) FROM master..sysdatabases
WHERE name = @dbname AND status & 256 = 256) != 1
BEGIN
PRINT 'sp_resetstatus can only be run on suspect databases.'
RETURN (1)
END
BEGIN TRAN
UPDATE master..sysdatabases SET status = status ^ 256
WHERE name = @dbname
IF @@error != 0 OR @@rowcount != 1
ROLLBACK TRAN
ELSE
BEGIN
COMMIT TRAN
SELECT @msg = 'Database ' + @dbname + ' status reset!'
PRINT @msg
PRINT ''
PRINT 'WARNING: You must reboot SQL Server prior to '
PRINT ' accessing this database!'
PRINT ''
END
GO

Hi dorleta,
Is that all for a database which ran out of physical filespace? In my case, that wasnt the issue. There were like 20gigs left on the drive. So freespace wasnt the issue. It was something else. Talking about which i experienced another db crash just two days back. This time however it was a raid controller which put two hard disks to sleep and which caused logical drive error. When the array was enabled back, three production dbs(including baandb) as well as msdb got into suspect mode.

Month of May hasnt been good to me...

sk