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,
MarcHi 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...
>> 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
>|||Yes, but you were specifically talking about the SQL Server data/log file,
no?
> 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...
>> You have to take SQL offline if you wish to defrag the data\log file
>> with the builtin defrag.|||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.
=========================================================|||Hi Charles
Thank you for your information - this is really helpful.
I am still not sure, what I should do with my disks (post above). I'm sure
about the system disk c: - I will defrag it with defrag.exe.
But with the log drive and database drive I'm not sure what to do:
Should I run first I sql script that rebuilts all indexes and when this is
done run defrag.exe? Or should I do it the other way around, meaning run
defrag.exe and run the sql script for rebuild indexes? Or should I use only
the sql script or only defrag.exe?
Thanks for your help,
Kind regards,
Marc|||Hi Marc,
If the log drive and the database drive are only dedicated for your SQL
Server, you can just use SQL Server to rebuild/defragment the indexes (see
DBCC INDEXDEFRAG/DBCC DBREINDEX). You need not to run defrag.exe for the
two drives in this case. I recommend that you leave them for SQL Server. If
you still want to defragment them with defrag.exe, please first perform a
full database backup.
Please feel free to let me 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.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Thanks a lot Charles!
Best regards,
Marc|||>If the log drive and the database drive are only dedicated for your SQL
Server, you can just use SQL Server to rebuild/defragment the indexes (see
DBCC INDEXDEFRAG/DBCC DBREINDEX).
You could use the DMV sys.dm_db_index_physical_stats to dynamically rebuild
or reorganize your indexes based on the fragmentation. I have a stored
procedure that could help you with this.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html
Ola Hallengren
http://ola.hallengren.com
"Marc" wrote:
> Thanks a lot Charles!
> Best regards,
> Marc|||Thanks Ola!
Will use this code.
Best Regards,
Marc