multiple datafiles- on multiple drives on One raid set

Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?
Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?
|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:

> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> (>
>
>
|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...[vbcol=seagreen]
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
separate[vbcol=seagreen]
multiple[vbcol=seagreen]
database[vbcol=seagreen]
|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?

multiple datafiles- on multiple drives on One raid set

Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:
> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> > Is there an advantage (besides backup and restore) of creating multiple
> > datafiles on multiple disks on one physical raid set for a large database
> (>
> > 200GB) ?
>
>|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
> > Geert
> > Performance for example if you query a huge table /s that are on
separate
> > physical discks SQL Server will create two threads to retrieve the data
> > which means less I/O .
> >
> >
> > "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> > news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> > > Is there an advantage (besides backup and restore) of creating
multiple
> > > datafiles on multiple disks on one physical raid set for a large
database
> > (>
> > > 200GB) ?
> >
> >
> >|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
--
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?

multiple datafiles- on multiple drives on One raid set

Is there an advantage (besides backup and restore) of creating multiple
datafiles on multiple disks on one physical raid set for a large database (>
200GB) ?Geert
Performance for example if you query a huge table /s that are on separate
physical discks SQL Server will create two threads to retrieve the data
which means less I/O .
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?|||Is this also true with on physical raid set with two partitions on it?
We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
distributed over all physical disks (so there are many spindels)
"Uri Dimant" wrote:

> Geert
> Performance for example if you query a huge table /s that are on separate
> physical discks SQL Server will create two threads to retrieve the data
> which means less I/O .
>
> "GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
> news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> (>
>
>|||Hi
Well , if your database is heavy inserted I afraid a raid 5 is not good idea
,because it needs to maintain an additional disk strip.
For reading ,yes you will get performance benefit.
"geertVDB" <geertVDB@.discussions.microsoft.com> wrote in message
news:92F69BA3-B897-4221-83EA-F48A9EDBBEC6@.microsoft.com...[vbcol=seagreen]
> Is this also true with on physical raid set with two partitions on it?
> We have a HP EVA3000 with virtual raid sets. So all virtual raid 5 set are
> distributed over all physical disks (so there are many spindels)
> "Uri Dimant" wrote:
>
separate[vbcol=seagreen]
multiple[vbcol=seagreen]
database[vbcol=seagreen]|||In general, no. However, sometimes you may see a performance gain when you
have a lot of concurrent update activity in one database. This is especially
true for tempdb. More files in general means more management overhead.
Sometimes it is better to create multiple file groups for large database to
improve manageability.
Wei Xiao[MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeertVdb" <GeertVdb@.discussions.microsoft.com> wrote in message
news:C52EB00B-A816-481E-8B5B-A09D0A93ABC8@.microsoft.com...
> Is there an advantage (besides backup and restore) of creating multiple
> datafiles on multiple disks on one physical raid set for a large database
(>
> 200GB) ?

Multiple Data & Log Drives

I have a 20g database, the data and log files are spread
across multiple drives. What is the best way to get the
data back to 1 drive and the log back to 1 drive ?Use
Dbcc shrinkfile(Friendlyfilename, null, emptyfile) on a file
then
Alter database prod drop file friendlyfilename
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
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:05a401c3ce0d$9aafb910$a601280a@.phx.gbl...
> I have a 20g database, the data and log files are spread
> across multiple drives. What is the best way to get the
> data back to 1 drive and the log back to 1 drive ?|||Let me be more specific:
My database consists of 2 data files and 2 log files:
e:\data1.mdf
f:\data2.mdf
g:\log1.ldf
f:\log2.ldf
How can I get the data back to 1 file and 1 drive ?
How can I get the log bacl to 1 file and 1 drice.
>--Original Message--
>I have a 20g database, the data and log files are spread
>across multiple drives. What is the best way to get the
>data back to 1 drive and the log back to 1 drive ?
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_009F_01C3CDEB.FAF90BE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
For the log files, shrink the second log file with DBCC SHRINKFILE and use
the EMPTYFILE option. Next, alter the database to drop the now-empty file.
As for the data files, you did not say if they are in the same filegroup.
If so, proceed as above. If not, you will have to drop the indexes in the
second file and re-create them in the primary file. Then, proceed as above.
HTH
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
Let me be more specific:
My database consists of 2 data files and 2 log files:
e:\data1.mdf
f:\data2.mdf
g:\log1.ldf
f:\log2.ldf
How can I get the data back to 1 file and 1 drive ?
How can I get the log bacl to 1 file and 1 drice.
>--Original Message--
>I have a 20g database, the data and log files are spread
>across multiple drives. What is the best way to get the
>data back to 1 drive and the log back to 1 drive ?
>.
>
--=_NextPart_000_009F_01C3CDEB.FAF90BE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

For the log files, shrink the second =log file with DBCC SHRINKFILE and use the EMPTYFILE option. Next, alter the =database to drop the now-empty file.
As for the data files, you did not say =if they are in the same filegroup. If so, proceed as above. If not, you =will have to drop the indexes in the second file and re-create them in the =primary file. Then, proceed as above.
HTH
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kelly" wrote in message news:060001c3ce13$e0=c5dd70$a601280a@.phx.gbl...Let me be more specific:My database consists of 2 data files and 2 =log files:e:\data1.mdff:\data2.mdfg:\log1.ldff:\log2.=ldfHow can I get the data back to 1 file and 1 drive ? How can I get =the log bacl to 1 file and 1 drice.>--Original =Message-->I have a 20g database, the data and log files are spread >across =multiple drives. What is the best way to get the >data back to 1 drive and =the log back to 1 drive ?>.>

---

--=_NextPart_000_009F_01C3CDEB.FAF90BE0--|||Yes they are in the primary file group. My problem is that
I need to move the primary data and log files. Those are
the drives that are running out of space.
>--Original Message--
>For the log files, shrink the second log file with DBCC
SHRINKFILE and use
>the EMPTYFILE option. Next, alter the database to drop
the now-empty file.
>As for the data files, you did not say if they are in the
same filegroup.
>If so, proceed as above. If not, you will have to drop
the indexes in the
>second file and re-create them in the primary file.
Then, proceed as above.
>HTH
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Kelly" <anonymous@.discussions.microsoft.com> wrote in
message
>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
>Let me be more specific:
>My database consists of 2 data files and 2 log files:
>e:\data1.mdf
>f:\data2.mdf
>g:\log1.ldf
>f:\log2.ldf
>How can I get the data back to 1 file and 1 drive ?
>How can I get the log bacl to 1 file and 1 drice.
>>--Original Message--
>>I have a 20g database, the data and log files are spread
>>across multiple drives. What is the best way to get the
>>data back to 1 drive and the log back to 1 drive ?
>>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_00F8_01C3CDF1.9708E1E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
OK, moving files is different. What you can do is detach and reattach the
database:
1. Detach the database, using sp_detach_db
2. Move the files to a different drive
3. Reattach the database, using sp_attach_db
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Kelly" <anonymous@.discussions.microsoft.com> wrote in message
news:062b01c3ce17$192e4be0$a601280a@.phx.gbl...
Yes they are in the primary file group. My problem is that
I need to move the primary data and log files. Those are
the drives that are running out of space.
>--Original Message--
>For the log files, shrink the second log file with DBCC
SHRINKFILE and use
>the EMPTYFILE option. Next, alter the database to drop
the now-empty file.
>As for the data files, you did not say if they are in the
same filegroup.
>If so, proceed as above. If not, you will have to drop
the indexes in the
>second file and re-create them in the primary file.
Then, proceed as above.
>HTH
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Kelly" <anonymous@.discussions.microsoft.com> wrote in
message
>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...
>Let me be more specific:
>My database consists of 2 data files and 2 log files:
>e:\data1.mdf
>f:\data2.mdf
>g:\log1.ldf
>f:\log2.ldf
>How can I get the data back to 1 file and 1 drive ?
>How can I get the log bacl to 1 file and 1 drice.
>>--Original Message--
>>I have a 20g database, the data and log files are spread
>>across multiple drives. What is the best way to get the
>>data back to 1 drive and the log back to 1 drive ?
>>.
>
--=_NextPart_000_00F8_01C3CDF1.9708E1E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

OK, moving files is different. =What you can do is detach and reattach the database:
1. Detach the =database, using sp_detach_db
2. Move the files to =a different drive
3. Reattach the =database, using sp_attach_db
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Kelly" wrote in message news:062b01c3ce17$19=2e4be0$a601280a@.phx.gbl...Yes they are in the primary file group. My problem is that I need to =move the primary data and log files. Those are the drives that are running =out of space. >--Original Message-->For the log =files, shrink the second log file with DBCC SHRINKFILE and use>the =EMPTYFILE option. Next, alter the database to drop the now-empty file.>>As for the data files, you did not say if they are =in the same filegroup.>If so, proceed as above. If not, you =will have to drop the indexes in the>second file and re-create them in =the primary file. Then, proceed as above.>>HTH>>-->Tom>>--=------>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Kell=y" wrote in message>news:060001c3ce13$e0c5dd70$a601280a@.phx.gbl...>=Let me be more specific:>>My database consists of 2 data files =and 2 log files:>>e:\data1.mdf>f:\data2.mdf>>g:\=log1.ldf>f:\log2.ldf>>How can I get the data back to 1 file and 1 drive ?>>How can I =get the log bacl to 1 file and 1 drice.>>--Original Message-->I have a 20g database, the data and log files =are spread>across multiple drives. What is the best way to get the>data back to 1 drive and the log back to 1 drive ?>.>>

---

--=_NextPart_000_00F8_01C3CDF1.9708E1E0--