Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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!
0
sidelogic
Asked:
sidelogic
3 Solutions
 
Anthony PerkinsCommented:
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
 
keyuCommented:
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 MillerPrincipal 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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now