Disk fragmentation after backup

I got heavily disk fragmented after a full database backup. The disks
configured in RAID 10 and used to store just 1 full backup file daily. I
used the option "with init" on the backup statement. If you have experience
this or know the solution to this issue, please help. Thanks!
Relax. Is it really affecting performance? How do you know?
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>
|||Try deleting the file once just before you do the full backup. If the disk
does not have other files on it then the new file should be contiguous.
Andrew J. Kelly SQL MVP
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>

Disk fragmentation after backup

I got heavily disk fragmented after a full database backup. The disks
configured in RAID 10 and used to store just 1 full backup file daily. I
used the option "with init" on the backup statement. If you have experience
this or know the solution to this issue, please help. Thanks!Relax. Is it really affecting performance? How do you know?
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>|||Try deleting the file once just before you do the full backup. If the disk
does not have other files on it then the new file should be contiguous.
--
Andrew J. Kelly SQL MVP
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>

Disk fragmentation after backup

I got heavily disk fragmented after a full database backup. The disks
configured in RAID 10 and used to store just 1 full backup file daily. I
used the option "with init" on the backup statement. If you have experience
this or know the solution to this issue, please help. Thanks!Relax. Is it really affecting performance? How do you know?
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>|||Try deleting the file once just before you do the full backup. If the disk
does not have other files on it then the new file should be contiguous.
Andrew J. Kelly SQL MVP
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>

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 Raid 5

Can a disk failure on Raid 5 cause torn pages? It actually did cause
torn pages on several databases for us, so I am wondering if we have
some kind of configuration problem.
Thanks
A torn page basically reads the last two bits of a page that is written to
disk. If you have a hardware issue as the page is written to disk you always
have the potential to have part of the data written and part not. I think
anytime you have a disk failure you run the risk of a torn page.
Andrew J. Kelly SQL MVP
"Dave" <daveg.01@.gmail.com> wrote in message
news:1136847162.322252.52170@.o13g2000cwo.googlegro ups.com...
> Can a disk failure on Raid 5 cause torn pages? It actually did cause
> torn pages on several databases for us, so I am wondering if we have
> some kind of configuration problem.
> Thanks
>
|||Is there anything we could do to prevent this or is it just a fact of
life?
I guess I don't understand why the data page would not have been
written correctly to the redundant drive.
|||I'm no storage expert, but I would ask the storage vendor whether the RAID system fulfils Write
ordering and other aspects mentioned in
http://www.microsoft.com/technet/pro...basics.mspx.in case of a drive
failure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dave" <daveg.01@.gmail.com> wrote in message
news:1136912372.389395.67820@.g47g2000cwa.googlegro ups.com...
> Is there anything we could do to prevent this or is it just a fact of
> life?
> I guess I don't understand why the data page would not have been
> written correctly to the redundant drive.
>
|||Well a RAID 5 does not actually have a redundant drive. All the drives are
written to with a small piece of the data. One of the drives holds the
parity while the others each get a piece of the actual data. So if any one
piece is missing it can rebuild the data with the parity. But that does not
mean you can not get corruption on the write especially during a hardware
failure. I won't claim to know how the drive controllers work internally and
how they each do their stuff. So I am not sure other than ensuring you have
good name equipment and all in proper working order. Especially the UPS.
Andrew J. Kelly SQL MVP
"Dave" <daveg.01@.gmail.com> wrote in message
news:1136912372.389395.67820@.g47g2000cwa.googlegro ups.com...
> Is there anything we could do to prevent this or is it just a fact of
> life?
> I guess I don't understand why the data page would not have been
> written correctly to the redundant drive.
>
|||Guys, I guess I am just not getting it. I thought that RAID 5 was
redundant, meaning it should not be affected by a disk failure. The
data should be stored on 2 drives right? So how can you get torn page
errors when a disk fails?
Can someone please step me though a scenario of how data can be
corrupted with a RAID 5 disk failure?
|||No that is not how a RAID 5 works. If you have 4 drives in a RAID 5 you
will essentially split the data into 3 pieces. One of each of the pieces
will go onto 3 of the drives and a parity is calculated and placed on the
fourth drive. Each time you write to the drive array this is repeated but
the parity moves around so it is not always on the same drive. Under normal
conditions when you read the data the parity is not used and the whole data
block is created by piecing the three pieces back together. In the event of
a single disk failure the controller can read the two remaining good pieces
and using the parity recreate the third to get the data back. A Raid 5 does
not store the data twice. But even if it did that still does not prevent
torn pages. As I mentioned a torn page occurs when for some reason (usually
hardware related) the last two bits on a page did not get written properly
or at all. This can happen when the driver thinks it wrote the page
properly but the hardware didn't. A Raid 5 array does not claim to stop this
from occurring. That is why backups are so important. You can not protect
your data 100% with a Raid array.
Andrew J. Kelly SQL MVP
"Dave" <daveg.01@.gmail.com> wrote in message
news:1137002846.251710.290370@.g44g2000cwa.googlegr oups.com...
> Guys, I guess I am just not getting it. I thought that RAID 5 was
> redundant, meaning it should not be affected by a disk failure. The
> data should be stored on 2 drives right? So how can you get torn page
> errors when a disk fails?
> Can someone please step me though a scenario of how data can be
> corrupted with a RAID 5 disk failure?
>
|||Thanks, that helps a little.
I am still having a hard time grasping parity.
I will use Raid 3 for simplicity.
Disk 1: 00000000
Disk 2: 11111111
Disk 3: ??
On Raid 3, if Disk 3 stores parity data, what would it store? I
don't understand how one drive could store enough data to rebuild
Disk 1 or Disk 2.
|||This should explain Parity:
http://www.pcguide.com/ref/hdd/perf/...nParity-c.html
This shows how raid 3 (and others) use parity.
http://www.storagereview.com/guide20...gleLevel3.html
This tells you why you may want to consider something other than RAID 5.
http://www.baarf.com/
Andrew J. Kelly SQL MVP
"Dave" <daveg.01@.gmail.com> wrote in message
news:1137006115.234760.166690@.g44g2000cwa.googlegr oups.com...
> Thanks, that helps a little.
> I am still having a hard time grasping parity.
> I will use Raid 3 for simplicity.
> Disk 1: 00000000
> Disk 2: 11111111
> Disk 3: ??
> On Raid 3, if Disk 3 stores parity data, what would it store? I
> don't understand how one drive could store enough data to rebuild
> Disk 1 or Disk 2.
>
|||How would RAID 10 affect this scenerio?
sql

