ok to defrag hard drive volumes that sql 2000 DBs reside on?

The volumes that host my SQL DB's are getting badly fragmented and I'm
wondering if it's OK to run windows (2003) built in defragmeting utility on
them to correct this? Thanks.
mb
Yes, however, it will not defrag the sql database files b/c sql server keeps
a lock on them. If you want to degragment the entire drive, you'll need to
shutdown sql server, or you can detach a particular database if you want to
only defragment select files. If this box is production you should make sure
you have a valid archive..
mike
"mb" wrote:

> The volumes that host my SQL DB's are getting badly fragmented and I'm
> wondering if it's OK to run windows (2003) built in defragmeting utility on
> them to correct this? Thanks.
> mb
|||Actually I think it will defrag it anyway. Or at least what it can. Just
make sure you backup first and be prepared for a performance hit if the
system is busy.
Andrew J. Kelly SQL MVP
"michael" <michael@.discussions.microsoft.com> wrote in message
news:0EA37353-147E-4F5A-B335-5B7E50FF7691@.microsoft.com...[vbcol=seagreen]
> Yes, however, it will not defrag the sql database files b/c sql server
> keeps
> a lock on them. If you want to degragment the entire drive, you'll need
> to
> shutdown sql server, or you can detach a particular database if you want
> to
> only defragment select files. If this box is production you should make
> sure
> you have a valid archive..
> mike
>
> "mb" wrote:
|||"Inside SQL server" ( MS Press ) says shutdown sql server or detach
databases then perfom the defrag ( as the files will be locked by sql )
what i personally do is the following , after my backup i perform necesary
maintenance on my database ( reindexing , shrinking ) then i perform the
defrag
as you will notice that reindexing and shrinking after a backup will result
in a smaller database size ( as this solves internall fragmentation ) this
will limit the time that defrag needs to perform its task and it reclaims
disk space
in my situation with a disk of + - 800 GB this takes + - 3 Hours
Dell poweredge 4600 , 2.8 dual XEON with 6 GB mem on Windows 2000
advanced , SQL 2000 enterprise ( with PAE and AWE extensions enabled )
I perform this task only when necessary ( if i see a hughe fragmentation )
in my personal opinion hughe fragmentation occurs because of bad
administration of a database
if you have a good start size of your db while creating it ( if you expect
it to grow in the near future to 3 gb create it with that amount of
diskspace already ) and a good autogrow strategy for the DB and log
then fragmentation is sized down to a minimum .
however it can never be 100% prevented
regards
Michel Posseth [MCP]
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OkLAKpWiFHA.1416@.TK2MSFTNGP09.phx.gbl...
> Actually I think it will defrag it anyway. Or at least what it can. Just
> make sure you backup first and be prepared for a performance hit if the
> system is busy.
> --
> Andrew J. Kelly SQL MVP
>
> "michael" <michael@.discussions.microsoft.com> wrote in message
> news:0EA37353-147E-4F5A-B335-5B7E50FF7691@.microsoft.com...
>
|||I have spoken directly with the makers of the Defrag software and they
assure me it can be done online with the current software. That said I
always recommend you shut down first as to speed the operation and minimize
any risks and performance issues. You should only have to defrag your files
at the OS level once. Once you defrag them they will not get fragmented at
the OS level again unless you constantly shrink and grow the files. You
should essentially never shrink your data files.

> what I personally do is the following , after my backup i perform
> necesary maintenance on my database ( reindexing , shrinking ) then i
> perform the defrag
By growing and later shrinking the files you cause fragmentation at the OS
level.

> as you will notice that reindexing and shrinking after a backup will
> result in a smaller database size ( as this solves internall
> fragmentation ) this will limit the time that defrag needs to perform its
> task and it reclaims disk space
Of coarse shrinking results in less overall space used but you totally
destroy the work you just did by reindexing. When you reindex an index sql
server will recreate the indexes (this is the data too for a clustered
index) in another area of the data file. This means you need at least 1.2
times the amount of the index size in free space in the file. If you don't
have it (which you wont since you keep shrinking the file) it will expand
the file. It then places the newly defragmented indexes at the end of the
file since that is where all the free space is. It then drops the original
indexes at the beginning of the file leaving free space. This is normal and
expected. But if you go and shrink the file you will move the indexes that
were all nicely built and contiguous at the end of the file to the beginning
since it has to shrink from the end of the file inwards. Well when it moves
the indexes it does it in small pieces and you usually end up with indexes
that are fragmented again since they will no longer be contiguous. All of
that data movement is fully logged as well.
So by shrinking your db you basically destroy all that hard work you did
earlier by defragging at the OS and internally with DBREINDEX.
TURN OFF THE SHRINK and life will be much better. Your db needs plenty of
free space to operate properly and there is no penalty for too much free
space.
Andrew J. Kelly SQL MVP
"m.posseth" <michelp@.nohausystems.nl> wrote in message
news:%231pcDPeiFHA.3288@.TK2MSFTNGP09.phx.gbl...
> "Inside SQL server" ( MS Press ) says shutdown sql server or detach
> databases then perfom the defrag ( as the files will be locked by sql )
> what i personally do is the following , after my backup i perform
> necesary maintenance on my database ( reindexing , shrinking ) then i
> perform the defrag
> as you will notice that reindexing and shrinking after a backup will
> result in a smaller database size ( as this solves internall
> fragmentation ) this will limit the time that defrag needs to perform its
> task and it reclaims disk space
> in my situation with a disk of + - 800 GB this takes + - 3 Hours
> Dell poweredge 4600 , 2.8 dual XEON with 6 GB mem on Windows 2000
> advanced , SQL 2000 enterprise ( with PAE and AWE extensions enabled )
> I perform this task only when necessary ( if i see a hughe
> ragmentation ) in my personal opinion hughe fragmentation occurs because
> of bad administration of a database
> if you have a good start size of your db while creating it ( if you expect
> it to grow in the near future to 3 gb create it with that amount of
> diskspace already ) and a good autogrow strategy for the DB and log
> then fragmentation is sized down to a minimum .
> however it can never be 100% prevented
> regards
> Michel Posseth [MCP]
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OkLAKpWiFHA.1416@.TK2MSFTNGP09.phx.gbl...
>