Database Corruption (Please help!)

Hi,
I created a nonclustered index on sysobjects table. After that, when I
create a new object and sysobjects is modified, the database does not work
properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
object is created, again database get corrupted (CHECKDB reports error). I
cannot drop this index with DROP INDEX command.
I'm wondered if an index on system table cannot be dropped, why it can be
created? If it is allowed to create index, why it corrupts the database?
Any help would be greatly appreciated.
LeilaLeila
First of all , go to the ERROR.LOG and see what is going on?
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I created a nonclustered index on sysobjects table. After that, when I
> create a new object and sysobjects is modified, the database does not work
> properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
> object is created, again database get corrupted (CHECKDB reports error). I
> cannot drop this index with DROP INDEX command.
> I'm wondered if an index on system table cannot be dropped, why it can be
> created? If it is allowed to create index, why it corrupts the database?
> Any help would be greatly appreciated.
> Leila
>|||I found this link but I don't know how to drop the index!
http://support.microsoft.com/kb/106122
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Leila
> First of all , go to the ERROR.LOG and see what is going on?
> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon as
>> a new object is created, again database get corrupted (CHECKDB reports
>> error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can be
>> created? If it is allowed to create index, why it corrupts the database?
>> Any help would be greatly appreciated.
>> Leila
>|||Leila
There are pretty good examples telling how to do that
"Leila" <Leilas@.hotpop.com> wrote in message
news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I found this link but I don't know how to drop the index!
> http://support.microsoft.com/kb/106122
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> First of all , go to the ERROR.LOG and see what is going on?
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>>
>>
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon as
>> a new object is created, again database get corrupted (CHECKDB reports
>> error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can
>> be created? If it is allowed to create index, why it corrupts the
>> database?
>> Any help would be greatly appreciated.
>> Leila
>>
>|||But I didn't find any example about dropping an index in this article
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Leila
> There are pretty good examples telling how to do that
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>>I found this link but I don't know how to drop the index!
>> http://support.microsoft.com/kb/106122
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> First of all , go to the ERROR.LOG and see what is going on?
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>>
>>
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon
>> as a new object is created, again database get corrupted (CHECKDB
>> reports error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can
>> be created? If it is allowed to create index, why it corrupts the
>> database?
>> Any help would be greatly appreciated.
>> Leila
>>
>>
>|||It does not drop the index, it fixes the index ( perhaps by droping it)
:-)))) Do you have a good backup of the database?
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23GS$VYdIHHA.536@.TK2MSFTNGP02.phx.gbl...
> But I didn't find any example about dropping an index in this article
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> There are pretty good examples telling how to do that
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>>I found this link but I don't know how to drop the index!
>> http://support.microsoft.com/kb/106122
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> First of all , go to the ERROR.LOG and see what is going on?
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>>
>>
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon
>> as a new object is created, again database get corrupted (CHECKDB
>> reports error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can
>> be created? If it is allowed to create index, why it corrupts the
>> database?
>> Any help would be greatly appreciated.
>> Leila
>>
>>
>>
>|||We discovered this problem a little late. This exists in the database since
10 days ago. If we restore the backup before the corruption, 10 days of
users work will lost!
That article mentions that you can drop the index of system tables but I
couldn't find anything!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eDZm7edIHHA.3424@.TK2MSFTNGP02.phx.gbl...
> It does not drop the index, it fixes the index ( perhaps by droping it)
> :-)))) Do you have a good backup of the database?
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23GS$VYdIHHA.536@.TK2MSFTNGP02.phx.gbl...
>> But I didn't find any example about dropping an index in this article
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uZQr3VdIHHA.2456@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> There are pretty good examples telling how to do that
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%238EDYTdIHHA.1468@.TK2MSFTNGP04.phx.gbl...
>>I found this link but I don't know how to drop the index!
>> http://support.microsoft.com/kb/106122
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eAUl4QdIHHA.3872@.TK2MSFTNGP06.phx.gbl...
>> Leila
>> First of all , go to the ERROR.LOG and see what is going on?
>> http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
>>
>>
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when
>> I create a new object and sysobjects is modified, the database does
>> not work properly. I used DBCC CHECKDB to repair it. It works but as
>> soon as a new object is created, again database get corrupted
>> (CHECKDB reports error). I cannot drop this index with DROP INDEX
>> command.
>> I'm wondered if an index on system table cannot be dropped, why it
>> can be created? If it is allowed to create index, why it corrupts the
>> database?
>> Any help would be greatly appreciated.
>> Leila
>>
>>
>>
>>
>|||You might be able to do it if you sp_configure 'allow updates'. Not sure, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <Leilas@.hotpop.com> wrote in message news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I created a nonclustered index on sysobjects table. After that, when I
> create a new object and sysobjects is modified, the database does not work
> properly. I used DBCC CHECKDB to repair it. It works but as soon as a new
> object is created, again database get corrupted (CHECKDB reports error). I
> cannot drop this index with DROP INDEX command.
> I'm wondered if an index on system table cannot be dropped, why it can be
> created? If it is allowed to create index, why it corrupts the database?
> Any help would be greatly appreciated.
> Leila
>|||This option does not help :(
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23EandTgIHHA.3268@.TK2MSFTNGP04.phx.gbl...
> You might be able to do it if you sp_configure 'allow updates'. Not sure,
> though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I created a nonclustered index on sysobjects table. After that, when I
>> create a new object and sysobjects is modified, the database does not
>> work properly. I used DBCC CHECKDB to repair it. It works but as soon as
>> a new object is created, again database get corrupted (CHECKDB reports
>> error). I cannot drop this index with DROP INDEX command.
>> I'm wondered if an index on system table cannot be dropped, why it can be
>> created? If it is allowed to create index, why it corrupts the database?
>> Any help would be greatly appreciated.
>> Leila|||Leila (Leilas@.hotpop.com) writes:
> I created a nonclustered index on sysobjects table.
Uh-uh, never do that again, promise? :-)
> After that, when I create a new object and sysobjects is modified, the
> database does not work properly. I used DBCC CHECKDB to repair it. It
> works but as soon as a new object is created, again database get
> corrupted (CHECKDB reports error). I cannot drop this index with DROP
> INDEX command. I'm wondered if an index on system table cannot be
> dropped, why it can be created? If it is allowed to create index, why it
> corrupts the database?
It seems that you need to enable "Allow updates" to create an index on a
system table. With this setting all bets are off anyway.
I would recommend that you create a new database from scripts and bulk
data over.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Perfect Match Finder
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
Online Web Promotion
http://www.max-online.biz/idevaffiliate/idevaffiliate.php?id=804
For further details email me at maxonline.sunil@.gmail.com