defraging sql database

[v555]

please can anybody help me with this problem,our datadase is fragermented to nearly 80 and i have tried using the dbcc indexdefrag and eachtime i tried i get the following error."an in correct number of parameters where giving to the DBCC statement".or can you send the script on how to defrag sql database.thanks for your cooeration

 

[JayKusch]

If you are trying to reindex the DB, you have the wrong command. Your command, IndexDefrag, works only for a specific table w/in a specific DB.

If you are trying to defrag your DB, you will need to run ... DBReIndex.

Remember though (Straight from BOL) ...

Unlike DBCC DBREINDEX (or the index building operation in general), DBCC INDEXDEFRAG is an online operation. It does not hold locks long term and thus will not block running queries or updates. A relatively unfragmented index can be defragmented faster than a new index can be built because the time to defragment is related to the amount of fragmentation. A very fragmented index might take considerably longer to defragment than to rebuild. In addition, the defragmentation is always fully logged, regardless of the database recovery model setting (see ALTER DATABASE). The defragmentation of a very fragmented index can generate more log than even a fully logged index creation. The defragmentation, however, is performed as a series of short transactions and thus does not require a large log if log backups are taken frequently or if the recovery model setting is SIMPLE.



Thanks

J. Kusch

 

[v555]

Hi jay,
thanks for the reply,you where right about the difference between INDEXDEFRAG and DBREINDEX but my question is how to make it work using the query analyzer,because when i type the DBCC SHOWCONTIG the letters are all blue and you can excute the command,but when i type DBCC INDEXDEFRAG only the dbcc is blue and not the INDEXDEFRAG then i get the error in the bottom."an in correct number of parameters where giving to the DBCC statement".moreover due the current situation of the database i will rather run the indexdefrag online then after i can rebuild the index using the DREINDEX so please if you know how i can type all the maintanace or excute the bd maintanance commands on the query analyzer please let me and i will appreciate it.

 

[SQLBill]

You need to include the database name, table name and index name.

DBCC INDEXDEFRAG (mydb, mytable, myindex)

Refer to the BOL for more information.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875