OK to delete sql2ksp3 folder after install?

I'm desperate for room on my "C" drive at the moment.
I've installed the SP3 for SQL Server 2000. Is it OK to remove the sql2ksp3
folder from my C drive now?
(and even though this is off topic, does anyone know how to increase the siz
e of the "C" partition on W2K AS?
I have 2 18GB drives in a RAID1 config. They are basic disks and are going
to stay that way. The system is
running in production mode, and between Dell, MSFT, and 2 partition tools I
cannot seem to find anyone who
can give me an authoritative 'yes' and how to do it. They all just want to
point to the other guys and have me
get the answer there.)
Thanks in advance for whatever help anyone can provide.
randyRandy,
Yes you should be able to remove that folder. As for increasing the
partition that's probably not an option. If it's a Raid 1 and you created
it initially with all the space in the primary partition you have no room to
grow. Can you be more specific as to where you need the room etc?
Andrew J. Kelly
SQL Server MVP
"randyvol" <randy.volters@.whaleyfoodservice.com> wrote in message
news:B5439337-990F-4542-8EC9-FF41701FDFFB@.microsoft.com...
> I'm desperate for room on my "C" drive at the moment.
> I've installed the SP3 for SQL Server 2000. Is it OK to remove the
sql2ksp3 folder from my C drive now?
> (and even though this is off topic, does anyone know how to increase the
size of the "C" partition on W2K AS?
> I have 2 18GB drives in a RAID1 config. They are basic disks and are
going to stay that way. The system is
> running in production mode, and between Dell, MSFT, and 2 partition tools
I cannot seem to find anyone who
> can give me an authoritative 'yes' and how to do it. They all just want
to point to the other guys and have me
> get the answer there.)
> Thanks in advance for whatever help anyone can provide.
> randy|||Hi Randy,
Thanks for using the newsgroup.
Yes, you could remove the sql2ksp3 folder because it is just a folder for
install files.
As for the question space of your Windows 2000 advanced server, we do have
related newsgroup and you could ask your question at:
http://communities2.microsoft.com/c...s/default.aspx?
dg=microsoft.public.win2000.advanced_server
OR
News:microsoft.public.win2000.advanced_server
Our related engineers there will help you with this issue.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks Andres and Baisong -
I'll check out the other group for my partition question.
Just to close it out here, I do have more room available. The two disks in
the RAID are both 18Giggers. So, I have
approx. 12 GB of unallocated space just beyond the initial 4GB "C" drive par
tition. Why not just create a "D" partition?
Well, we are holding out the possibility of rebuilding our cluster, and the
other server was just redone - however, this time
with a little more help from Dell using Server Asst. we got the full use of
the drive. So I'm trying to do this one the same.
At this point I'm pretty much resigned to having to blow this one away as so
on as we have the other server up.
Why do we need that big a "C" drive? Well besides having paid for the stora
ge and wanting to use it, believe it or
not there are still companies (like AVAYA) that build applications that MUST
be put in the C drive's programs folder.
ARRRRRRRGGGGGGGGGHHHHHHH. ;-)
Thanks again.|||Just FYI, it helps if you supply all the information to get correct
responses for your issue. You did not initially mention the drives only
had 4GB out of 18GB partitioned. That kind of information goes a long way.
Andrew J. Kelly
SQL Server MVP
"randyvol" <anonymous@.discussions.microsoft.com> wrote in message
news:5572D410-FF1B-43D9-BD01-EF04388DB162@.microsoft.com...
> Thanks Andres and Baisong -
> I'll check out the other group for my partition question.
> Just to close it out here, I do have more room available. The two disks
in the RAID are both 18Giggers. So, I have
> approx. 12 GB of unallocated space just beyond the initial 4GB "C" drive
partition. Why not just create a "D" partition?
> Well, we are holding out the possibility of rebuilding our cluster, and
the other server was just redone - however, this time
> with a little more help from Dell using Server Asst. we got the full use
of the drive. So I'm trying to do this one the same.
> At this point I'm pretty much resigned to having to blow this one away as
soon as we have the other server up.
> Why do we need that big a "C" drive? Well besides having paid for the
storage and wanting to use it, believe it or
> not there are still companies (like AVAYA) that build applications that
MUST be put in the C drive's programs folder.
> ARRRRRRRGGGGGGGGGHHHHHHH. ;-)
> Thanks again.|||Since you say that they are basic disks and not dynamic, Partition Magic
should be able to resize the 4 gig partitions.
David Walker

OK to delete sql2ksp3 folder after install?