Disk Failure on Raid 5

Can a disk failure on Raid 5 cause torn pages? It actually did cause
torn pages on several databases for us, so I am wondering if we have
some kind of configuration problem.
ThanksA torn page basically reads the last two bits of a page that is written to
disk. If you have a hardware issue as the page is written to disk you always
have the potential to have part of the data written and part not. I think
anytime you have a disk failure you run the risk of a torn page.
--
Andrew J. Kelly SQL MVP
"Dave" <daveg.01@.gmail.com> wrote in message
news:1136847162.322252.52170@.o13g2000cwo.googlegroups.com...
> Can a disk failure on Raid 5 cause torn pages? It actually did cause
> torn pages on several databases for us, so I am wondering if we have
> some kind of configuration problem.
> Thanks
>|||Is there anything we could do to prevent this or is it just a fact of
life?
I guess I don't understand why the data page would not have been
written correctly to the redundant drive.|||I'm no storage expert, but I would ask the storage vendor whether the RAID system fulfils Write
ordering and other aspects mentioned in
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx.in case of a drive
failure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dave" <daveg.01@.gmail.com> wrote in message
news:1136912372.389395.67820@.g47g2000cwa.googlegroups.com...
> Is there anything we could do to prevent this or is it just a fact of
> life?
> I guess I don't understand why the data page would not have been
> written correctly to the redundant drive.
>|||Well a RAID 5 does not actually have a redundant drive. All the drives are
written to with a small piece of the data. One of the drives holds the
parity while the others each get a piece of the actual data. So if any one
piece is missing it can rebuild the data with the parity. But that does not
mean you can not get corruption on the write especially during a hardware
failure. I won't claim to know how the drive controllers work internally and
how they each do their stuff. So I am not sure other than ensuring you have
good name equipment and all in proper working order. Especially the UPS.
--
Andrew J. Kelly SQL MVP
"Dave" <daveg.01@.gmail.com> wrote in message
news:1136912372.389395.67820@.g47g2000cwa.googlegroups.com...
> Is there anything we could do to prevent this or is it just a fact of
> life?
> I guess I don't understand why the data page would not have been
> written correctly to the redundant drive.
>|||Guys, I guess I am just not getting it. I thought that RAID 5 was
redundant, meaning it should not be affected by a disk failure. The
data should be stored on 2 drives right? So how can you get torn page
errors when a disk fails?
Can someone please step me though a scenario of how data can be
corrupted with a RAID 5 disk failure?|||No that is not how a RAID 5 works. If you have 4 drives in a RAID 5 you
will essentially split the data into 3 pieces. One of each of the pieces
will go onto 3 of the drives and a parity is calculated and placed on the
fourth drive. Each time you write to the drive array this is repeated but
the parity moves around so it is not always on the same drive. Under normal
conditions when you read the data the parity is not used and the whole data
block is created by piecing the three pieces back together. In the event of
a single disk failure the controller can read the two remaining good pieces
and using the parity recreate the third to get the data back. A Raid 5 does
not store the data twice. But even if it did that still does not prevent
torn pages. As I mentioned a torn page occurs when for some reason (usually
hardware related) the last two bits on a page did not get written properly
or at all. This can happen when the driver thinks it wrote the page
properly but the hardware didn't. A Raid 5 array does not claim to stop this
from occurring. That is why backups are so important. You can not protect
your data 100% with a Raid array.
--
Andrew J. Kelly SQL MVP
"Dave" <daveg.01@.gmail.com> wrote in message
news:1137002846.251710.290370@.g44g2000cwa.googlegroups.com...
> Guys, I guess I am just not getting it. I thought that RAID 5 was
> redundant, meaning it should not be affected by a disk failure. The
> data should be stored on 2 drives right? So how can you get torn page
> errors when a disk fails?
> Can someone please step me though a scenario of how data can be
> corrupted with a RAID 5 disk failure?
>|||Thanks, that helps a little.
I am still having a hard time grasping parity.
I will use Raid 3 for simplicity.
Disk 1: 00000000
Disk 2: 11111111
Disk 3: ''
On Raid 3, if Disk 3 stores parity data, what would it store? I
don't understand how one drive could store enough data to rebuild
Disk 1 or Disk 2.|||This should explain Parity:
http://www.pcguide.com/ref/hdd/perf/raid/concepts/genParity-c.html
This shows how raid 3 (and others) use parity.
http://www.storagereview.com/guide2000/ref/hdd/perf/raid/levels/singleLevel3.html
This tells you why you may want to consider something other than RAID 5.
http://www.baarf.com/
Andrew J. Kelly SQL MVP
"Dave" <daveg.01@.gmail.com> wrote in message
news:1137006115.234760.166690@.g44g2000cwa.googlegroups.com...
> Thanks, that helps a little.
> I am still having a hard time grasping parity.
> I will use Raid 3 for simplicity.
> Disk 1: 00000000
> Disk 2: 11111111
> Disk 3: ''
> On Raid 3, if Disk 3 stores parity data, what would it store? I
> don't understand how one drive could store enough data to rebuild
> Disk 1 or Disk 2.
>|||How would RAID 10 affect this scenerio?|||I don't think it would matter what raid level it was. While Raid 10 does not
use parity it still writes to the disk and any write has the potential to
have a torn page.
--
Andrew J. Kelly SQL MVP
"JLA" <info@.jlaenterprises-dot-com.no-spam.invalid> wrote in message
news:43c5acee$0$17777$c3e8da3@.news.astraweb.com...
> How would RAID 10 affect this scenerio?
>|||Thanks! that was some good reading!
I think I am going to push for Raid 10. :-)

