Move Large MDF file

Hi all!
I've got a fairly large database that has 2 datafiles (x:\ra.mdf and
y:\ra2.ndf). ra.mdf is about 20G bytes and ra2.ndf is about 15G bytes. This
needs to be available as close to 24/7 as possible.
My goal is move x:\ra.mdf to z:\ra.mdf with minimal downtime. (Meaning I
can't just detach the DB, move the MDF file, and re-attach).
My original idea was to:
1) Increase the size of the RA2.ndf file to 40G to allow it to hold all of
the data
2) DBCC SHRINKFILE (RA_Data, EMPTYFILE)
3) DBCC SHRINKFILE(RA_Data, TRUNCATEONLY)
4) In theory my MDF file would now be small and I could detach, move the
file, re-attach in a minute. In practice it seems that the MDF file is still
about 20G, even though SQL server reports using only a very little bit of it.
Steps 1 and 2 work fine, howeve I can't seem to physically shrink the MDF
file. Is there a way around this issue?
Thanks in advance for any help you can provide!
-Phil Mattson
How about using Log Shipping or a SQL Server cluster?
Rick Sawtell
MCT, MCSD, MCDBA
"pmattson" <pmattson@.discussions.microsoft.com> wrote in message
news:9E08DE35-7360-4CE0-938A-F3219A8889D6@.microsoft.com...
> Hi all!
> I've got a fairly large database that has 2 datafiles (x:\ra.mdf and
> y:\ra2.ndf). ra.mdf is about 20G bytes and ra2.ndf is about 15G bytes.
This
> needs to be available as close to 24/7 as possible.
> My goal is move x:\ra.mdf to z:\ra.mdf with minimal downtime. (Meaning I
> can't just detach the DB, move the MDF file, and re-attach).
> My original idea was to:
> 1) Increase the size of the RA2.ndf file to 40G to allow it to hold all of
> the data
> 2) DBCC SHRINKFILE (RA_Data, EMPTYFILE)
> 3) DBCC SHRINKFILE(RA_Data, TRUNCATEONLY)
> 4) In theory my MDF file would now be small and I could detach, move the
> file, re-attach in a minute. In practice it seems that the MDF file is
still
> about 20G, even though SQL server reports using only a very little bit of
it.
> Steps 1 and 2 work fine, howeve I can't seem to physically shrink the MDF
> file. Is there a way around this issue?
> Thanks in advance for any help you can provide!
> -Phil Mattson
|||If I hear you right what you're saying is:
1) Restore my RA database as a different DB (Say RA2) onto drive z:
2) Log ship (or manually) restore transaction log backups of RA into RA2.
3) When I'm ready to "move" my database then rename the Databases so that
RA2 becomes RA. I believe that might work for me.
Any other ideas? I'm a little concerned that my server may not be able to
handle the total amount of IO activity "log shipping" my very active RA
database transaction logs and applying them to a RA2 database.
"Rick Sawtell" wrote:

> How about using Log Shipping or a SQL Server cluster?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "pmattson" <pmattson@.discussions.microsoft.com> wrote in message
> news:9E08DE35-7360-4CE0-938A-F3219A8889D6@.microsoft.com...
> This
> still
> it.
>
>
|||If you have that much activity going on, get another server or an additional
processor. <wink>
You check your real activity levels with System Monitor and SQL Server
Profiler tools. See how much activity you are really supporting.
Rick Sawtell
MCT, MCSD, MCDBA
"pmattson" <pmattson@.discussions.microsoft.com> wrote in message
news:E58441F3-7D59-4388-844F-6682E6F5B117@.microsoft.com...[vbcol=seagreen]
> If I hear you right what you're saying is:
> 1) Restore my RA database as a different DB (Say RA2) onto drive z:
> 2) Log ship (or manually) restore transaction log backups of RA into RA2.
> 3) When I'm ready to "move" my database then rename the Databases so that
> RA2 becomes RA. I believe that might work for me.
> Any other ideas? I'm a little concerned that my server may not be able to
> handle the total amount of IO activity "log shipping" my very active RA
> database transaction logs and applying them to a RA2 database.
> "Rick Sawtell" wrote:
I[vbcol=seagreen]
all of[vbcol=seagreen]
the[vbcol=seagreen]
of[vbcol=seagreen]
MDF[vbcol=seagreen]

Move Large MDF file

