Solved

SQL 2000 defrag index's

Posted on 2006-12-01
3
655 Views
Last Modified: 2008-03-06
I have a script to defrag some indexes however after building a list an a temp table it does the following to select which indexes to defrag:

SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= 25
     AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

This only returns one row but on inspection there are loads of indexes with a crap scan density, my question is this:

If i remove :

 AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

And just defrag all of the indexes with fragmentation > 25 will this cause any problems or will this work with out issue. In summary do I need to do the second check on the index properties and if I remove it am I safe to defrag all the indexes?

Cheers
0
Comment
Question by:Netstore
3 Comments
 
LVL 11

Accepted Solution

by:
lluthien earned 500 total points
ID: 18053044
if you remove the indexdepth check,
you will _not_ skip the systemtables.

so you'd have to figure out another away around that,

   WHERE LogicalFrag >= 25

are you sure you're checking the right fragmentation percentage?

this link has some background info:
http://www.sql-server-performance.com/tp_automatic_reindexing.asp
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now