SQL 2005 cleanup of databases

Hello,
  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!
sidelogicIT ManagerAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
I suggest you start here:
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance
http://ola.hallengren.com/

This script will reindex/reorganize the tables that are fragmented.
0
 
sidelogicIT ManagerAuthor Commented:
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..
0
 
keyuConnect With a Mentor Commented:
hi,

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

http://www.sqlmag.com/article/stored-procedures/clean-up-your-sql-server-databases

http://msdn.microsoft.com/en-us/library/dd408732.aspx
0
 
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
You can find out which indexes to rebuild or reorganize by using

SELECT OBJECT_NAME(ix.object_id) as table_name,
ix.name as index_name,
P.*
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.