Disk Failure on Raid 5

Can a disk failure on Raid 5 cause torn pages? It actually did cause
torn pages on several databases for us, so I am wondering if we have
some kind of configuration problem.
ThanksA torn page basically reads the last two bits of a page that is written to
disk. If you have a hardware issue as the page is written to disk you always
have the potential to have part of the data written and part not. I think
anytime you have a disk failure you run the risk of a torn page.
Andrew J. Kelly SQL MVP
"Dave" <daveg.01@.gmail.com> wrote in message
news:1136847162.322252.52170@.o13g2000cwo.googlegroups.com...
> Can a disk failure on Raid 5 cause torn pages? It actually did cause
> torn pages on several databases for us, so I am wondering if we have
> some kind of configuration problem.
> Thanks
>|||Is there anything we could do to prevent this or is it just a fact of
life?
I guess I don't understand why the data page would not have been
written correctly to the redundant drive.|||I'm no storage expert, but I would ask the storage vendor whether the RAID s
ystem fulfils Write
ordering and other aspects mentioned in
http://www.microsoft.com/technet/pr...mspx.in
case of a drive
failure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dave" <daveg.01@.gmail.com> wrote in message
news:1136912372.389395.67820@.g47g2000cwa.googlegroups.com...
> Is there anything we could do to prevent this or is it just a fact of
> life?
> I guess I don't understand why the data page would not have been
> written correctly to the redundant drive.
>|||Well a RAID 5 does not actually have a redundant drive. All the drives are
written to with a small piece of the data. One of the drives holds the
parity while the others each get a piece of the actual data. So if any one
piece is missing it can rebuild the data with the parity. But that does not
mean you can not get corruption on the write especially during a hardware
failure. I won't claim to know how the drive controllers work internally and
how they each do their stuff. So I am not sure other than ensuring you have
good name equipment and all in proper working order. Especially the UPS.
Andrew J. Kelly SQL MVP
"Dave" <daveg.01@.gmail.com> wrote in message
news:1136912372.389395.67820@.g47g2000cwa.googlegroups.com...
> Is there anything we could do to prevent this or is it just a fact of
> life?
> I guess I don't understand why the data page would not have been
> written correctly to the redundant drive.
>|||Guys, I guess I am just not getting it. I thought that RAID 5 was
redundant, meaning it should not be affected by a disk failure. The
data should be stored on 2 drives right? So how can you get torn page
errors when a disk fails?
Can someone please step me though a scenario of how data can be
corrupted with a RAID 5 disk failure?|||No that is not how a RAID 5 works. If you have 4 drives in a RAID 5 you
will essentially split the data into 3 pieces. One of each of the pieces
will go onto 3 of the drives and a parity is calculated and placed on the
fourth drive. Each time you write to the drive array this is repeated but
the parity moves around so it is not always on the same drive. Under normal
conditions when you read the data the parity is not used and the whole data
block is created by piecing the three pieces back together. In the event of
a single disk failure the controller can read the two remaining good pieces
and using the parity recreate the third to get the data back. A Raid 5 does
not store the data twice. But even if it did that still does not prevent
torn pages. As I mentioned a torn page occurs when for some reason (usually
hardware related) the last two bits on a page did not get written properly
or at all. This can happen when the driver thinks it wrote the page
properly but the hardware didn't. A Raid 5 array does not claim to stop this
from occurring. That is why backups are so important. You can not protect
your data 100% with a Raid array.
Andrew J. Kelly SQL MVP
"Dave" <daveg.01@.gmail.com> wrote in message
news:1137002846.251710.290370@.g44g2000cwa.googlegroups.com...
> Guys, I guess I am just not getting it. I thought that RAID 5 was
> redundant, meaning it should not be affected by a disk failure. The
> data should be stored on 2 drives right? So how can you get torn page
> errors when a disk fails?
> Can someone please step me though a scenario of how data can be
> corrupted with a RAID 5 disk failure?
>|||Thanks, that helps a little.
I am still having a hard time grasping parity.
I will use Raid 3 for simplicity.
Disk 1: 00000000
Disk 2: 11111111
Disk 3: ''
On Raid 3, if Disk 3 stores parity data, what would it store? I
don't understand how one drive could store enough data to rebuild
Disk 1 or Disk 2.|||This should explain Parity:
http://www.pcguide.com/ref/hdd/perf...enParity-c.html
This shows how raid 3 (and others) use parity.
http://www.storagereview.com/guide2.../www.baarf.com/
Andrew J. Kelly SQL MVP
"Dave" <daveg.01@.gmail.com> wrote in message
news:1137006115.234760.166690@.g44g2000cwa.googlegroups.com...
> Thanks, that helps a little.
> I am still having a hard time grasping parity.
> I will use Raid 3 for simplicity.
> Disk 1: 00000000
> Disk 2: 11111111
> Disk 3: ''
> On Raid 3, if Disk 3 stores parity data, what would it store? I
> don't understand how one drive could store enough data to rebuild
> Disk 1 or Disk 2.
>|||How would RAID 10 affect this scenerio?

