syscomments

RocketScientist's Miscellaneous Ramblings
posts - 76, comments - 354, trackbacks - 3

DBREINDEX and INDEXDEFRAG

If you ever needed more evidence that Microsoft is a big company and often any two given people who should talk to each other don't, here you go. DBCC DBREINDEX and DBCC INDEXDEFRAG do very similar jobs, and should take an identical set of input parameters. DBREINDEX takes at a minimum a table name, and it will reindex the table. INDEXDEFRAG, however, takes a minimum of 3 parameters: Database name, Table Name and Index name (specified as DBCC INDEXDEFRAG ('MyDB', 'MyTable', 'MyIndex')). It is neccessary to pass all three. Why? I've got to write a cursor on sysindexes AND sysobjects to defrag indexes. Ick. Why not make both commands the same? Do we not have enough stupid arcane knowledge to keep in our poor little heads without this kind of...uh...(graz wants a G rating)...poo?

In case you don't know the difference, DBREINDEX completely reindexes each table, and INDEXDEFRAG just defragments the indexes. The fundamental practical difference between the two is that DBREINDEX on a table witha clustered index will acquire an exclusive table lock on the table for the duration, and INDEXDEFRAG only acquires a shared lock (enough of a lock to keep the table from being dropped) for it's duration. INDEXDEFRAG is supposedly less effective, but if you're dealing with tables that actually get changed (inserts, updates, deletes) it's probably just as good over some period of time/number of transactions.

rs.

Print | posted on Wednesday, October 29, 2003 12:29 PM |

Feedback

Gravatar

# re: DBREINDEX and INDEXDEFRAG

Well, after that feed back, how can I say more.. :P

There is a difference between them, if you look more closely. One DBREINDEX reindexes all tables and indexes, while INDEXDEFRAG reindex one specific index. INDEXDEFRAG is a very good think we you have a very large database and don't want to take out all of SQL because of one fragged index file.
10/27/2004 1:54 PM | Gavin
Gravatar

# re: DBREINDEX and INDEXDEFRAG

hi there,

We are facing problem in reorganize database.
During reorganizing data base it come out giving error MSG.
Error MSG is pasted for your reference.


Rebuilding indexes for table 'S260BIW1'
[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0: [Microsoft][ODBC
SQL Server Driver][Named Pipes]ConnectionCheckForData (PeekNamedPipe()).
[Microsoft][ODBC SQL Server Driver][Named Pipes]Connection broken.

** Execution Time: 23 hrs, 14 mins, 38 secs **

[Microsoft SQL-DMO (ODBC SQLState: HYT00)] Error 0: [Microsoft][ODBC
SQL Server Driver]Timeout expired
End of maintenance plan 'DB Maintenance Plan1' on Sun Oct 31 21:29:24
2004

SQLMAINT.EXE Process Exit Code: 1 (Failed)

KIndly look in to it & help
11/4/2004 11:23 PM | S260BIW1
Gravatar

# re: DBREINDEX and INDEXDEFRAG

Check your errorlog. I bet one of two things is happening:

1. The maintenance plan wizard is timing out before the operation completes. I'd suggest using something besides the maint plan wizard if your database is that large.

2. Your connection is getting dropped because of some other error. That's why you need to check the error log.


rs.
11/5/2004 9:12 AM | rocketscientist
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET