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--
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment