?
Solved

reindexing sysobjects

Posted on 2000-01-30
14
Medium Priority
?
2,248 Views
Last Modified: 2010-08-05
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)


0
Comment
Question by:csabay
  • 8
  • 5
14 Comments
 
LVL 2

Expert Comment

by:jkotek
ID: 2456884

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

2) run dbcc reindex(tablename) to repair the damaged indexes
0
 

Author Comment

by:csabay
ID: 2466085
Adjusted points to 130
0
 

Author Comment

by:csabay
ID: 2466086
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 2

Expert Comment

by:jkotek
ID: 2477400
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
 

Author Comment

by:csabay
ID: 2477684
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
 
LVL 2

Expert Comment

by:jkotek
ID: 2478512
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
 

Author Comment

by:csabay
ID: 2489148
Adjusted points to 200
0
 

Author Comment

by:csabay
ID: 2489149
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
 
LVL 2

Expert Comment

by:jkotek
ID: 2494127
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
 

Author Comment

by:csabay
ID: 2500267
Adjusted points to 220
0
 

Author Comment

by:csabay
ID: 2500268
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
 
LVL 2

Expert Comment

by:jkotek
ID: 2500811
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
 
LVL 1

Accepted Solution

by:
albertsp earned 660 total points
ID: 2500943
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
 

Author Comment

by:csabay
ID: 2518657
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data security in the cloud is very much like a security in an on-premises data center - only without costs for maintaining facilities and computer hardware.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question