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.

Problem with Archive database

4 posts in General Discussion Last posting was on 2010-06-22 18:43:49.0Z
John Flynn Posted on 2010-06-21 14:37:39.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
Subject: Problem with Archive database
Lines: 38
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c1f7933$1@forums-1-dub>
Date: 21 Jun 2010 07:37:39 -0700
X-Trace: forums-1-dub 1277131059 10.22.241.152 (21 Jun 2010 07:37:39 -0700)
X-Original-Trace: 21 Jun 2010 07:37:39 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29321
Article PK: 78553

Hi.

I am on ASE 15.0.3 on Solaris. I recently started experimenting with an
Archive Database. I pointed it to a recent database dump file, and
everything was working fine.

But then later, by accident I allowed my normal scheduled database dump job
to kick in. It tried to write a new dump to that same filename on the disk.
The dump immediately failed, saying "Volume on device cannot be opened for
write access. Mount another volume." So I gave it the sp_volchanged command
to abort. So far this seems reasonable. But then things went bad because the
end result was that the file on disk went to zero bytes! That then hosed up
my Archive database, and now I can't drop it. When I try the "drop database"
command, it reasonably gives I/O errors. But also I can't use dbcc
dbrepair(dropdb) because the database isn't marked suspect. I have not yet
tried any other methods to force-drop the database, because they all seem to
require bouncing the ASE, and I can't do that right now (unless it's an
emergency, which this isn't).

So, I have a few questions:

1. The dump process is apparently capable of recognizing that the file can't
be opened for write access, so why did it zero out the file? If it had just
left the file alone, I think I could have gotten myself out of this
situation.

2. Will the usual method for dropping a database work on an Archive
database? I.e. mark the database suspect, bounce ASE, then dbcc
dbrepair(dropdb). Would it be safe for me to mark the database suspect now,
even if I don't intend to bounce the ASE for a week or more?

3. Is there any method for repairing this that doesn't require bouncing the
ASE?

Thanks.
- John.


"Mark A. Parsons" <iron_horse Posted on 2010-06-21 17:36:16.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Problem with Archive database
References: <4c1f7933$1@forums-1-dub>
In-Reply-To: <4c1f7933$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100606-0, 06/06/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c1fa310@forums-1-dub>
Date: 21 Jun 2010 10:36:16 -0700
X-Trace: forums-1-dub 1277141776 10.22.241.152 (21 Jun 2010 10:36:16 -0700)
X-Original-Trace: 21 Jun 2010 10:36:16 -0700, vip152.sybase.com
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29323
Article PK: 78554


> 1. The dump process is apparently capable of recognizing that the file can't
> be opened for write access, so why did it zero out the file?

*shrug* You'll have to either a) open a case with Sybase TechSupport or b) wait for someone with backupserver internals
knowledge to come along and see your post.

> 2. Will the usual method for dropping a database work on an Archive
> database? I.e. mark the database suspect, bounce ASE, then dbcc
> dbrepair(dropdb). Would it be safe for me to mark the database suspect now,
> even if I don't intend to bounce the ASE for a week or more?

I've never tried that with an archive database so I'd say ... when it doubt try it out (on a development server).

Alternatively, I know that deleting the offending records from sysdatabases, sysusages and sysattributes (followed by a
dataserver bounce) always works for me.

> 3. Is there any method for repairing this that doesn't require bouncing the ASE?

Create a new archive database and point your processing at said archive database.

Alternatively, see if sp_renamedb will work on your current archive database ... objective being to set the current
archive database aside (ie, rename it), then create a new archive database (with the old name), and have your processing
continue to use the newly created but samely-named archive database.


Carl Kayser Posted on 2010-06-21 16:15:14.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4c1f7933$1@forums-1-dub>
Subject: Re: Problem with Archive database
Lines: 48
Organization: BLS
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c1f9012$1@forums-1-dub>
Date: 21 Jun 2010 09:15:14 -0700
X-Trace: forums-1-dub 1277136914 10.22.241.152 (21 Jun 2010 09:15:14 -0700)
X-Original-Trace: 21 Jun 2010 09:15:14 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29322
Article PK: 78555


"John Flynn" <jflynn@miqs.com> wrote in message
news:4c1f7933$1@forums-1-dub...
> Hi.
>
> I am on ASE 15.0.3 on Solaris. I recently started experimenting with an
> Archive Database. I pointed it to a recent database dump file, and
> everything was working fine.
>
> But then later, by accident I allowed my normal scheduled database dump
> job to kick in. It tried to write a new dump to that same filename on the
> disk. The dump immediately failed, saying "Volume on device cannot be
> opened for write access. Mount another volume." So I gave it the
> sp_volchanged command to abort. So far this seems reasonable. But then
> things went bad because the end result was that the file on disk went to
> zero bytes! That then hosed up my Archive database, and now I can't drop
> it. When I try the "drop database" command, it reasonably gives I/O
> errors. But also I can't use dbcc dbrepair(dropdb) because the database
> isn't marked suspect. I have not yet tried any other methods to force-drop
> the database, because they all seem to require bouncing the ASE, and I
> can't do that right now (unless it's an emergency, which this isn't).
>
> So, I have a few questions:
>
> 1. The dump process is apparently capable of recognizing that the file
> can't be opened for write access, so why did it zero out the file? If it
> had just left the file alone, I think I could have gotten myself out of
> this situation.
>
> 2. Will the usual method for dropping a database work on an Archive
> database? I.e. mark the database suspect, bounce ASE, then dbcc
> dbrepair(dropdb). Would it be safe for me to mark the database suspect
> now, even if I don't intend to bounce the ASE for a week or more?
>
> 3. Is there any method for repairing this that doesn't require bouncing
> the ASE?
>
> Thanks.
> - John.
>
>

Have you tried dumping a trivial database to the same file and then doing a
"drop database"? This worked for me when I had a "good archive database"
that happened to have external foreign keys to a non-existent database
(because the dbids were for a different server).


John Flynn Posted on 2010-06-22 18:43:49.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
References: <4c1f7933$1@forums-1-dub> <4c1f9012$1@forums-1-dub>
Subject: Re: Problem with Archive database
Lines: 28
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4c210465$1@forums-1-dub>
Date: 22 Jun 2010 11:43:49 -0700
X-Trace: forums-1-dub 1277232229 10.22.241.152 (22 Jun 2010 11:43:49 -0700)
X-Original-Trace: 22 Jun 2010 11:43:49 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29325
Article PK: 78557


Carl Kayser wrote:
> Have you tried dumping a trivial database to the same file and then
> doing a "drop database"? This worked for me when I had a "good
> archive database" that happened to have external foreign keys to a
> non-existent database (because the dbids were for a different server).

I tried your idea, but Backup Server refused to overwrite the existing file.

So, I then found a dump of a small database, and from Unix I copied it over
the original file. But then when I went back into ASE and tried to drop the
archive database, I still got I/O errors.

Then, I thought maybe it would work if I repeated that, but from a larger
database dump, i.e. one larger than the archive database I'm trying to drop.
So I found a larger database dump laying around, and from Unix copied it
over my original file. Now, I've gone back into ASE and tried to drop the
archive database. It accepted the command, but now it has been hung for
about an hour. ASE is running fine, but the drop database command is hung. I
didn't expect the drop database to be instantaneous, but from past
experience I thought it might be a few minutes, not an hour.

I'm not sure what to do now. This is a production system and I'm afraid to
touch anything else.

Thanks.
- John.