Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bpsintl
Posting Yak Master
132 Posts |
 Posted - 2009-04-10 : 05:53:11
|
| I have a script that loops through all tables in my db and defrags the indexes. I'm using the following code for this:USE mydbDECLARE @TableName sysnameDECLARE @indid intDECLARE cur_tblfetch CURSOR FORSELECT table_name FROM information_schema.tables WHERE table_type = 'base table'OPEN cur_tblfetchFETCH NEXT FROM cur_tblfetch INTO @TableNameWHILE @@FETCH_STATUS = 0BEGINDECLARE cur_indfetch CURSOR FORSELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0OPEN cur_indfetchFETCH NEXT FROM cur_indfetch INTO @indidWHILE @@FETCH_STATUS = 0BEGIN SELECT 'Derfagmenting index_id = ' + convert(char(3), @indid) + 'of the ' + rtrim(@TableName) + ' table' IF @indid <> 255 DBCC INDEXDEFRAG (mydb, @TableName, @indid) FETCH NEXT FROM cur_indfetch INTO @indidENDCLOSE cur_indfetchDEALLOCATE cur_indfetch FETCH NEXT FROM cur_tblfetch INTO @TableNameENDCLOSE cur_tblfetchDEALLOCATE cur_tblfetchWhen I run it I get multiple errors like:Msg 7999, Level 16, State 41, Line 18Could not find any index named '_WA_Sys_00000003_3BFFE745' for table 'mytable'.Any ideas why? |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2009-04-11 : 08:00:48
|
| This looks like an autogenerated statistic - you are not supposed to defrag these stats.Use INDEXPROPERTY() to identify their statusJack Vamvas--------------------http://www.ITjobfeed.com |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
bpsintl
Posting Yak Master
132 Posts |
Posted - 2009-04-14 : 06:30:42
|
| Thanks tkizer |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
index defrag - getting index missing errors