Hi all!
I've got a fairly large database that has 2 datafiles (x:\ra.mdf and
y:\ra2.ndf). ra.mdf is about 20G bytes and ra2.ndf is about 15G bytes. This
needs to be available as close to 24/7 as possible.
My goal is move x:\ra.mdf to z:\ra.mdf with minimal downtime. (Meaning I
can't just detach the DB, move the MDF file, and re-attach).
My original idea was to:
1) Increase the size of the RA2.ndf file to 40G to allow it to hold all of
the data
2) DBCC SHRINKFILE (RA_Data, EMPTYFILE)
3) DBCC SHRINKFILE(RA_Data, TRUNCATEONLY)
4) In theory my MDF file would now be small and I could detach, move the
file, re-attach in a minute. In practice it seems that the MDF file is still
about 20G, even though SQL server reports using only a very little bit of it.
Steps 1 and 2 work fine, howeve I can't seem to physically shrink the MDF
file. Is there a way around this issue?
Thanks in advance for any help you can provide!
-Phil MattsonHow about using Log Shipping or a SQL Server cluster?
Rick Sawtell
MCT, MCSD, MCDBA
"pmattson" <pmattson@.discussions.microsoft.com> wrote in message
news:9E08DE35-7360-4CE0-938A-F3219A8889D6@.microsoft.com...
> Hi all!
> I've got a fairly large database that has 2 datafiles (x:\ra.mdf and
> y:\ra2.ndf). ra.mdf is about 20G bytes and ra2.ndf is about 15G bytes.
This
> needs to be available as close to 24/7 as possible.
> My goal is move x:\ra.mdf to z:\ra.mdf with minimal downtime. (Meaning I
> can't just detach the DB, move the MDF file, and re-attach).
> My original idea was to:
> 1) Increase the size of the RA2.ndf file to 40G to allow it to hold all of
> the data
> 2) DBCC SHRINKFILE (RA_Data, EMPTYFILE)
> 3) DBCC SHRINKFILE(RA_Data, TRUNCATEONLY)
> 4) In theory my MDF file would now be small and I could detach, move the
> file, re-attach in a minute. In practice it seems that the MDF file is
still
> about 20G, even though SQL server reports using only a very little bit of
it.
> Steps 1 and 2 work fine, howeve I can't seem to physically shrink the MDF
> file. Is there a way around this issue?
> Thanks in advance for any help you can provide!
> -Phil Mattson|||If I hear you right what you're saying is:
1) Restore my RA database as a different DB (Say RA2) onto drive z:
2) Log ship (or manually) restore transaction log backups of RA into RA2.
3) When I'm ready to "move" my database then rename the Databases so that
RA2 becomes RA. I believe that might work for me.
Any other ideas? I'm a little concerned that my server may not be able to
handle the total amount of IO activity "log shipping" my very active RA
database transaction logs and applying them to a RA2 database.
"Rick Sawtell" wrote:
> How about using Log Shipping or a SQL Server cluster?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "pmattson" <pmattson@.discussions.microsoft.com> wrote in message
> news:9E08DE35-7360-4CE0-938A-F3219A8889D6@.microsoft.com...
> > Hi all!
> >
> > I've got a fairly large database that has 2 datafiles (x:\ra.mdf and
> > y:\ra2.ndf). ra.mdf is about 20G bytes and ra2.ndf is about 15G bytes.
> This
> > needs to be available as close to 24/7 as possible.
> >
> > My goal is move x:\ra.mdf to z:\ra.mdf with minimal downtime. (Meaning I
> > can't just detach the DB, move the MDF file, and re-attach).
> >
> > My original idea was to:
> > 1) Increase the size of the RA2.ndf file to 40G to allow it to hold all of
> > the data
> > 2) DBCC SHRINKFILE (RA_Data, EMPTYFILE)
> > 3) DBCC SHRINKFILE(RA_Data, TRUNCATEONLY)
> > 4) In theory my MDF file would now be small and I could detach, move the
> > file, re-attach in a minute. In practice it seems that the MDF file is
> still
> > about 20G, even though SQL server reports using only a very little bit of
> it.
> >
> > Steps 1 and 2 work fine, howeve I can't seem to physically shrink the MDF
> > file. Is there a way around this issue?
> >
> > Thanks in advance for any help you can provide!
> >
> > -Phil Mattson
>
>|||If you have that much activity going on, get another server or an additional
processor. <wink>
You check your real activity levels with System Monitor and SQL Server
Profiler tools. See how much activity you are really supporting.
Rick Sawtell
MCT, MCSD, MCDBA
"pmattson" <pmattson@.discussions.microsoft.com> wrote in message
news:E58441F3-7D59-4388-844F-6682E6F5B117@.microsoft.com...
> If I hear you right what you're saying is:
> 1) Restore my RA database as a different DB (Say RA2) onto drive z:
> 2) Log ship (or manually) restore transaction log backups of RA into RA2.
> 3) When I'm ready to "move" my database then rename the Databases so that
> RA2 becomes RA. I believe that might work for me.
> Any other ideas? I'm a little concerned that my server may not be able to
> handle the total amount of IO activity "log shipping" my very active RA
> database transaction logs and applying them to a RA2 database.
> "Rick Sawtell" wrote:
> > How about using Log Shipping or a SQL Server cluster?
> >
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> > "pmattson" <pmattson@.discussions.microsoft.com> wrote in message
> > news:9E08DE35-7360-4CE0-938A-F3219A8889D6@.microsoft.com...
> > > Hi all!
> > >
> > > I've got a fairly large database that has 2 datafiles (x:\ra.mdf and
> > > y:\ra2.ndf). ra.mdf is about 20G bytes and ra2.ndf is about 15G bytes.
> > This
> > > needs to be available as close to 24/7 as possible.
> > >
> > > My goal is move x:\ra.mdf to z:\ra.mdf with minimal downtime. (Meaning
I
> > > can't just detach the DB, move the MDF file, and re-attach).
> > >
> > > My original idea was to:
> > > 1) Increase the size of the RA2.ndf file to 40G to allow it to hold
all of
> > > the data
> > > 2) DBCC SHRINKFILE (RA_Data, EMPTYFILE)
> > > 3) DBCC SHRINKFILE(RA_Data, TRUNCATEONLY)
> > > 4) In theory my MDF file would now be small and I could detach, move
the
> > > file, re-attach in a minute. In practice it seems that the MDF file is
> > still
> > > about 20G, even though SQL server reports using only a very little bit
of
> > it.
> > >
> > > Steps 1 and 2 work fine, howeve I can't seem to physically shrink the
MDF
> > > file. Is there a way around this issue?
> > >
> > > Thanks in advance for any help you can provide!
> > >
> > > -Phil Mattson
> >
> >
> >

