SQL 2005 cleanup of databases

Posted on 2012-08-13
Last Modified: 2012-09-22
  I have a client that has some SQL 2005 databases.  They are severly fragmented.  I'm no SQL DB so I'm not exactly sure how the best way would be in cleaning them up.  Is it possible to do the rebuild and index through a scheduled job?  I tried creating one, but it failed.  Any help would be great!
Question by:sidelogic
    LVL 75

    Accepted Solution

    I suggest you start here:
    SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

    This script will reindex/reorganize the tables that are fragmented.

    Author Comment

    I don't know enough about sql to know how to run that .sql script.  I guess we better just leave it alone if that is the only option out there..
    LVL 9

    Assisted Solution


    have a look with below links..First take a full backup before procedding if anything goes wrong you can restore it back...
    LVL 24

    Assisted Solution

    by:DBAduck - Ben Miller
    You can find out which indexes to rebuild or reorganize by using

    SELECT OBJECT_NAME(ix.object_id) as table_name, as index_name,
    FROM sys.indexes ix
    inner join sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') P
             ON ix.object_id = P.object_id and ix.index_id = P.index_id

    This will tell you the fragmentation of the indexes on that database.  You can then check to see which ones have > 10000 pages with the page_count column and there are recommendations all the way from < 15 % REORGANIZE, and > 30% and > 10000 pages, REBUILD.

    Really this is related to how large the database is and which indexes are the most used and are highly volatile, as well as the idea of if there is no fillfactor on the index, you probably should have one if you REBUILD the index instead of REORGANIZE.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    728 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

    21 Experts available now in Live!

    Get 1:1 Help Now