reindexing sysobjects

Dear All!

On my Sybase SQL server an index of a sysobjects table got damaged. The table needs to be reindexed, I think. I, however, cannot do that because Sybase denies such act repairing on sysobject tables.("Allow updates" is on.)

Urgent help please!

Thanks in advance

Csabay Károly (Charles)


csabayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkotekCommented:

1) run sp_indsuspect procedure to test if there are more damaged indexes

2) run dbcc reindex(tablename) to repair the damaged indexes
0
csabayAuthor Commented:
Adjusted points to 130
0
csabayAuthor Commented:
sp_indsuspend refers only on suspended indices and does not make sysobjects free. dbcc reindex does not run on sysobjects, not after sp_indsuspend either.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

jkotekCommented:
Do you get any errors running the dbcc?
Are you running dbcc over a database in single-user mode or have you only turned the 'allow updates' on?
(to put database [not master] in single user mode: 'sp_dboption dbname, "single user", true')
0
csabayAuthor Commented:
Thanks,

jkotek,

having troubles with me. Yes, I have got an error as follows:

Indexes on sysobjects and sysindexes cannot be recreated.
(Msg 2598, Level 16, State 1).

Over my database options
select into/bulk copy
single user only
are on, other options are off.

Thanks again -

Ch.
0
jkotekCommented:
This will be a tricky one... And will only help if we talk about nonclustered index.

I have following howto from 'Troubleshooting and Error messages Guide', that can be found at http://sybooks.sybase.com:80/onlinebooks/group-as/asg1192e/svrtsg/
or here if the exact walkthrough http://sybooks.sybase.com:80/onlinebooks/group-as/asg1192e/svrtsg/@Generic__BookTextView/6579;cs=default;ts=default;pt=6579#X

Get the indid of the corrupted index (...from sysindexes where id=1 [sysobjects have objectid=1]).

Now set 'allow updates' on and put the db into single user mode.

Get and write down the sysobject's sysstat value from sysobjects table:
select sysstat from sysobjects where id=1

Modify this sysstat value:
update sysobjects set sysstat = sysstat | 4096 where id = 1

Run stored procedure sp_fixindex:
sp_fixindex database_name, sysobjects, 2

Restore the sysstat value:
update sysobjects set sysstat = sysstat_ORIGINAL where id=1

Run dbcc checktable to verify the index.

Turn off 'allow updates' and 'single-user mode'.

Finished
0
csabayAuthor Commented:
Adjusted points to 200
0
csabayAuthor Commented:
I'm sorry,

jkotek,

there are two problems with your proposal:

1. I cannot come out with the being of sysobjects' indices; when I ask them by sp_help  that refers as follows:

index_name: sysobjects
index_description: clustered, unique...
index_keys: id
....

As I try to create a table I get the following error message:

"Could not find leaf row in nonclustered index 'sysobjects' that corresponds to data row from logical data page 6, row offset 4 during update index
(Msg 2610, Level 22, State 1)."

Well, is now the index sysobject a clustered or a nonclustered one?!

2. I haven't got sp_fixindex; my Sybase is a 4.9.2. I could imagine that you wolud be so kind to defncopy out this procedure and send me (csabay@isis.elte.hu). It can happen, of course, that this procedure does not run under a 4.9.2 but I shall have tried.

A lot of thanks -

Charles

0
jkotekCommented:
I am not sure if this will work for the clustered index. And in 4.9 .

However here is the script from 11.9.2 .

/*
**Messages for "sp_fixindex"      18055
*/

exec sp_instmsg 18055, "Procedure should be used on system tables only."
exec sp_instmsg 18056, "Cannot re-create index on this table."
exec sp_instmsg 18057, "The index with id 1 on sysobjects can not be recreated."

if exists (select *
      from sysobjects
            where sysstat & 7 = 4
                  and name = 'sp_fixindex')
begin
      drop procedure sp_fixindex
end
go
print "Installing sp_fixindex"
go

/* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
/*      4.8      1.1      06/14/90      sproc/src/fixindex */


create procedure sp_fixindex
            @dbname            varchar(30),
            @tabname      varchar(30),              /* system table name */
            @indid            int                  /* index id value    */
