February 20, 2008 at 11:09 pm
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...
February 20, 2008 at 11:46 pm
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
February 21, 2008 at 12:05 am
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
February 21, 2008 at 12:13 am
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
February 21, 2008 at 12:18 am
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.
February 21, 2008 at 12:40 am
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
February 21, 2008 at 1:28 am
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...
February 21, 2008 at 2:58 am
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
February 21, 2008 at 3:18 am
Gila .. i am here only .. u will get my reply soonly.. here it shows 250 entries... table having 1024 columns...
AUTO 00NULL
_WA_s_TRX_TIMESTAMP_7227B923410485856NULL
_WA_s_CODE_7227B923510485856NULL
_WA_s_USER_ID_7227B923910485856NULL
_WA_s_sKEY_7227B923108388704NULL
_WA_s_DQ_CD_7227B9231210485856NULL
_WA_s_RECORD_TYPE_7227B9231510485856NULL
_WA_s_DRV_1_NUMBER_7227B9231610485856NULL
_WA_s_VEH_1_MODEL_YEAR_7227B9231710485856NULL
_WA_s_NI_LAST_NAME_7227B9231810485856NULL
February 21, 2008 at 10:18 am
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
February 21, 2008 at 10:52 pm
What i have to enter at Statistics name ..
i tried by column name and _w.. also
February 21, 2008 at 10:59 pm
The name that you get from sysindexes.
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
February 22, 2008 at 11:30 pm
how to find the statistics name ???
February 23, 2008 at 7:48 am
It's the name listed in sysindexes. (From the list you gave earlier, all the ones like _WA_s_....)
SELECT name FROM sysindxes WHERE INDEXPROPERTY(id, indid, 'IsStatistics') = 1 will give you the names of all the statistics. You can then use DROP STATISTICS to drop these. You should then be able to create indexes.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply