Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Suspect Database Error

5 posts in Windows NT Last posting was on 1998-02-10 17:41:23.0Z
Karim Ahmed Posted on 1997-10-20 14:37:29.0Z
From: "Karim Ahmed" <kahmed@datastream.com>
Subject: Suspect Database Error
Date: Mon, 20 Oct 1997 15:37:29 +0100
Lines: 14
X-Newsreader: Microsoft Outlook Express 4.71.1008.3
X-MimeOle: Produced By Microsoft MimeOLE Engine V4.71.1008.3
Message-ID: <mZHgG0V38GA.151@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5474
Article PK: 1081552

I have the following error when trying to "use" my database from isql under
NT 4:

Database 'PreView' cannot be opened. An earlier attempt at recovery marked
it
'suspect'. Check the SQL Server errorlog for information as to the cause.

I have checked the log but don't understand what the problem is. Is there a
diagnostic tool or similar?

Karim.


Mark A. Parsons Posted on 1997-10-23 22:56:25.0Z
Message-ID: <344FD619.6738@compuserve.com>
Date: Thu, 23 Oct 1997 18:56:25 -0400
From: "Mark A. Parsons" <Iron_Horse@compuserve.com>
Reply-To: Iron_Horse@compuserve.com
Organization: Iron Horse, Inc.
X-Mailer: Mozilla 3.01 (Win95; I)
MIME-Version: 1.0
Subject: Re: Suspect Database Error
References: <mZHgG0V38GA.151@forums.powersoft.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 35
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5461
Article PK: 1081539

Have your DBA investigate.

At some time in the past ... during database recovery (during startup)
... the dataserver was unable to bring your database online for some
reason. Their are several different reasons ... a few of which include:

1) A 'load database' command did not complete successfully.
2) A device the database relies on did not come online at startup time.
3) The database could have corruption (could be caused by a slew of
problems).
etc., etc., etc., etc.

It may be a simple case of resetting the database's status bit ... or
you may need to reload the database from a backup ... or you may need to
rebuild the database from scratch ... or ....

Hard to tell without more info. Have your DBA(s) take a look at the
problem. If they can't solve the problem come back here and we (the
newsgroup) will see what we can do. Alternatively you could call Sybase
TS or bring in an outside source to help.

Mark Parsons
Iron Horse, Inc.

Karim Ahmed wrote:
>
> I have the following error when trying to "use" my database from isql under
> NT 4:
>
> Database 'PreView' cannot be opened. An earlier attempt at recovery marked
> it
> 'suspect'. Check the SQL Server errorlog for information as to the cause.
>
> I have checked the log but don't understand what the problem is. Is there a
> diagnostic tool or similar?
>
> Karim.


Gregory Junker Posted on 1998-02-09 20:04:50.0Z
From: "Gregory Junker" <gjunker@spambuster.one.net>
Subject: Re: Suspect Database Error
References: <mZHgG0V38GA.151@forums.powersoft.com> <344FD619.6738@compuserve.com>
Organization: Shockwave Audio
Message-ID: <01bd3596$80dd5f80$8d9b01c0@dsp>
X-Newsreader: Microsoft Internet News 4.70.1162
Newsgroups: sybase.public.sqlserver.nt
Date: Mon, 09 Feb 1998 15:04:50 -0500
Lines: 48
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5219
Article PK: 1081298

I had the same problem, and it stemmed from the device not being available
one time (the drive refused to spin up). I can get to the device file, but
how do I turn off the "suspect" bit so that I can get the data out of the
database?

Problem #1: No backups. This is not a server I administer, just one that I
was called in to try to recover.

Problem #2: DBCC had droppe dthe database from sysdatabases. How can I
recreate the entry in sysdatabases so that the server knows that the DB
exists?

Any help is appreciated...

Thanks
Greg

p.s. the next option is reloading the database one entry at a time, which
is a 40-hour job.....if that's all we can do, so be it, but there has to be
a way to get data out of a valid database device.....I think...

Mark A. Parsons <Iron_Horse@compuserve.com> wrote in article
<344FD619.6738@compuserve.com>...
> Have your DBA investigate.
>
> At some time in the past ... during database recovery (during startup)
> ... the dataserver was unable to bring your database online for some
> reason. Their are several different reasons ... a few of which include:
>
> 1) A 'load database' command did not complete successfully.
> 2) A device the database relies on did not come online at startup time.
> 3) The database could have corruption (could be caused by a slew of
> problems).
> etc., etc., etc., etc.
>
> It may be a simple case of resetting the database's status bit ... or
> you may need to reload the database from a backup ... or you may need to
> rebuild the database from scratch ... or ....
>
> Hard to tell without more info. Have your DBA(s) take a look at the
> problem. If they can't solve the problem come back here and we (the
> newsgroup) will see what we can do. Alternatively you could call Sybase
> TS or bring in an outside source to help.
>
> Mark Parsons
> Iron Horse, Inc.


