Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Noncluster at sysindexes Expand / Collapse
Noncluster at sysindexes
Poll ResultsVotes
Noncluster at sysindexes
0%
0
Member Votes: 0, Anonymous Votes: 0. You don't have permission to vote within this poll.
Author
Message
Posted Wednesday, February 20, 2008 11:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, February 20, 2008 11:46 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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

Post #458373
Posted Thursday, February 21, 2008 12:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, February 21, 2008 12:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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

Post #458377
Posted Thursday, February 21, 2008 12:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, February 21, 2008 12:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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

Post #458380
Posted Thursday, February 21, 2008 1:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, February 21, 2008 2:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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

Post #458422
Posted Thursday, February 21, 2008 3:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, February 21, 2008 10:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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

Post #458669
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse