Using EXEC for DBCC

Question

Using EXEC for DBCC

Ranga N

SSChasing Mays

Points: 642
More actions
March 28, 2006 at 2:11 pm

#114044

Hi,
I am trying to create a generic SP to do DBCC INDEXDEFRAG on all the user table indexes, so have to use dynamic sql....
But after I execute the below command in QA, it is just done immeaditely with the result below...The logical fragmentation is still high, same as it was, looks like indexdefrag was not done...Not sure whats going on...

SQL:
declare @strsql varchar(500)
set @strsql = ' dbcc indexdefrag('+'''DBName'''+',41554452,3)'
select (@strsql)
exec (@strsql)

Result:
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
3 0 0

Thanks,
Ranga

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

SQL Server is not always able to defag an index. What happens if you run the DBCC command straight from QA and not in the stored procedure using dynamic SQL? Does it still not defrag the index?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 2

Hi,

have a look at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Microsoft tells us that index reorganisation has an impact on performance only for indexes bigger than 1000 pages...

regards karl

Best regards
karl

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

You may use

sp_MSforeachdb

to perform tasks on each database. See

http://www.sqlservercentral.com/columnists/achigrik/sql2000.asp

SQL Server 2000 Useful Undocumented Stored Procedures

by Alexander Chigrik here on this site

Regards,Yelena Varsha

Ranga N SSChasing Mays Points: 642 More actions · Answer 4

Thanks for your responses...I changed my code to defrag indexes which have more than 1000 pages.