SQL 6.5 database recovery problem -db is suspect

Skip to first unread message


Jan 28, 2000, 3:00:00 AM1/28/00
how do I recover a suspect db.?
Error message in errorlog "kernel udopen: file c:\mssql\data\mydb.dat is
incorrect size (128000 bytes, should be 51200)
kernel udactivate(primary) failed to open device for vdn 5.
(same msg. for T. log - vdn 6 25600 bytes, should be 10240)
I re-installed SQL 6.5, restored backup of master.dat... I do not have a
backup of the suspect database - I do have a NT backup of the mydb.dat
file and T.L. The file sizes are 128000bytes and 25600.
I also ran sp_resetstatus - db is still suspect. Any advice??

Gianluca Hotz

Feb 2, 2000, 3:00:00 AM2/2/00
"306448" <ajay...@aol.com> wrote in message

See the FAQ article below, if anything else fails you me
able to BCP out some data setting the db to emergency status.

Gianluca Hotz
Backoffice MVP (SQL Server)
MCP SQL Server and MCP Windows
Q. My SQL Server database has been marked "suspect" - what can I do?
(v1.4 12.1.1999)

A. In addition to these ideas, also check out www.microsoft.com/support for
the MS Knowledgebase. Specifically Q165918.

Firstly look in <sql>\LOG and look at all recent errorlog(s). There WILL be
an indication here as to why the database has been marked suspect. You need
to fix whatever the problem is first (i.e. missing file, permissions
problem, hardware error etc.)

Then, when the problem has been fixed and you're either sure that the data
is going to be ok, or you have no backup anyway, so you've nothing to lose,
then change the database status to normal and restart SQL Server. To change
the database status, and to get more information on recovery, look up the
sp_resetstatus sp in the Books Online.

If you don't have access to sp_resetstatus information, then the short
version of this is :-

UPDATE master..sysdatabases SET status = status ^ 256 WHERE name = <dbname>

If the database still goes back into suspect mode, and you can't fix the
original problem, and you have no recent backup, then you can get
information out of the database by putting it into emergency mode. If you
do this, extract the data/objects out with bcp/transfer manager and then
rebuild the database. Note that the data may be corrupt or transactionally

Issue the following command to put the database into emergency mode (you'll
need to allow updates first)

For SQL 6.5 and below

UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'

For SQL 7.0

UPDATE master..sysdatabases SET status=32768 WHERE name='<dbname>'


Jun 18, 2013, 3:23:54 PM6/18/13
Recover sql databases by means of repair sql 2005 database



May 19, 2014, 8:03:57 AM5/19/14
I want to share a sql data recovery tool. By using this tool you can easily recover database from suspect mode without running any T-SQL command. http://www.sqlrecoverysoftware.net/blog/repair-database-from-suspect-mode.html
Reply all
Reply to author
0 new messages