suspect database. How to correct the problem
Joanne Pham
Hi all,
One for awhile I have the message next to the database is
suspect. For example in the server manager window next
to the database(suspect).
How to correct this problem.
Please send me email if you have the same problem
joann...@compuware.com
Thanks in advance,
Joanne
JurewiczJW
SQL Books Online has a number of articles on this subject. If that's not
available to you, I've found Microsoft's site very useful in problem solving:
http://support.microsoft.com/support/c.asp
Here's one possibility, but realize there can be many causes to your problem:
PRB: Missing Device Causes Database to Be Marked Suspect
Last reviewed: February 23, 1998
Article ID: Q180500
The information in this article applies to:
Microsoft SQL Server, versions 6.0 and 6.5
SYMPTOMS
SQL Server marks a database suspect if any of the device files for the database
are unavailable when it attempts to start. You may see either of the following
sets of messages in the SQL Server error log:
96/11/18 10:48:32.60 kernel udopen: Operating System Error 32 (The
process cannot access the file because it is being used by another
process.) during the creation/opening of physical device,
C:\DATA\SQL\MSDB.DAT
96/11/18 10:48:32.60 kernel udactivate (primary): failed to open
device C:\MSSQL\DATA\MSDB.DAT for vdn 127
-or-
96/11/18 10:48:32.60 kernel udopen: operating system error 2(The
system cannot find the file specified.) during the creation/opening
of physical device C:\MSSQL\DATA\MSDB.DAT
96/11/18 10:48:32.60 kernel udactivate (primary): failed to open
device C:\MSSQL\DATA\MSDB.DAT for vdn 127
These will be followed later in the log by:
96/11/18 10:48:36.70 kernel udread: Operating system error 6(The
handle is invalid.) on device 'C:\MSSQL\DATA\MSDB.DAT' (virtpage
0x7f000018).
96/11/18 10:48:36.77 spid11 Error : 840, Severity: 17, State: 2
96/11/18 10:48:36.77 spid11 Device 'MSDBData' (with physical name
'C:\MSSQL\DATA\MSDB.DAT', and virtual device number 127) is not
available. Please contact System Administrator for assistance.
96/11/18 10:48:36.77 spid11 Buffer 1092480 from database 'msdb'
has page number 0 in the page header and page number 24 in the
buffer header
96/11/18 10:48:37.43 spid11 Unable to proceed with the recovery of
dbid <5> because of previous errors. Continuing with the next
database.
For example, performing the following steps will demonstrate the problem:
Stop SQL Server.
Issue the following command from a command prompt at the Mssql\Data directory:
ren msdb.dat msdb.sav
Start SQL Server.
You will see the above errors (the ones from the second set) in the SQL Server
errorlog. If you then issue the following query in the master database
select name, dbid, mode, status from sysdatabases where dbid =
select db_id('msdb')
You receive the following results:
name dbid mode status
------------------------------
msdb 5 0 328
The status of 328 evaluates to:
truncate log on chkpt
database not recovered yet
database is suspect
For more information, see the "Sysdatabases (Master Database Only)" topic in
the SQL Server Books Online.
CAUSE
At startup, SQL Server attempts to obtain an exclusive lock on the device file.
If the device is being used by another process (for example, backup software)
or if the file is missing, the scenario described above will be encountered. In
these cases, there is usually nothing wrong with the devices and database. For
the database to recover correctly, the device must be made available, and the
database status must be reset.
WORKAROUND
To work around this problem, perform the steps below. Note that the final step
is critical.
Ensure that the device file is actually available.
Use the supplemental stored procedure sp_resetstatus to reset the status of a
suspect database. If you have not already done so, create this procedure by
executing the Instsupl.sql script, found in the Mssql\Install directory. For
more information on sp_resetstatus, see the "Resetting the Suspect Status"
topic in the SQL Server Books Online.
Execute sp_resetstatus in the master database for the suspect database:
use master
go
exec sp_resetstatus msdb -- replace msdb with your database name
You will see the following output:
Prior to Update sysdatabases attempt for DBName='msdb', the mode=0
and status=328 (status suspect_bit=256). For DBName='msdb' in
sysdatabases, status bit 256 was forced Off and mode was forced to
0. WARNING: You MUST stop/restart SQL Server prior to accessing this
database!
Stop and restart SQL Server.
Verify that the database was recovered and is available.
Run DBCC NEWALLOC, DBCC TEXTALL, and DBCC CHECKDB.
Adwait Ullal
Try the DBCC command (see BOL Books Online for further details).
- Adwait
Joanne Pham wrote in message <6ilh3l$ke...@cns.compuware.com>...
Ori Geva
Hi Joanne,
Look in the books on-line for using the words reset and suspect, you
will find an article explaining it. I will enclose it here for your
convenience:
The sp_resetstatus procedure shown below 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 this manual. 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
The sp_resetstatus procedure can be executed only by the system
administrator. Always shut down SQL Server immediately after executing
this procedure.
Syntax
sp_resetstatus database_name
Example
sp_resetstatus PRODUCTION
Database 'PRODUCTION' status reset!
WARNING: You must reboot SQL Server prior to
accessing this database!
Stored Procedure Code
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
Good luck
Ori Geva
Information Objects