there are 350 tables in database.in each hour 60 records appended to each
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
Message posted via http://www.sqlmonster.com
sepideh iranpour via SQLMonster.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
David Gugick
Imceda Software
www.imceda.com
sql
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Tuesday, March 27, 2012
database grows abnormally
there are 350 tables in database.in each hour 60 records appended to each
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
--
Message posted via http://www.sqlmonster.comsepideh iranpour via SQLMonster.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
--
David Gugick
Imceda Software
www.imceda.com
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
--
Message posted via http://www.sqlmonster.comsepideh iranpour via SQLMonster.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
--
David Gugick
Imceda Software
www.imceda.com
database grows abnormally
there are 350 tables in database.in each hour 60 records appended to each
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
Message posted via http://www.droptable.comsepideh iranpour via droptable.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
David Gugick
Imceda Software
www.imceda.com
table.there is a unclustered index on each table.
but database size grows abnomarly (fragmentation) .i exported and imported
it then the size was normal .but again after inserting records
the size of grows abnormally.
this db is used in real time system so time is impossibe.when i used
clustered index the insertion action was very slow.so i used unclustred
index.
i used dbreindex , defrag whitout any advantage for defragmanting.
thanks of all.
Message posted via http://www.droptable.comsepideh iranpour via droptable.com wrote:
> there are 350 tables in database.in each hour 60 records appended to
> each table.there is a unclustered index on each table.
> but database size grows abnomarly (fragmentation) .i exported and
> imported it then the size was normal .but again after inserting
> records
> the size of grows abnormally.
> this db is used in real time system so time is impossibe.when i used
> clustered index the insertion action was very slow.so i used
> unclustred index.
>
> i used dbreindex , defrag whitout any advantage for defragmanting.
> thanks of all.
Is it possible the database is just auto-growing to accommodate the
newly inserted data? i don't think fragmentation would itself be the
cause of abnormal growth. Is it just the data file size that's growing
and not really the underlying data in the file(s)?
David Gugick
Imceda Software
www.imceda.com
Tuesday, February 14, 2012
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.
Leila
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
>
|||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...
>
|||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...
>
|||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...
>
|||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...
>
|||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...
>
|||This option does not help
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23EandTgIHHA.3268@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> 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...
|||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
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
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...
>
|||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...
>
|||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...
>
|||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...
>
|||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...
>
|||This option does not help
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23EandTgIHHA.3268@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> 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...
|||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
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/in..._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/in..._suspect_db.asp
>
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.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...
>|||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...
>|||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...
>|||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...
>|||You might be able to do it if you sp_configure 'allow updates'. Not sure, th
ough.
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...[vbcol=seagreen]
> 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...|||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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
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/in..._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/in..._suspect_db.asp
>
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23z2JEMdIHHA.1248@.TK2MSFTNGP02.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...
>|||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...
>|||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...
>|||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...
>|||You might be able to do it if you sp_configure 'allow updates'. Not sure, th
ough.
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...[vbcol=seagreen]
> 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...|||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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
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?
I found this link:
http://support.microsoft.com/kb/106122
Although it mentions SP_FixIndex can drop such indexes but there are no description about it.
Any help would be greatly appreciated.
LeilaToday's lesson: "Don't f*** with system tables."|||Any solution?!..|||You may be able to drop the index if you run the SET OPTION command to allow changed to system objects.
Make sure you execute the command to disallow changes when you are done!|||It doesn't allow to drop index from system tables|||Did you try the SET OPTION command?
Otherwise, create a new blank database and copy your production tables to it, and then drop the old database with its corrupted system table.
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?
I found this link:
http://support.microsoft.com/kb/106122
Although it mentions SP_FixIndex can drop such indexes but there are no description about it.
Any help would be greatly appreciated.
LeilaToday's lesson: "Don't f*** with system tables."|||Any solution?!..|||You may be able to drop the index if you run the SET OPTION command to allow changed to system objects.
Make sure you execute the command to disallow changes when you are done!|||It doesn't allow to drop index from system tables|||Did you try the SET OPTION command?
Otherwise, create a new blank database and copy your production tables to it, and then drop the old database with its corrupted system table.
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
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
Subscribe to:
Posts (Atom)