[Webinar] Streamline your web hosting managementRegister Today

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

Windows defrag on SQL Server 2005 machine?

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
0
reddal
Asked:
reddal
  • 2
1 Solution
 
jmoss111Commented:
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
0
 
OriNetworksCommented:
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.
0
 
jmoss111Commented:
Actually there are two kinds of fragmentation in SQL server files. And Defrag won't necessarily make a file contiguous.
0
 
reb73Commented:
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.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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