disk fragmentation

I have a Dell PowerEdge 2850 with Windows 2003 server. 2 disk drives with RAID. My databases are fragmented on the drive and I need to defrag the drive at least once a week. My database files are fragmented. Defragger cleans up the files but as soon as I start running processes again, the files fragment and system response time suffers.

What can I do to keep the files from fragmenting?

calculate the database growth

and set the file increment in the db option

bigger than that. i meann just a little bigger

|||

Question : does file fragmentation really impact the performance of a DB ?

I mean : by construction, the data in files are fragmented, and access to data in DB always require a lot of disk seek. Sequential access through files should be marginal. So, fragmentation of the whole file should not imply a big performance impact.

Am I correct ?

|||

How are you measuring the fragmentation?

There are two types of fragmentation here:

1) The normal filesystem fragmentation of the database files. This should have minimal to nil impact unless you do a lot of table-scans. On the other hand, you shouldn't be getting this sort of fragmentation unless you are constantly growing your database. Far better to size it for growth initially than to allow it to grow incrementally.

2) Internal fragmentation of the information within the database files (as measured by commands like DBCC SHOWCONTIG). THis has nothing to do with the physical files being fragmented on disk, and is resolved by using SQL techniques such as DBCC INDEXDEFRAG or ALTER INDEX REBUILD.

disk failure on cluster array

We experienced a disk failure on the cluster array. It's a
active/passive SQL cluster. I was told by dell that we must powerdown
the passive before replacing the failed drive because otherwise both
servers will attempt to rebuild the array. Is this true? Can you
simply swap out the bad drive or will I need to power down the passave
node?
Dear Icemon,
It seems a bit much to me and I would guess that modern disk arrays (both
direct SCSI attach as well as any SAN implementation) would do a rebuild of
the RAID on the Array, with the host connected and online.
However my advice is to follow your vendors instructions.
I know this is probably not the answer you were hoping for, but it all
depends on your hardware vendor to provide support for replacing a hard
drive.
good luck and
rgds,
Edwin.
"icemon" <johnsitu@.gmail.com> wrote in message
news:1165899856.732197.284880@.79g2000cws.googlegro ups.com...
> We experienced a disk failure on the cluster array. It's a
> active/passive SQL cluster. I was told by dell that we must powerdown
> the passive before replacing the failed drive because otherwise both
> servers will attempt to rebuild the array. Is this true? Can you
> simply swap out the bad drive or will I need to power down the passave
> node?
>
|||"icemon" <johnsitu@.gmail.com> wrote in message
news:1165899856.732197.284880@.79g2000cws.googlegro ups.com...
> We experienced a disk failure on the cluster array. It's a
> active/passive SQL cluster. I was told by dell that we must powerdown
> the passive before replacing the failed drive because otherwise both
> servers will attempt to rebuild the array. Is this true? Can you
> simply swap out the bad drive or will I need to power down the passave
> node?
It really depends on the array and the controllers. Do you have PERCs in
each node and are the drives all SCSI attached? If so, then Dell seems to be
leading you the right direction.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
Denver starting Feb 12th
NYC starting Feb 19th
|||Let me guess? One of the Powervault 200/220 series arrays with PERC cards
in each host? Dell is correct. Unfortunately, SCSI arrays with no internal
controllers have a lot of limitations. You just found one the hard way.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"icemon" <johnsitu@.gmail.com> wrote in message
news:1165899856.732197.284880@.79g2000cws.googlegro ups.com...
> We experienced a disk failure on the cluster array. It's a
> active/passive SQL cluster. I was told by dell that we must powerdown
> the passive before replacing the failed drive because otherwise both
> servers will attempt to rebuild the array. Is this true? Can you
> simply swap out the bad drive or will I need to power down the passave
> node?
>
|||Since I didn't get any immiedate responses on this post, I decided to
call Dell once more. I spoke to another engineer who advised me
differently. Shouldn't they be more in sync with their knowledge base?
Anyway, this guy told me that it is not necessary since it's an
Active/Passive Node. He said that Since the Passive Node is not
running, it will not compete. I swap out the array and the drive is
rebuilt. It is running a PERC 3 on a powervault 220 array.
Geoff N. Hiten wrote:[vbcol=seagreen]
> Let me guess? One of the Powervault 200/220 series arrays with PERC cards
> in each host? Dell is correct. Unfortunately, SCSI arrays with no internal
> controllers have a lot of limitations. You just found one the hard way.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "icemon" <johnsitu@.gmail.com> wrote in message
> news:1165899856.732197.284880@.79g2000cws.googlegro ups.com...
|||I am not completely surprised either way. Level 1 tech support from Dell
isn't the most accurate in the universe, even on the Gold Support Queue. And
if you are running a cluster without at least Gold support, then you are
crazier than I am. The escalation techs are pretty good and the TAMs can
make things happen so I shouldn't be too harsh on Dell. I do wish they
would update their SCSI storage systems a bit, though.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"icemon" <johnsitu@.gmail.com> wrote in message
news:1165978456.551049.281650@.f1g2000cwa.googlegro ups.com...
> Since I didn't get any immiedate responses on this post, I decided to
> call Dell once more. I spoke to another engineer who advised me
> differently. Shouldn't they be more in sync with their knowledge base?
> Anyway, this guy told me that it is not necessary since it's an
> Active/Passive Node. He said that Since the Passive Node is not
> running, it will not compete. I swap out the array and the drive is
> rebuilt. It is running a PERC 3 on a powervault 220 array.
>
> Geoff N. Hiten wrote:
>