Move data from NDF file to MDF file

I have a database which has two data files, MDF and NDF.
The question is how can I move data in NDF into MDF file,
so I could delete the NDF file from the database.
Thanks
ALex
Hi,
I HAVE ALREADY REPLIED TO UR OLD POST.
EMPTY FILE:
Migrates all data from the specified file to other files . This option
allows the file to be dropped using the ALTER DATABASE statement.
THANKS
HARI
MCDBA
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:19f9901c41d5a$bdfea1a0$a501280a@.phx.gbl...
> I have a database which has two data files, MDF and NDF.
> The question is how can I move data in NDF into MDF file,
> so I could delete the NDF file from the database.
> Thanks
> ALex

Move data from NDF file to MDF file

Please could someone help?
I have a database which has two data files, mdf and ndf.
How can I move data from ndf into mdf and remove the ndf
file from the database?
Thanks in advance.
Alex
Hi,
To do this, use the DBCC SHRINKFILE with EMPTY FILE option for the NDF file
This will empty the file and mark it as unavailable. After this use ALTER
DATABASE.. REMOVE FILE option to remove the NDF file.
Steps:
1. Take a database backup
2.
Use database_name
go
DBCCSHRINKFILE('NDFFILE_name','EMPTYFILE')
3. Use ALTER database to Remove the file
ALTER DATABASE <DBNAME> REMOVE FILE 'logical ndf file name'
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:19ee801c41d4f$c71cecc0$a401280a@.phx.gbl...
> Please could someone help?
> I have a database which has two data files, mdf and ndf.
> How can I move data from ndf into mdf and remove the ndf
> file from the database?
>
> Thanks in advance.
> Alex
|||Thank you very much Hari.
It worked!

>--Original Message--
>Hi,
>To do this, use the DBCC SHRINKFILE with EMPTY FILE
option for the NDF file
>This will empty the file and mark it as unavailable.
After this use ALTER
>DATABASE.. REMOVE FILE option to remove the NDF file.
>Steps:
>1. Take a database backup
>2.
>Use database_name
>go
>DBCCSHRINKFILE('NDFFILE_name','EMPTYFILE')
>3. Use ALTER database to Remove the file
> ALTER DATABASE <DBNAME> REMOVE FILE 'logical ndf file
name'
>Thanks
>Hari
>MCDBA
>Thanks
>Hari
>MCDBA
>
>"Alex" <anonymous@.discussions.microsoft.com> wrote in
message
>news:19ee801c41d4f$c71cecc0$a401280a@.phx.gbl...
>
>.
>

Move data from NDF file to MDF file