Bret Halford Posted on 1998-02-10 17:41:23.0Z
Message-ID: <34E09143.6455@sybase.com>
Date: Tue, 10 Feb 1998 10:41:23 -0700
From: Bret Halford <bret@sybase.com>
Organization: Customer Service & Support
X-Mailer: Mozilla 3.0 (X11; U; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
To: Gregory Junker <gjunker@spambuster.one.net>
Subject: Re: Suspect Database Error
References: <mZHgG0V38GA.151@forums.powersoft.com> <344FD619.6738@compuserve.com> <01bd3596$80dd5f80$8d9b01c0@dsp>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 75
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5213
Article PK: 1081292

Actually, you would need to restore entires in both
sysdatabases and sysusages.


If you knew what they looked like, you could just do manual
updates and then reboot SQL Server.

A better solution would be to have a dump of master that you
could load.

If you don't have either of these, you can try the following:

take a dump of master so you can return to this state.
try the "disk refit" command. I'm not sure how much dbcc
dbrepair(<dbname>,dropdb) erases. If it just cleaned out
memory structures and deleted the rows from sysdatabases and
sysusages, disk refit should be able to rebuild the database
from the information on disk. You might get some additional
garbage rows in sysdatabases or sysusages that you will have to
delete by hand afterwards.

Good Luck,

-bret

Gregory Junker wrote:
>
> I had the same problem, and it stemmed from the device not being available
> one time (the drive refused to spin up). I can get to the device file, but
> how do I turn off the "suspect" bit so that I can get the data out of the
> database?
>
> Problem #1: No backups. This is not a server I administer, just one that I
> was called in to try to recover.
>
> Problem #2: DBCC had droppe dthe database from sysdatabases. How can I
> recreate the entry in sysdatabases so that the server knows that the DB
> exists?
>
> Any help is appreciated...
>
> Thanks
> Greg
>
> p.s. the next option is reloading the database one entry at a time, which
> is a 40-hour job.....if that's all we can do, so be it, but there has to be
> a way to get data out of a valid database device.....I think...
>
> Mark A. Parsons <Iron_Horse@compuserve.com> wrote in article
> <344FD619.6738@compuserve.com>...
> > Have your DBA investigate.
> >
> > At some time in the past ... during database recovery (during startup)
> > ... the dataserver was unable to bring your database online for some
> > reason. Their are several different reasons ... a few of which include:
> >
> > 1) A 'load database' command did not complete successfully.
> > 2) A device the database relies on did not come online at startup time.
> > 3) The database could have corruption (could be caused by a slew of
> > problems).
> > etc., etc., etc., etc.
> >
> > It may be a simple case of resetting the database's status bit ... or
> > you may need to reload the database from a backup ... or you may need to
> > rebuild the database from scratch ... or ....
> >
> > Hard to tell without more info. Have your DBA(s) take a look at the
> > problem. If they can't solve the problem come back here and we (the
> > newsgroup) will see what we can do. Alternatively you could call Sybase
> > TS or bring in an outside source to help.
> >
> > Mark Parsons
> > Iron Horse, Inc.

--
Bret Halford Imagine my disappointment
Sybase Technical Support in learning the true nature
3665 Discovery Drive of rec.humor.oracle...
Boulder, CO 80303


John McVicker Posted on 1998-02-10 02:33:03.0Z
Message-ID: <34DFBC5F.56E64C17@sybase.com>
Date: Mon, 09 Feb 1998 21:33:03 -0500
From: John McVicker <mcvicker@sybase.com>
Reply-To: mcvicker@sybase.com
Organization: Sybase Professional Services
X-Mailer: Mozilla 4.04 [en] (Win95; U)
MIME-Version: 1.0
To: Gregory Junker <gjunker@spambuster.one.net>
Subject: Re: Suspect Database Error
References: <mZHgG0V38GA.151@forums.powersoft.com> <344FD619.6738@compuserve.com> <01bd3596$80dd5f80$8d9b01c0@dsp>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 71
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5218
Article PK: 1081297

Gregory,
You can un-set the status bit in the master..sysdatabases table. Various
documents exist on this topic in Answerbase or in the Techinfo library on
http://techinfo.sybase.com. You really just do this:

/* do this as "sa" */
sp_configure "allow updates", 1
go
begin tran
update master..sysdatabases
set status = status - 256
where name = "your_dbname"
if @@rowcount = 1
commit
else
rollback
go
exec sp_helpdb "your_dbname"
go
<<should be reset now>>
sp_configure "allow updates", 0
go

Gregory Junker wrote:

> I had the same problem, and it stemmed from the device not being available
> one time (the drive refused to spin up). I can get to the device file, but
> how do I turn off the "suspect" bit so that I can get the data out of the
> database?
>
> Problem #1: No backups. This is not a server I administer, just one that I
> was called in to try to recover.
>
> Problem #2: DBCC had droppe dthe database from sysdatabases. How can I
> recreate the entry in sysdatabases so that the server knows that the DB
> exists?
>
> Any help is appreciated...
>
> Thanks
> Greg
>
> p.s. the next option is reloading the database one entry at a time, which
> is a 40-hour job.....if that's all we can do, so be it, but there has to be
> a way to get data out of a valid database device.....I think...
>
> Mark A. Parsons <Iron_Horse@compuserve.com> wrote in article
> <344FD619.6738@compuserve.com>...
> > Have your DBA investigate.
> >
> > At some time in the past ... during database recovery (during startup)
> > ... the dataserver was unable to bring your database online for some
> > reason. Their are several different reasons ... a few of which include:
> >
> > 1) A 'load database' command did not complete successfully.
> > 2) A device the database relies on did not come online at startup time.
> > 3) The database could have corruption (could be caused by a slew of
> > problems).
> > etc., etc., etc., etc.
> >
> > It may be a simple case of resetting the database's status bit ... or
> > you may need to reload the database from a backup ... or you may need to
> > rebuild the database from scratch ... or ....
> >
> > Hard to tell without more info. Have your DBA(s) take a look at the
> > problem. If they can't solve the problem come back here and we (the
> > newsgroup) will see what we can do. Alternatively you could call Sybase
> > TS or bring in an outside source to help.
> >
> > Mark Parsons
> > Iron Horse, Inc.

--
John McVicker
Principal Consultant, District Lead Architect
Sybase Professional Services
Pennsylvania/New Jersey District
301-896-1765
mcvicker@sybase.com, mcvicker@bellatlantic.net, JohnMcVicker@compuserve.com

http://www.sybase.com
http://www.powersoft.com
http://www.isug.com