Defrag Production SQL Box

Dear all
I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
64bit) that is heavily fragmented. This is the result from the built in
defrag.exe.
To defrag a machine like this, you have published some best practices,
right? Can you provide me with a link or could you provide me with the
appropriate steps I have to take?
Thanks in advance and kind regards,
Marc
Hi Marc,
You have to take SQL offline if you wish to defrag the data\log file with
the builtin defrag. There are 3rd party utilitized that say they can do it
online but I would make sure the backups are verified. Start off with larger
data files moving forward so it does not autogrow.
Jason Massie
http://statisticsio.com
http://twitter.com/statisticsio
http://friendfeed.com/statisticsio
http://feeds.feedburner.com/statisticsio
"Marc" <Rombooth1@.community.nospam> wrote in message
news:9911B546-137C-40C2-90A5-8528AA05B614@.microsoft.com...
> Dear all
> I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
> 64bit) that is heavily fragmented. This is the result from the built in
> defrag.exe.
> To defrag a machine like this, you have published some best practices,
> right? Can you provide me with a link or could you provide me with the
> appropriate steps I have to take?
> Thanks in advance and kind regards,
> Marc
>
>
|||> You have to take SQL offline if you wish to defrag the data\log file with
> the builtin defrag.
And you shouldn't do that anyway. I would configure the defragging tool to
not look at the data/log file folders, and rely on SQL Server's own
mechanisms to determine/repair fragmentation. Fragmentation within a
database is not quite the same thing as fragmentation in a file system...
they are similar, but they are not resolved in the same way. And windows or
diskeeper cannot alert you when you should defragment a table/index, but
they could be misleading you because they think your database file is
fragmented. Frankly, it's none of their business.
A
|||The OP is talking about file fragementation.
Jason Massie
http://statisticsio.com
http://twitter.com/statisticsio
http://friendfeed.com/statisticsio
http://feeds.feedburner.com/statisticsio
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:F8C822CD-EED4-49A2-B3DE-3B8ED255B6C6@.microsoft.com...
> And you shouldn't do that anyway. I would configure the defragging tool
> to not look at the data/log file folders, and rely on SQL Server's own
> mechanisms to determine/repair fragmentation. Fragmentation within a
> database is not quite the same thing as fragmentation in a file system...
> they are similar, but they are not resolved in the same way. And windows
> or diskeeper cannot alert you when you should defragment a table/index,
> but they could be misleading you because they think your database file is
> fragmented. Frankly, it's none of their business.
> A
>
|||Yes, but you were specifically talking about the SQL Server data/log file,
no?
[vbcol=seagreen]
> The OP is talking about file fragementation.
> --
> Jason Massie
> http://statisticsio.com
> http://twitter.com/statisticsio
> http://friendfeed.com/statisticsio
> http://feeds.feedburner.com/statisticsio
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:F8C822CD-EED4-49A2-B3DE-3B8ED255B6C6@.microsoft.com...
|||Depending on what kind of storage you are using, file system fragmentation
may or may not have much of a performance impact on your SQL workloads. I
know the file system defrag tool vendors are out there scaring people. You
have to take their ads with a huge grain of salt.
The point is that if you can afford to run some tests, I'd suggest you do
that first before you commit to some massive file system degrag operations.
Linchi
"Marc" wrote:

> Dear all
> I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
> 64bit) that is heavily fragmented. This is the result from the built in
> defrag.exe.
> To defrag a machine like this, you have published some best practices,
> right? Can you provide me with a link or could you provide me with the
> appropriate steps I have to take?
> Thanks in advance and kind regards,
> Marc
>
>
|||Thanks a lot for your comments. I should be more precise here. The file
system on the SQL Box is configured like this:
C: operating system, sql server and reporting services
E: Logs
F: Relational and analytical DB of SQL Server 2005
I ran the analysis part of defrag.exe on each drive. All are heavily
fragmented. I regurlary defragment the indexes on the relational db. The
analytical db gets calculated every night.
I’m considering defragmenting only the c drive and ignoring e and f drive.
Would you agree?
Kind regards,
Marc
|||"I regurlary defragment the indexes on the relational db"
I do this inside management studio.
Regards,
Marc
|||Hi Linchi

> Depending on what kind of storage you are using,
I'm using HDs in a Raid 1 configuration for the operating system and sql
server and a Raid 1 for the log. The SQL DB's (relational and analytical) run
on a Raid 10.
Marc
|||Hi Marc,
I totally agree with Aaron's suggestions. Please separate your file system
defragmentation from your SQL Server defragmentation. You may refer to the
section "Defragmentation as a Part of Your Infrastructure" in this article:
The Desktop Files The Truth about Defragmentation
http://technet.microsoft.com/en-us/magazine/cc137767.aspx
Also when you schedule a file system defragmentation or SQL Server
defragmentation, you should also consider performance impact. Please
arrange a not busy time for this job.
Please feel free to let us know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#
notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are
best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no
rights.
================================================== =======