July 22, 2004 at 2:16 am
Hai,
Suddenly, when i opened the enterprise manager, i found that by the side of some of the dbs, it is written(suspect) and there are no items found within those dbs. What does it mean. and if that is a problem, could someone suggest a solution to this problem.
Thank you,
Regards,
K. Sripriya
July 23, 2004 at 1:11 am
Hi,
Have a look at Resetting the Suspect Status in BOL
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:
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', 0GORECONFIGURE WITH OVERRIDEGO
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!
G.B.
July 23, 2004 at 6:33 am
I have had this happen to me just once - and it was because the hard disk on which the database lived had become full. (In earlier days when I was ignorant of the need for scheduled tasks to clear down transaction logs etc).
It may not be the cause of your problem - but is very easy to check before delving into more complicated possible causes.
Cheers
Paul S.
July 26, 2004 at 11:38 am
Procedure to Reset Status of Databases Marked Suspect
Operating System/Software Version: SQL 6.5 and 7.0
Overview: This article presents the procedure to reset the status of databases marked suspect.
When SQL Server starts and is not able to access the database devices (transaction log files or
database files), the databases will become marked suspect because SQL Server can not obtain an
exclusive lock on those files. When going into the SQL Enterprise Manager, you will see (suspect)
to the right of the database(s) which have become marked suspect. Simply stoping/restarting
MSSQLServer service will not usually correct this condition.
There are a few different reasons why this may occur. For example, the drive which contains the
database or transaction log files may not be accessible or the files have been deleted. Also, if a
database file is in the process of being backed up when SQL Server starts, SQL Server will not be
able to obtain an exclusive lock and will mark the database suspect. In the NT Event Viewer, you
will see messages from SQL Server indicating that a database device failed to be opened. There is
also the possibility that the database has become seriously corrupt and as a result you may have to
do a database restore. The steps below are to be used when there had been a problem reading the
database files and the problem has now been corrected and you simply need to reset the status. If all
of the VNeT databases are marked Suspect, the problem is most likely a problem accessing the
database files. If only one or two of the databases are marked suspect, it may be a corruption
problem. To reset the suspect status, follow the below procedures:
1. Make sure the database device files are available.
2. You will need to create a stored procedure (the required stored procedure is not on the system
unless it had already been manually created in which case you will start at step 5).
Because this procedure modifies the system tables, you must enable updates to the system tables
before creating the stored procedure. To enable updates, copy and run the following script:
USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
3. Next, to create the sp_resetstatus stored procedure, run the following script:
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
4. Next you need to run the following script to disable updates to the system tables:
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
5. Next You need to run the stored procedure sp_resetstatus. Below is an example and resetting the
status for the Audit database:
sp_resetstatus Audit
6. After completing the above procedures, you must stop/restart MSSQLServer service.
IF this wont work the only option is delete that suspect database and create a new database and Restore the Latest and virus free Backup.
Mohammed
MCSA,MCDBA
July 29, 2004 at 8:29 am
Run recovery by executing DBCC DBRECOVER (database).
Add a log file to the suspect database and run recovery on the database by executing sp_add_log_file_recover_suspect_db.
Mohammed
MCSA,MCDBA
May 7, 2006 at 11:09 pm
I know this post is late in the game, but it is mainly "food for thought" for those struggling with suspect databases in the future. "Suspect" can also appear if you change the drive letter of a drive housing the data or transaction log files. The way I fixed mine was to set the allow updates to 1, and update the sysdatabases and sysaltfiles tables with the correct location. Then I ran DBCC DBRECOVER () for each one, restarted MSSQLSERVER service, refreshed EM, and once fixed, reset allow updates back to 0.
February 23, 2007 at 8:51 am
Hi All,
I would appreciate if you could help me on this. I had a suspect database, and I followed the procedure by resetting the status, adding a log file, and recycle the Server. The "Suspect" word was gone, database is working but it does not update any transaction log file. No date/timestamp on the transaction log file. The database backup and transaction log backups are being done regularly. I dont where the transaction logs are being written,
any help would be greatly appreciated.
Thanks
February 23, 2007 at 4:42 pm
What do you mean it doesn't update the transaction log?
-SQLBill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply