Solved

defragging our SQL server

Posted on 2013-05-30
6
437 Views
Last Modified: 2013-05-31
I run tasks on our SQL database to rebuild indexes periodically, but it just dawned on me that I never run the Windows Defragging utility on the actual server.  I am using Windows 2003 server.  

If I use this utility regularly, would it make a significant difference in the performance of the SQL database?  Are there any dangers to using it?  The database is only about 8GB in size
0
Comment
Question by:al4629740
  • 3
  • 2
6 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 39208732
the Windows defragger does not actually impact the SQL fragmentation as the defragger has no knowledge of SQL data storage.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39208787
There's no real need.  I can imaging a case where the databases have been set up horribly (small auto-grows and auto-shrink turned on - ugh!) and the way they grew was oddly spread out so that disk fragmentation occurred, but it is very unlikely.

Have you run disk defrag on your DATA and/or LOG drives and just done an analysis to see how fragmented they were?

My gut says don't defrag.  *IF* your fragmentation is really bad, say 40% or higher, then I would think about it.  You can run disk de-frag anytime, even on open files, but performance goes to the crapper while its running.
0
 

Author Comment

by:al4629740
ID: 39209861
How do I run defrag just on the Data?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 250 total points
ID: 39209871
Most DBAs store their DATA and LOG files on separate drives.  Your asking of the question makes me think this is not the case for you (and that you have both on the same drive, which is bad).  You can only defrag an entire drive (which I'm thinking you already know).
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 250 total points
ID: 39210222
>>How do I run defrag just on the Data?

you just need to reindex and the data will be defragged.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 39211486
Thanks for the points
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now