• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

database Defragmenting

hi
i need a  query, for Defragmenting  database in sql 2005 ?
not for only indexs

thanks
0
mekusharim_ltd
Asked:
mekusharim_ltd
  • 3
  • 3
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
If you have a clustered index, you don't need to defrag it as all records of your database are in that physical order. You may consider scheduling a maintenance plan to re-index and delete empty spots in the tables
0
 
mekusharim_ltdAuthor Commented:
ok,

but i need to defrag the database  .

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello mekusharim_ltd,

>but i need to defrag the database  .

I think you are looking for REINDEXING

DBCC REINDEX


Aneesh R
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mekusharim_ltdAuthor Commented:
the "DBCC REINDEX" do defrag  to all database without locks ??
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Okay, then Use DBCC INDEXDEFRAG, which Defragments clustered and secondary indexes of the specified table or view.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.

0
 
mekusharim_ltdAuthor Commented:
can i do Defragments to table, not to index ??

i think the DBCC DBREINDEX do only index
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
if you have the Clustured index defined on the table , then DBCC INDEXDEFRAG will do the trick, otherwise there is no otherway.
now if you wanna reclaim the  space which you were using for some text / nvarchar/ varchar columns, which you deleted, then you can use DBCC CLEANTABLE  to reclaim the space
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now