Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Noncluster at sysindexes


Noncluster at sysindexes

Poll
Noncluster at sysindexes

0% - 0 votes Noncluster at sysindexes
0% 0 votes
Member votes: 0, Guest votes: 0. You don't have permission to vote in this poll
Author
Message
niranjankumar_k
niranjankumar_k
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 119
Table "auto" (1024 column exist ) is not having any references and contraints. In sp_help it is not showing any indexes on the table. however it has 249 entries in sysindexes , i need to create only 3 index on table. if i try to create non clustered index it throws more than 249 non cluster is not allowed. but i have not created any index on the table...

How the entry happened in sysindexes for non cluster....

also i generate the same table script and create new instance of the table. now i see only one entry in sysindexes..



how that happened...
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47396 Visits: 44399
Strange.

Can you run a checkDB on that database? See if there's any form of corruption.

Do you know if anyone there does direct updates of the system tables? check if updates are allowed - (exec sp_configure ) and see what the value of allow_updates is.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


niranjankumar_k
niranjankumar_k
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 119
after executing

dbcc checkdb

DBCC results for 'AUTO'.

There are 154783 rows in 154784 pages for object 'AUTO'.

DBCC results for 'sysindexes'.

There are 1856 rows in 112 pages for object 'sysindexes'.

Also executed the sp_sonfigure .. it shows

minimum 0
maximum 0
rest are 0

By keeping this result how to find my solution
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47396 Visits: 44399
If you try to drop one of those indexes that appears in sysindexes for that table, what happens?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


niranjankumar_k
niranjankumar_k
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 119
I am sorry for posting two time the same topic.. this is first time i am posting..

i tried to delete .. however i dont have rights...

error throws ...
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47396 Visits: 44399
No, no, no. I didn't mean delete from the system table. That's guaranted to stuff things up.

I meant drop the index.

DROP INDEX <TableName>.<IndexName>


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


niranjankumar_k
niranjankumar_k
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 119
I have not created any index on table.. also if see by using sp_help there is no indexes ... but only thing is entry is available in the sysindex.. if table auto table shows the index only i cud drop.. so only i am confused how the entry happened in sysidexes...
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47396 Visits: 44399
I know you haven't created any inde3xes. I'm trying to identify what the entries in sysindexes are and how they got there.

Please run the following query and post the results here.

SELECT name, indid, status, INDEXPROPERTY(id, indid, IsStatistics) FROM sysindexes WHERE id = OBJECT_ID('Auto')


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


niranjankumar_k
niranjankumar_k
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 119
Gila .. i am here only .. u will get my reply soonly.. here it shows 250 entries... table having 1024 columns...

AUTO 0 0 NULL
_WA_s_TRX_TIMESTAMP_7227B923 4 10485856 NULL
_WA_s_CODE_7227B923 5 10485856 NULL
_WA_s_USER_ID_7227B923 9 10485856 NULL
_WA_s_sKEY_7227B923 10 8388704 NULL
_WA_s_DQ_CD_7227B923 12 10485856 NULL
_WA_s_RECORD_TYPE_7227B923 15 10485856 NULL
_WA_s_DRV_1_NUMBER_7227B923 16 10485856 NULL
_WA_s_VEH_1_MODEL_YEAR_7227B923 17 10485856 NULL
_WA_s_NI_LAST_NAME_7227B923 18 10485856 NULL
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47396 Visits: 44399
I should have thought of that earlier.

Those aren't indexes. They're automatically created column statistics. The query optimiser creates those to better know the data distribution within a column.

You can drop them. SQL will recreate them if necessary.

For each of the entries in sysindexes that begin with _WA_Sys, run the following

DROP STATISTICS auto.<Statistic Name>

Statistics can indicate a column where an index would be useful, so you may want to watch which columns get stats and consider creating indexes on them. Also, from what I saw of the column names, your table is highly unnormalised. Is that intentional?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search