<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Advertise
Write for us
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 7,2000
»
Administration
»
Noncluster at sysindexes
14 posts, Page 1 of 2
1
2
»»
Noncluster at sysindexes
Rate Topic
Display Mode
Topic Options
Noncluster at sysindexes
Poll Results
Votes
Noncluster at sysindexes
0%
0
Member Votes: 0, Anonymous Votes: 0.
You don't have permission to vote within this poll.
Author
Message
niranjankumar_k
niranjankumar_k
Posted Wednesday, February 20, 2008 11:09 PM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, July 24, 2008 11:51 PM
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...
Post #458367
GilaMonster
GilaMonster
Posted Wednesday, February 20, 2008 11:46 PM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 17,105,
Visits: 12,207
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
Post #458373
niranjankumar_k
niranjankumar_k
Posted Thursday, February 21, 2008 12:05 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, July 24, 2008 11:51 PM
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
Post #458375
GilaMonster
GilaMonster
Posted Thursday, February 21, 2008 12:13 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 17,105,
Visits: 12,207
If you try to drop one of those indexes that appears in sysindexes for that table, what happens?
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
Post #458377
niranjankumar_k
niranjankumar_k
Posted Thursday, February 21, 2008 12:18 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, July 24, 2008 11:51 PM
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.
Post #458378
GilaMonster
GilaMonster
Posted Thursday, February 21, 2008 12:40 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 17,105,
Visits: 12,207
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
Post #458380
niranjankumar_k
niranjankumar_k
Posted Thursday, February 21, 2008 1:28 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, July 24, 2008 11:51 PM
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...
Post #458390
GilaMonster
GilaMonster
Posted Thursday, February 21, 2008 2:58 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 17,105,
Visits: 12,207
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
Post #458422
niranjankumar_k
niranjankumar_k
Posted Thursday, February 21, 2008 3:18 AM
SSC Rookie
Group: General Forum Members
Last Login: Thursday, July 24, 2008 11:51 PM
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
Post #458434
GilaMonster
GilaMonster
Posted Thursday, February 21, 2008 10:18 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 17,105,
Visits: 12,207
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
We walk in the dark places no others will enter
We stand on the bridge and none may pass
Post #458669
« Prev Topic
|
Next Topic »
14 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2010 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use
