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]
Showing posts with label bytes. Show all posts
Showing posts with label bytes. Show all posts
Friday, March 9, 2012
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
> >
> >
> >
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
> >
> >
> >
Subscribe to:
Posts (Atom)