I'm desperate for room on my "C" drive at the moment
I've installed the SP3 for SQL Server 2000. Is it OK to remove the sql2ksp3 folder from my C drive now
(and even though this is off topic, does anyone know how to increase the size of the "C" partition on W2K AS
I have 2 18GB drives in a RAID1 config. They are basic disks and are going to stay that way. The system i
running in production mode, and between Dell, MSFT, and 2 partition tools I cannot seem to find anyone wh
can give me an authoritative 'yes' and how to do it. They all just want to point to the other guys and have m
get the answer there.
Thanks in advance for whatever help anyone can provide
randyRandy,
Yes you should be able to remove that folder. As for increasing the
partition that's probably not an option. If it's a Raid 1 and you created
it initially with all the space in the primary partition you have no room to
grow. Can you be more specific as to where you need the room etc?
--
Andrew J. Kelly
SQL Server MVP
"randyvol" <randy.volters@.whaleyfoodservice.com> wrote in message
news:B5439337-990F-4542-8EC9-FF41701FDFFB@.microsoft.com...
> I'm desperate for room on my "C" drive at the moment.
> I've installed the SP3 for SQL Server 2000. Is it OK to remove the
sql2ksp3 folder from my C drive now?
> (and even though this is off topic, does anyone know how to increase the
size of the "C" partition on W2K AS?
> I have 2 18GB drives in a RAID1 config. They are basic disks and are
going to stay that way. The system is
> running in production mode, and between Dell, MSFT, and 2 partition tools
I cannot seem to find anyone who
> can give me an authoritative 'yes' and how to do it. They all just want
to point to the other guys and have me
> get the answer there.)
> Thanks in advance for whatever help anyone can provide.
> randy|||Hi Randy,
Thanks for using the newsgroup.
Yes, you could remove the sql2ksp3 folder because it is just a folder for
install files.
As for the question space of your windows 2000 advanced server, we do have
related newsgroup and you could ask your question at:
http://communities2.microsoft.com/communities/newsgroups/en-us/default.aspx?
dg=microsoft.public.win2000.advanced_server
OR
News:microsoft.public.win2000.advanced_server
Our related engineers there will help you with this issue.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks Andres and Baisong -
I'll check out the other group for my partition question
Just to close it out here, I do have more room available. The two disks in the RAID are both 18Giggers. So, I have
approx. 12 GB of unallocated space just beyond the initial 4GB "C" drive partition. Why not just create a "D" partition
Well, we are holding out the possibility of rebuilding our cluster, and the other server was just redone - however, this time
with a little more help from Dell using Server Asst. we got the full use of the drive. So I'm trying to do this one the same
At this point I'm pretty much resigned to having to blow this one away as soon as we have the other server up
Why do we need that big a "C" drive? Well besides having paid for the storage and wanting to use it, believe it o
not there are still companies (like AVAYA) that build applications that MUST be put in the C drive's programs folder
ARRRRRRRGGGGGGGGGHHHHHHH. ;-
Thanks again.|||Just FYI, it helps if you supply all the information to get correct
responses for your issue. You did not initially mention the drives only
had 4GB out of 18GB partitioned. That kind of information goes a long way.
--
Andrew J. Kelly
SQL Server MVP
"randyvol" <anonymous@.discussions.microsoft.com> wrote in message
news:5572D410-FF1B-43D9-BD01-EF04388DB162@.microsoft.com...
> Thanks Andres and Baisong -
> I'll check out the other group for my partition question.
> Just to close it out here, I do have more room available. The two disks
in the RAID are both 18Giggers. So, I have
> approx. 12 GB of unallocated space just beyond the initial 4GB "C" drive
partition. Why not just create a "D" partition?
> Well, we are holding out the possibility of rebuilding our cluster, and
the other server was just redone - however, this time
> with a little more help from Dell using Server Asst. we got the full use
of the drive. So I'm trying to do this one the same.
> At this point I'm pretty much resigned to having to blow this one away as
soon as we have the other server up.
> Why do we need that big a "C" drive? Well besides having paid for the
storage and wanting to use it, believe it or
> not there are still companies (like AVAYA) that build applications that
MUST be put in the C drive's programs folder.
> ARRRRRRRGGGGGGGGGHHHHHHH. ;-)
> Thanks again.|||Since you say that they are basic disks and not dynamic, Partition Magic
should be able to resize the 4 gig partitions.
David Walker

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.
mbYes, 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...
> 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|||"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...
>> 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
>|||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...
>> 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...
>> 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
>>
>

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...
>

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.
mbYes, however, it will not defrag the sql database files b/c sql server keep
s
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 sur
e
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 o
n
> 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...
>