| Author |
 Topic  |
|
|
aval
Honorable But Hopeless Addict
USA
2204 Posts
Status: offline |
 Posted - 04/30/2010 : 10:02:45 AM
|
Judging from this thread:
http://web2.minasi.com/forum/topic.asp?TOPIC_ID=18625&SearchTerms=defrag
It looks like I should take the db offline before defragging the hard drive.
Is that still the case (4 years later and with SQL 2008)?
Looking around elsewhere (Google here, Google there), some people (qualified or not, I do not know) said it was not necessary but that you would take a performance hit.
It seems that everytime we make the slightest change to the database (to the database, i.e. upgrade, not the data contained in it), the third-party software running on top of SQL needs to upgrade settings on the client machines, which requires admin rights and in the case of Vista, disabling UAC. That takes some time.
Hence my hesitation to change anything on the server end unless absolutely necessary (example, end of support for SQL 2000).
|
|
|
NMDANGE
Honorable But Hopeless Addict
USA
1703 Posts
Status: offline |
Posted - 04/30/2010 : 11:34:30 AM
|
There is not really any reason to defragment SQL Server database files. Since SQL is random IO, the file doesn't have to be contiguous, it won't make much difference performance wise.
Of course, if you have the SQL data files mixed on a drive with other files, then maybe defragmenting that drive might be needed. When the defragmenter is working on a SQL Server file, it will suspend all I/O to that file, so yes it is a good idea to take the SQL Server offline before running a defrag 
Edit: Ok I misread that as a File System defrag. You are actually talking about doing a reindex yes? When SQL is reindexing, it will cause a performance hit on whatever index it is working on - unless you have SQL Server Enterprise Edition, which supports online reindexing. You do not have to take the database offline, but you should schedule it to run off hours. |
Michael D'Angelo
MVP-MIIS, Pace University Accounts Administrator / DBA
(MS)NMDANGE
PhoeniX WorX Systems Administrator. If you play Total Annihilation, please join us. http://www.phoenixworx.org |
Edited by - NMDANGE on 04/30/2010 11:36:25 AM |
 |
|
|
aval
Honorable But Hopeless Addict
USA
2204 Posts
Status: offline |
Posted - 04/30/2010 : 11:59:57 AM
|
quote:
Ok I misread that as a File System defrag.
No, no! That's exactly what I mean. |
|
|
|
Rambler
Old Timer
Czech Republic
609 Posts
Status: offline |
Posted - 05/19/2010 : 10:08:52 AM
|
File system fragmentation of SQL databases can certainly be an issue, especially with many small databases, which have autogrowth enabled. Even worse when autogrowth is unconfigured (ie. left at default) or when autoshrink is enabled.
For example we had a 50GB partition with about 1000 smallish (~30MB) databases and the volume had 80% file system fragmentation.
So while I'm no expert on SQL, I still defrag the filesystem - going on by my common sense :) |
Edited by - Rambler on 05/19/2010 10:18:11 AM |
|
|
| |
Topic |
|
|
|
Defrag SQL server - take db offline?
