Solved

SQL 2000 defrag index's

Posted on 2006-12-01
3
662 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query 5 44
access to sql migration 5 25
Need quicker response from an Execption table 11 24
sql server major issue  need help 2 51
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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

730 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