Disk configuration and optimization

Hi,
If I have a RAID 1 configuration and a RAID 5 configuration, what is the
optimal way to setup all my paging, data files, log files, temp files, etc.
for optimal SQL Server Performance?
DoOptimal means different things depending on what you are optimizing for, =but some suggestions:
Lose the raid 5 and make it 0+1 if you want best combination of speed =and resilience.
If performance matters more than resilience go for raid 0 for sql server =data files (don't panic the log is going elsewhere)
If resilience matters more than performance go for raid 1 for data =files.
Then put log on raid 1 (always)
System drive on raid 1
SQL server data files on 0+ 1, 0 or 1 depending on your meaning of =optimal.
How far you go with this then depends on many other factors - how many =spindles, controllers etc
Mike John
"Do" <doduong12141214@.hotmail.com> wrote in message =news:OEa$vSi1DHA.3220@.tk2msftngp13.phx.gbl...
> Hi,
> > If I have a RAID 1 configuration and a RAID 5 configuration, what is =the
> optimal way to setup all my paging, data files, log files, temp files, =etc.
> for optimal SQL Server Performance?
> > Do
> >|||So if I switch that RAID 5 to RAID 1 then I'll have two RAID 1
configurations on two separate controllers.
On my system RAID 1, I'll have my paging file and log files?
And on my other RAID 1, I'll have the data files?
So this means that I should keep my data and log files on separate disk
configurations?
Is it okay to put paging and log files on the same disk configuration?
Do
"Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
news:eYC2yli1DHA.2528@.TK2MSFTNGP10.phx.gbl...
Optimal means different things depending on what you are optimizing for, but
some suggestions:
Lose the raid 5 and make it 0+1 if you want best combination of speed and
resilience.
If performance matters more than resilience go for raid 0 for sql server
data files (don't panic the log is going elsewhere)
If resilience matters more than performance go for raid 1 for data files.
Then put log on raid 1 (always)
System drive on raid 1
SQL server data files on 0+ 1, 0 or 1 depending on your meaning of optimal.
How far you go with this then depends on many other factors - how many
spindles, controllers etc
Mike John
"Do" <doduong12141214@.hotmail.com> wrote in message
news:OEa$vSi1DHA.3220@.tk2msftngp13.phx.gbl...
> Hi,
> If I have a RAID 1 configuration and a RAID 5 configuration, what is the
> optimal way to setup all my paging, data files, log files, temp files,
etc.
> for optimal SQL Server Performance?
> Do
>|||I would always want to keep log and data on different discs both for =recovery and performance. You don't want to be paging much anyway so =access to pagefile should not be much. If tempdb use is very high then =moving that to yet another set of drives can help as well, but you need =to know a lot more about the workload to judge that.
Mike John
"Do" <doduong12141214@.hotmail.com> wrote in message =news:egC2cvi1DHA.2528@.TK2MSFTNGP09.phx.gbl...
> So if I switch that RAID 5 to RAID 1 then I'll have two RAID 1
> configurations on two separate controllers.
> > On my system RAID 1, I'll have my paging file and log files?
> And on my other RAID 1, I'll have the data files?
> > So this means that I should keep my data and log files on separate =disk
> configurations?
> Is it okay to put paging and log files on the same disk configuration?
> > Do
> > > "Mike John" <Mike.John@.knowledgepool.spamtrap.com> wrote in message
> news:eYC2yli1DHA.2528@.TK2MSFTNGP10.phx.gbl...
> Optimal means different things depending on what you are optimizing =for, but
> some suggestions:
> > Lose the raid 5 and make it 0+1 if you want best combination of speed =and
> resilience.
> > If performance matters more than resilience go for raid 0 for sql =server
> data files (don't panic the log is going elsewhere)
> If resilience matters more than performance go for raid 1 for data =files.
> > Then put log on raid 1 (always)
> System drive on raid 1
> SQL server data files on 0+ 1, 0 or 1 depending on your meaning of =optimal.
> > How far you go with this then depends on many other factors - how many
> spindles, controllers etc
> > Mike John
> > > "Do" <doduong12141214@.hotmail.com> wrote in message
> news:OEa$vSi1DHA.3220@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > If I have a RAID 1 configuration and a RAID 5 configuration, what =is the
> > optimal way to setup all my paging, data files, log files, temp =files,
> etc.
> > for optimal SQL Server Performance?
> >
> > Do
> >
> >
> >|||As mike says, using raid 10 gives the best performance, but you have to have
more hard drives... Most (non extrememe performance) folks I see simply Raid
5 the data and Raid 1 the log, and system drives.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Do" <doduong12141214@.hotmail.com> wrote in message
news:OEa$vSi1DHA.3220@.tk2msftngp13.phx.gbl...
> Hi,
> If I have a RAID 1 configuration and a RAID 5 configuration, what is the
> optimal way to setup all my paging, data files, log files, temp files,
etc.
> for optimal SQL Server Performance?
> Do
>

Disk Configuration

Are there any scripts or commands, or GUI interfaces that let you know if
your disk drives are configured for RAID 1, RAID 5, etc.? How is one to walk
into a shop and find this knowledge if the network admin is unavailable?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200604/1
Usually you can find out this type of information on the box. Compaq
servers that I have worked on have the Array Configuration Utility. I am
guessing that other vendors have their own version of a utility to
administer (or look at) an array from within Windows. You can also boot the
box and watch the BIOS screens as they appear (assuming that this is not a
production box and can be rebooted during the day).
Keith Kratochvil
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5e54ab7a96d03@.uwe...
> Are there any scripts or commands, or GUI interfaces that let you know if
> your disk drives are configured for RAID 1, RAID 5, etc.? How is one to
> walk
> into a shop and find this knowledge if the network admin is unavailable?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200604/1
sql

Disk Configuration

Are there any scripts or commands, or GUI interfaces that let you know if
your disk drives are configured for RAID 1, RAID 5, etc.? How is one to walk
into a shop and find this knowledge if the network admin is unavailable?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1Usually you can find out this type of information on the box. Compaq
servers that I have worked on have the Array Configuration Utility. I am
guessing that other vendors have their own version of a utility to
administer (or look at) an array from within Windows. You can also boot the
box and watch the BIOS screens as they appear (assuming that this is not a
production box and can be rebooted during the day).
--
Keith Kratochvil
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5e54ab7a96d03@.uwe...
> Are there any scripts or commands, or GUI interfaces that let you know if
> your disk drives are configured for RAID 1, RAID 5, etc.? How is one to
> walk
> into a shop and find this knowledge if the network admin is unavailable?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1

Disk Configuration

Are there any scripts or commands, or GUI interfaces that let you know if
your disk drives are configured for RAID 1, RAID 5, etc.? How is one to walk
into a shop and find this knowledge if the network admin is unavailable?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1Usually you can find out this type of information on the box. Compaq
servers that I have worked on have the Array Configuration Utility. I am
guessing that other vendors have their own version of a utility to
administer (or look at) an array from within Windows. You can also boot the
box and watch the BIOS screens as they appear (assuming that this is not a
production box and can be rebooted during the day).
Keith Kratochvil
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5e54ab7a96d03@.uwe...
> Are there any scripts or commands, or GUI interfaces that let you know if
> your disk drives are configured for RAID 1, RAID 5, etc.? How is one to
> walk
> into a shop and find this knowledge if the network admin is unavailable?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1

Disaster Recovery: SQL Server 2000 on Win 2000 Server.

This past Monday, our Raid 5 array failed on our SQL 2000 server (standard edition). Long story short, we were able to get the data back, mostly. In SQL server Ent. Mgr., I had to restore 4 databases that were 'Suspect' and "greyed out". No problems there... I was able to restore them.

What was interesting was that the SQL Server Agent would not start. As it turned out, a few files were either corrupt or missing after we got the machine back up. We had to restore a few files from tape backup and now the SQL Server Agent starts... but now I have a new problem.

For some reason, my default SQL Server Agent "Job" that is scheduled to run once per day fails. It appears that all of the databases get backed up, but for some reason, the msdbdata.mdf database is NOT being backed up.

I have a few questions. Could my msdbdata.mdf be corrupt? I have a successful backup of the msdbdata.mdf table from 9/10/2007. Can I just restore from that? Will there be any repercussions to any of the other database tables if I do?

Furthermore, should I just restore the msdbdata.mdf, master.mdf and the model.mdf? I really have no idea what these tables do and the repercussions of modifying them. I am not a DBA (obviously) and I just don't want to make an mistakes.

Thanks, CFDev.
Can you pls provide the details of the job history if any ? I guess you mean to say that msdb database backup fails if i interpret it correctly and Yes you can go ahead and restore the msdb.bak dated 9/10/2007 and check........the only issue is that all the jobs,dts packages created since 9/10 will not be available if you restore it.........I don't think its needed its enough if you restore msdb no need to restore master or model dbs..........
|||The "Job" is part of the databases default "maintenance plan". It consists of four parts:
1. DB backup
2. Integrity checks
3. Optimizations
4. Transaction Log Backup

This should be familiar to you, no? Anyway, would be okay to restore the msdb database then? How about the model and master databases? No tables, stored procedures, dts packages or anything has been changed. I just want peace of mind that if I were to restore any of these database tables, it would not effect any of my website's database tables. Make sense?

|||

Yes i am familiar with it if you feel there is no change done in msdb after 9/10 you can go ahead and restore msdb. Master stores all the login information and model acts as
template for other dbs so just restore msdb as it contains all the jobs and dts packages....I dont think restoring msdb would affect your db tables.......

|||

It would also be a good idea to run checkdb on all databases to make sure that they're clean.

You haven't made changes since 9/10, but you'd really rather not trip across hidden problems later.

Disaster Recovery: SQL Server 2000 on Win 2000 Server.

This past Monday, our Raid 5 array failed on our SQL 2000 server (standard edition). Long story short, we were able to get the data back, mostly. In SQL server Ent. Mgr., I had to restore 4 databases that were 'Suspect' and "greyed out". No problems there... I was able to restore them.

What was interesting was that the SQL Server Agent would not start. As it turned out, a few files were either corrupt or missing after we got the machine back up. We had to restore a few files from tape backup and now the SQL Server Agent starts... but now I have a new problem.

For some reason, my default SQL Server Agent "Job" that is scheduled to run once per day fails. It appears that all of the databases get backed up, but for some reason, the msdbdata.mdf database is NOT being backed up.

I have a few questions. Could my msdbdata.mdf be corrupt? I have a successful backup of the msdbdata.mdf table from 9/10/2007. Can I just restore from that? Will there be any repercussions to any of the other database tables if I do?

Furthermore, should I just restore the msdbdata.mdf, master.mdf and the model.mdf? I really have no idea what these tables do and the repercussions of modifying them. I am not a DBA (obviously) and I just don't want to make an mistakes.

Thanks, CFDev.
Can you pls provide the details of the job history if any ? I guess you mean to say that msdb database backup fails if i interpret it correctly and Yes you can go ahead and restore the msdb.bak dated 9/10/2007 and check........the only issue is that all the jobs,dts packages created since 9/10 will not be available if you restore it.........I don't think its needed its enough if you restore msdb no need to restore master or model dbs..........
|||The "Job" is part of the databases default "maintenance plan". It consists of four parts:
1. DB backup
2. Integrity checks
3. Optimizations
4. Transaction Log Backup

This should be familiar to you, no? Anyway, would be okay to restore the msdb database then? How about the model and master databases? No tables, stored procedures, dts packages or anything has been changed. I just want peace of mind that if I were to restore any of these database tables, it would not effect any of my website's database tables. Make sense?

|||

Yes i am familiar with it if you feel there is no change done in msdb after 9/10 you can go ahead and restore msdb. Master stores all the login information and model acts as
template for other dbs so just restore msdb as it contains all the jobs and dts packages....I dont think restoring msdb would affect your db tables.......

|||

It would also be a good idea to run checkdb on all databases to make sure that they're clean.

You haven't made changes since 9/10, but you'd really rather not trip across hidden problems later.

Disaster Recovery

Yikes! I lost my autoloader bakcup device along with my SQL2K Server. I reinstalled Win2k and now can see my RAID partitions where I had SQL server installed, along with all of the files. Since my tape backup is dead, I cannot simply restore from media. I'd like to reinstall SQL Server and then somehow grab the files on the partition and restore all of the data back to where things were. Is this even possible? If so, could someone help me out? I am worried by reading this list that my primary application (Sharepoint) will not be able to be restored because of security issues. Any ideas on that?

I appreciate any help you can provide.

-DenYup I would say disaster...

not so sure about the recovery part....

No tapes?|||You will probably be able to get the databases back with sp_attach_db (see BOL), but since this is Sharepoint, it is anyone's guess as to whether the databases will talk to anyone. Try to keep all the same server names for Sharepoint servers, since Sharepoint does not react well to changes of that sort.|||If you need to used SP_ATTACHDB then ensure those are properly detached with SP_DETACHDB. Using the last good known backup of SQL database is the best and use RESTORE to restore the data which will create the database.

To know the contents of that backup file you can use RESTORE FILELISTONLY as suggested in books online.

KBA http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;307775 about DR articles.
HTH