as
      /*
      **      Description:      allow the SA to force a drop and then a
      **                  create index on system catalogs.
      **
      **      Usage:            sp_fixindex  database, systemcatalog, ind_id
      **
      **      History:      3/28/88            (robbie)
      */

      declare      @ramboix smallint                  /* mask for rambo indx*/
      declare @badlist  varchar(255)   /* name list of all tables that should
                                    not have any indexes on them */



      if @@trancount = 0
      begin
            set chained off
      end

      set transaction isolation level 1

      /* check if user has SA role, proc_role will also do auditing
      ** if required. proc_role will also print error message if required.
      */

        if (proc_role("sa_role") = 0)
                return (1)

      /*
      **      Initialize name lists. These will be used in catching
      **      any attempts to fix indexes on fake tables or tables
      **      that have no indexes on them.
      */
      select @badlist = "syslocks, sysprocesses, syscurconfig,
                     syssegments, sysindexes"

      /*
      **      Make sure we are "fixing" a system catalog.
      */
      if @tabname NOT LIKE  "sys%"
      begin
            /*
            ** 18055: "Procedure should be used on system tables only."
            */
            raiserror 18055
            return (1)
      end

      /*
      **      Make sure that we are doing this on somenthing that
      **      has indexes (or real tables).
      */
      if @badlist LIKE  "%" + @tabname + "%"
      begin
            /*
            ** 18056: "Cannot re-create index on this table."
            */
            raiserror 18056
            return (1)
      end

      /*
      **      Make sure that we can not re-create index
      **      on index of id 1 on sysobjects.
      */
      if ((@tabname = "sysobjects" and @indid = 1))
      begin
            /*
            ** 18057: "The index with id 1 on sysobjects can not be
            ** recreated."
            */
            raiserror 18057
            return (1)
      end





      /*
      **      At this point we are sure that the table has an index
      **      of requested type, so all there is to be done is to
        **      set the ramboix bit for this object.
        **      NB: we have explicitly disallowed sysobjects, index id 1
        **          above.
        */
        select @ramboix = 4096
        update sysobjects set sysstat = sysstat | @ramboix
        where name = @tabname
 
        checkpoint

      /*
      **      Call dbcc to do the job.
      */
      dbcc dbrepair(@dbname, REPAIRINDEX, @tabname, @indid)

      /*
      **      Processing successfully done, so turn off the ramboix
      **      bit if not sysobjects, and tell the user that all is fine.
      */
      if @tabname != "sysobjects"
      begin
            update sysobjects set sysstat = sysstat & ~@ramboix
            where name = @tabname
      
            checkpoint
      end

      return (0)
go
exec sp_procxmode 'sp_fixindex', 'anymode'
go
grant execute on sp_fixindex to public
go
dump tran master with truncate_only
go
dump transaction sybsystemprocs with truncate_only
go

0
csabayAuthor Commented:
Adjusted points to 220
0
csabayAuthor Commented:
Sorry,

jkotek,

the script you have sent me does not work under 4.9.2.

By the way: have you got any idea what kind of indices sysobjects could be?

Thanks -

Charles
0
jkotekCommented:
sorry about that. maybye its time to call Sybase tech support. they can dial in to your server and manually fix the index.

The sysobjects table is one of the most important system tables - it contains information on all 'objects' in database - tables, indexes, views, stored procedures, triggers...
0
albertspCommented:
I think that you have to rebuild your database.

1. Create scripts for rebuilding tables, indexes, users, segments, triggers,stored procs etc.
2. Bulk copy your data out.
3. Drop database
4 recreate database
5. create database objects (not indexes)
6. Bulk copy your data in
7 create indexes.

Not a nice action but I fear that it is the only possibility.

Succes, Albert
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
csabayAuthor Commented:
Hi, albertsp!

I did not execute your proposal; namely I know that it will work. I tried to make it without opening another database. (Lack of disk space, you know.) In spite of that I accept the answer: I don't want to block the question and I want you earn points.

By the way I have to say the most troble was done by jkotek. I'm sorry that I could not grant points to him/her. That was a hard job, children, and we are quite not at the end.

Thanks for all -

Charles
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.