We help IT Professionals succeed at work.

Windows defrag on SQL Server 2005 machine?

reddal
reddal asked
on
Medium Priority
313 Views
Last Modified: 2012-05-06
Hi,

Is it necessary / recommended to run Windows defrag on servers primarily used for SQL Server 2005?

I have a server (Windows 2003 x64) with quite a lot of data on it split over several volumes - which have never been defragmented. Most of the data is SQL Server 2005 databases - but some is files/documents.

Should I run a windows defrag on the volumes? If so is it necessary to do this with the databases offline?

thanks - reddal
Comment
Watch Question

Commented:
Databases do become not contiguous over time, and for that reason I run contig.exe which does a single file defrag and will run online but seems to slow things up a bit so it runs during off peak hours weekly via .bat files and Task Scheduler.  

Contig.exe can be downloaded from Microsoft for free at sysinternals:

http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx

Regards,

Jim
CERTIFIED EXPERT

Commented:
Instead of contig i would just defrag the whole drive but I wouldnt do it frequently unless the database frequently grows and shrinks creating a lot of fragmentation. I would also recommend Running a defrag in SQL Server because daabases have their own kind of defragmentation.

Commented:
Actually there are two kinds of fragmentation in SQL server files. And Defrag won't necessarily make a file contiguous.
Commented:
An initial single file defrag using contig.exe, followed by a rebuild of all clustered indexes should get you database in a fairly optimum condition storage wise, but this wouldn't be a complete defrag of the data within the database file though.. Having the database offline would be advisable..

You can get a list of all clustered indexes with the following command -

SELECT Name FROM sysindexes where indid = 1

Use this list in a cursor and use the DBCC DBREINDEX command to rebuild the clustered indexes.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.