SQL 6.5 database recovery problem -db is suspect
306448
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
news:DD8C7CE3FFD94B4283A804C7D7B61FB63F28EF@CPMSFTMSGV22...
See the FAQ article below, if anything else fails you me
able to BCP out some data setting the db to emergency status.
HTH
--
Gianluca Hotz
Backoffice MVP (SQL Server)
MCP SQL Server and MCP Windows
http://users.alphasys.it/ghotz
--
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
inconsistent.
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>'
johnchri...@gmail.com
http://www.repairmdf.sqlserverrepairtoolbox.com
