Suspect DB & sp_configure 'allow updates to system tables' - NOT SUPPORTED?
Patrick
I'm using Sybase 12.5 on AIX 5.2.
I red that the following command follwed by updates to sysdatabases was not
supported by Sybase:
sp_configure 'allow updates to system tables'
go
And that we shouldn't call Sybase Technical Support if we experience
problems after running this sp_configure and updates to sysdatabases!
I'm VERY CONCERNED because I have the following procedure (when I start my
Sybase ASE 12.5 Server) to turn off the SUSPECT flag of my production
database:
------------------------------------------
1. Procedure (method) from the book: UNLEASHED Sybase SQLServer 11 (Sams
Publishing)
use master
go
sp_configure "allow updates", 1
reconfigure with override
go
update sysdatabases
set status = status - 256
where dbname = 'my_db_name'
go
sp_configure "allow updates", 0
reconfigure
go
And then shutdown and restart the server.
2. My actual procedure (I'm running 12.5):
use master
go
sp_configure "allow updates to system tables", 1
go
update sysdatabases
set status = new_status
where dbname = 'my_db_name'
go
sp_configure "allow updates to system tables", 0
go
And then shutdown and restart the server.
------------------------------------------
I'm using this procedure when I start my server and one my database is
marked as SUSPECT. I'm looking at the errorlog: the server startup process,
if it failed to start one of the device where the database is loaded.
Purpose: When I start my Sybase server it may happens that the server
detects a failure of a device and mark all or one of my database as SUSPECT.
I'm using for new_value: --> set new_value = status - 256 IS IT CORRECT?
BUT: is my procedure correct? I really want to know if it safe to execute
such a procedure when one or all of my database(s) is/are marked SUSPECT??
Do still I have support of Sybase if my database are still marked as
SUSPECT?
Is there another method?
Should I use instead: sp_listsuspect_db, sp_listsuspect_object and
sp_listsuspect_page to check if I have a suspect database and why? If yes,
should I use dbcc tablealloc and dbcc indexalloc to fix the problem? Do I
then need to install the dbccdb database?
In advance, thank you so much!
Best regards,
Patrick.
Bret Halford
general, you just shouldn't be making direct updates except under the direct
instruction of Tech Support, or using well-documented procedures.
There are very few cases where the best way to handle something is with
a direct update to system tables.
Your procedure is correct for a database that is truly marked suspect, but
could cause problems if run on a databasee that wasn't marked suspect.
A better update would be
update sysdatabases set status = status ^ 256 where status & 256 = 256 and name
= '<name of db>"
A very similar procedure is documented in the official ASE Troubleshooting
Guide, see
http://sybooks.sybase.com/onlinebooks/group-as/asg1251e/svrtsg/@Generic__BookView?DwebQuery=suspect+within+50+words+of+256&DwebSearchAll=1
I'm not sure where you read this blanket statement, I think it goes a bit far.
You
do have to be very careful when making updates to the system tables, the
way the server works often isn't the way you might intuitively think. (For
instance,
you can't change a column from allowing nulls to not allowing nulls by just
changing the bit in syscolumns, as the row layout of data on disk actually
differs
for null or not-null columns, and just changing the system table doesn't change
the data on disk - ASE would then start trying to interpret the data on disk
with
the wrong format, which could yield errors or corrupt output.)
-bret
Patrick
I'm using this procedure for a fail-over database environement! It's not a
production environment, but if a catastrophy scenario happens so I have to
switch and start my fail-over server and databases... In this case (crash,
catastrophy scenario...) it will be a production environment...
What do you mean by "truly" SUSPECT? What can causes a database to be set or
flagged to SUSPECT? I'm using sp_helpdb to check the flagg status of a
database.
Will a database be marked SUSPECT if I have too much (over the threshold...)
corrupt pages? Or will the database be marked/flagged CORRUPT instead of
SUSPECT?
Should I then, use instead: sp_listsuspect_db, sp_listsuspect_object and
sp_listsuspect_page to check if I have a suspect database and why? If yes,
should I use dbcc tablealloc and dbcc indexalloc to fix the problem? Do I
then need to install the dbccdb database?
Do you think it's a good idea to establish for the fail-over environment a
DBCC checks procedure?
Again, many thank for your precious comments and help!
Best regards,
Patrick.
Should I use instead: sp_listsuspect_db, sp_listsuspect_object and
> sp_listsuspect_page to check if I have a suspect database and why? If yes,
> should I use dbcc tablealloc and dbcc indexalloc to fix the problem? Do I
> then need to install the dbccdb database?
>
"Bret Halford" <br...@sybase.com> wrote in message
news:420CF3BC...@sybase.com...
Bret Halford
"...where status & 256 = 256" checks that
There is no "corrupt" flag.
Databases are marked suspect if problems are found during the recovery
process. A common cause is the database using a disk drive that
has
not spun up or is otherwise unavailable at the time recovery is run.
This
can be easily recovered by correcting the problem with the drive, marking
the database unsuspect, and rebooting. A database can also be
marked
suspect if corruption is found during recovery. The types of
corruption that
would cause that are almost always not fixable by dbccs - loading from
a backup
is usually the best course of action.
You only need to install the dbccdb database if, in general, you plan
to use
dbcc checkstorage as a faster alternative to dbcc checkalloc.
Checkstorage
won't help much once you already have a suspect database, you would
use
it regularly to detect problems before things got that far.
Cheers,
-bret
Patrick
"Bret Halford" <br...@sybase.com> wrote in message news:420E3784...@sybase.com...
Bret Halford
They range from
"the capability was never programmed into the product, so it just can't
be done"
ex. "UNION in VIEW was not supported prior to ASE version 12.5"
to
ASE 11.9.x is no longer supported on Solaris 2.8
(11.9 has been declared end-of-life by engineering, so no more
bugs will be fixed on it. Technical support will still support
it to some
degree by answering questions about it and attempting to find
workarounds to problems, but if a problem is found that can't be worked
around, there isn't much that can be done outside of upgrading.
Although it
isn't supported, 11.9.x generally still functions just fine on Solaris
2.8).
In this case, the way I read it is something like "this sp_resetstatus
stored procedure
was originally written by a junior tech support engineer and floated
around tech support for a year or two before an editor in the
technical
publications department decided to add it in to the troubleshooting
guide
because it was so useful. It seems to work just fine, but it
didn't come
from the codeline engineers, so, to be safe, it was tagged with the
"is not supported" phrase. It should eventually be added to the
installmaster
script and made official - but the change request to do so (CR 69352)
is so low priority
that it hasn't been acted on since it was opened 10 years ago.
So I wouldn't worry about the procedure too much - if it doesn't work
for you some
day, you can still call tech support for help.
Cheers,
-bret
Patrick wrote:
Thank you so much! I followed the method from the link you send to me! It's great!But they still say that the sp_resetstatus stored procedure is not supported at this time?!? It's quite strange? It is well written on the sybase website (http://sybooks.sybase.com) ... Best regards, Patrick."Bret Halford" <br...@sybase.com> wrote in message news:420E3784...@sybase.com...Truly suspect: the suspect bit in the status field is really on.
Patrick
"Bret Halford" <br...@sybase.com> wrote in message news:4213B60B...@sybase.com...