Dbcc dbindexdefrag

[dougancil]

I'm trying to reindex a table in my database using the following query:

DBCC INDEXDEFRAG (archive, marchive) and keep getting error Msg 2583.

Can anyone tell me what may be causing this error?

Thanks

Doug

[ck9663]

That command will defrag the index not recreate it. If you only need to recreate the indexes of your table, read this...

Good Luck!!!

~~ CK

[dougancil]

CK,

Does that put a lock on the index? This table is active and really can't be taken offline.

[ck9663]

Most re-index will... If you have a good server and small data, it should be fast. Otherwise, schedule this during off-peak time...

Good Luck!!!

~~ CK

[dougancil]

CK,

I understand that, which is why I wanted to just do a dbindexdefrag for now. Any idea as to why I keep getting an error on that query that I supplied?

[ck9663]

Other than missing parameter, BOL says it shows up when you try indexing a system table. Try specifying which index you want so you can isolate the issue.

Good Luck!!!

~~ CK

[dougancil]

CK,

since I didn't know the names of the indexes, I ran a query to get them and when I try this query:


dbcc dbindexdefrag (archive, marchive, Account)

it's still failing, telling me that its an incorrect dbcc statement. I know that the parameters are correct. Any idea what you think may be causing this?

Thanks

Doug

[ck9663]

Will a quote on those parameter matter?

~~ CK

[dougancil]

CK,

Here's what I tried next:

dbcc dbindexdefrag ('archive','mar chive','account ')

and tried every permutation of that. The odd thing is that if I parse it, the query says that it completes correctly. If I try to run it, that's when I get the error back.

[dougancil]

I found out that for sql 2000 the syntax is not dbcc dbindexdefrag
but dbcc indexdefrag, just as an fyi.