Please could someone help?
I have a database which has two data files, mdf and ndf.
How can I move data from ndf into mdf and remove the ndf
file from the database?
Thanks in advance.
AlexHi,
To do this, use the DBCC SHRINKFILE with EMPTY FILE option for the NDF file
This will empty the file and mark it as unavailable. After this use ALTER
DATABASE.. REMOVE FILE option to remove the NDF file.
Steps:
1. Take a database backup
2.
Use database_name
go
DBCCSHRINKFILE('NDFFILE_name','EMPTYFILE
')
3. Use ALTER database to Remove the file
ALTER DATABASE <DBNAME> REMOVE FILE 'logical ndf file name'
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:19ee801c41d4f$c71cecc0$a401280a@.phx
.gbl...
> Please could someone help?
> I have a database which has two data files, mdf and ndf.
> How can I move data from ndf into mdf and remove the ndf
> file from the database?
>
> Thanks in advance.
> Alex|||Thank you very much Hari.
It worked!

>--Original Message--
>Hi,
>To do this, use the DBCC SHRINKFILE with EMPTY FILE
option for the NDF file
>This will empty the file and mark it as unavailable.
After this use ALTER
>DATABASE.. REMOVE FILE option to remove the NDF file.
>Steps:
>1. Take a database backup
>2.
>Use database_name
>go
> DBCCSHRINKFILE('NDFFILE_name','EMPTYFILE
')
>3. Use ALTER database to Remove the file
> ALTER DATABASE <DBNAME> REMOVE FILE 'logical ndf file
name'
>Thanks
>Hari
>MCDBA
>Thanks
>Hari
>MCDBA
>
>"Alex" <anonymous@.discussions.microsoft.com> wrote in
message
> news:19ee801c41d4f$c71cecc0$a401280a@.phx
.gbl...
>
>.
>

Move data from NDF file to MDF file

I have a database which has two data files, MDF and NDF.
The question is how can I move data in NDF into MDF file,
so I could delete the NDF file from the database.
Thanks
ALexHi,
I HAVE ALREADY REPLIED TO UR OLD POST.
EMPTY FILE:
Migrates all data from the specified file to other files . This option
allows the file to be dropped using the ALTER DATABASE statement.
THANKS
HARI
MCDBA
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:19f9901c41d5a$bdfea1a0$a501280a@.phx
.gbl...
> I have a database which has two data files, MDF and NDF.
> The question is how can I move data in NDF into MDF file,
> so I could delete the NDF file from the database.
> Thanks
> ALex

Move data from NDF file to MDF file

Please could someone help?
I have a database which has two data files, mdf and ndf.
How can I move data from ndf into mdf and remove the ndf
file from the database?
Thanks in advance.
AlexHi,
To do this, use the DBCC SHRINKFILE with EMPTY FILE option for the NDF file
This will empty the file and mark it as unavailable. After this use ALTER
DATABASE.. REMOVE FILE option to remove the NDF file.
Steps:
1. Take a database backup
2.
Use database_name
go
DBCCSHRINKFILE('NDFFILE_name','EMPTYFILE')
3. Use ALTER database to Remove the file
ALTER DATABASE <DBNAME> REMOVE FILE 'logical ndf file name'
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:19ee801c41d4f$c71cecc0$a401280a@.phx.gbl...
> Please could someone help?
> I have a database which has two data files, mdf and ndf.
> How can I move data from ndf into mdf and remove the ndf
> file from the database?
>
> Thanks in advance.
> Alex|||Thank you very much Hari.
It worked!
>--Original Message--
>Hi,
>To do this, use the DBCC SHRINKFILE with EMPTY FILE
option for the NDF file
>This will empty the file and mark it as unavailable.
After this use ALTER
>DATABASE.. REMOVE FILE option to remove the NDF file.
>Steps:
>1. Take a database backup
>2.
>Use database_name
>go
>DBCCSHRINKFILE('NDFFILE_name','EMPTYFILE')
>3. Use ALTER database to Remove the file
> ALTER DATABASE <DBNAME> REMOVE FILE 'logical ndf file
name'
>Thanks
>Hari
>MCDBA
>Thanks
>Hari
>MCDBA
>
>"Alex" <anonymous@.discussions.microsoft.com> wrote in
message
>news:19ee801c41d4f$c71cecc0$a401280a@.phx.gbl...
>> Please could someone help?
>> I have a database which has two data files, mdf and ndf.
>> How can I move data from ndf into mdf and remove the ndf
>> file from the database?
>>
>> Thanks in advance.
>> Alex
>
>.
>

Move data from NDF file to MDF file

I have a database which has two data files, MDF and NDF.
The question is how can I move data in NDF into MDF file,
so I could delete the NDF file from the database.
Thanks
ALexHi,
I HAVE ALREADY REPLIED TO UR OLD POST.
EMPTY FILE:
Migrates all data from the specified file to other files . This option
allows the file to be dropped using the ALTER DATABASE statement.
THANKS
HARI
MCDBA
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:19f9901c41d5a$bdfea1a0$a501280a@.phx.gbl...
> I have a database which has two data files, MDF and NDF.
> The question is how can I move data in NDF into MDF file,
> so I could delete the NDF file from the database.
> Thanks
> ALex