Monday, March 19, 2012

Defraging database / help

Hi all
I want to defrag the SQL database
yes this is code
DBCC INDEXDEFRAG (Northwind, Orders, CustomersOrders)
but this will defrag a specific Table
but i want defrag entire a specific database
how can i do it
ThanksCreate a view in your database:

create view dbo.vw_DBCC_INDEXDEFRAG as
select cmd = 'dbcc indexdefrag (' + db_name() + ', ' + o.name + ', ' + i.name + ')'
from sysindexes i
inner join sysobjects o on i.id=o.id
where objectproperty(o.id, 'IsMSShipped') = 0
and objectproperty(o.id, 'IsTable') = 1
and indid > 0 and indid < 255 and (i.status & 64)=0

Then, at command prompt type the following:

C:\>BCP <db_name>.dbo.vw_DBCC_INDEXDEFRAG out DBCC_INDEXDEFRAG.SQL -S <your_server> -T -c
C:\>OSQL -i DBCC_INDEXDEFRAG.SQL -o DBCC_INDEXDEFRAG.OUT -S <server_name> -d <your_database> -E -h-1 -n -w 256

...And you're done.

No comments:

Post a Comment