Hi all,
I need to keep a MSSQL 2000 database (replicating
subscription) running for 2 more weeks, but it is running
out of drive space on an E:/drive. There is space on the
F:/Drive. This is just theoretical, I've never done this,
but can I add another data file on this F:/ drive, in the
primary filegroup, and if so will the database continue to
grow on this new disk space. If not, will I have to
manually move some tables to the new file.
Please help. I'm running out of time. Thanks.
Don SalugaDon,
yes, you can add the new file to the existing filegroup.
You won't be able to move objects to this file unless you
create a new filegroup and add the new file to it, however
in your case this is probably not required as when new
space is required in the database, it is added in
proportional fill manner (relative to the %occupied space
of the 2 files).
HTH,
Paul Ibison|||Thanks so much Paul.
I'll try adding the second file to the primary filegroup.
Just one question about the proportional fill manner
(relative to the %occupied space. If drive E:/ has 471MBs
free and Drive F:\ (which will hold the new file) has 20
GBs free, will Drive E: still fill up and stop the
database or will the data just flow to the F:/ drive?
of the 2 files). Thanks.
Don
>--Original Message--
>Don,
>yes, you can add the new file to the existing filegroup.
>You won't be able to move objects to this file unless you
>create a new filegroup and add the new file to it,
however
>in your case this is probably not required as when new
>space is required in the database, it is added in
>proportional fill manner (relative to the %occupied space
>of the 2 files).
>HTH,
>Paul Ibison
>.
>|||Don,
from BOL: "if file f1 has 100 megabytes (MB) free and file
f2 has 200 MB free, one extent is allocated from file f1,
two extents from file f2, and so on. This way both files
become full at about the same time, and simple striping is
achieved".
So, in your case I'd avoid creating a huge file on your
new disk, but create a file of the size of your existing
datafile. That way there shouldn't be the old datafile
should never need to increase in size. If you want more
space, you just add another file to the primary filegroup
but on the new disk.
HTH,
Paul Ibison
>--Original Message--
>Thanks so much Paul.
>I'll try adding the second file to the primary
filegroup.
>Just one question about the proportional fill manner
>(relative to the %occupied space. If drive E:/ has 471MBs
>free and Drive F:\ (which will hold the new file) has 20
>GBs free, will Drive E: still fill up and stop the
>database or will the data just flow to the F:/ drive?
>of the 2 files). Thanks.
>Don
>>--Original Message--
>>Don,
>>yes, you can add the new file to the existing filegroup.
>>You won't be able to move objects to this file unless
you
>>create a new filegroup and add the new file to it,
>however
>>in your case this is probably not required as when new
>>space is required in the database, it is added in
>>proportional fill manner (relative to the %occupied
space
>>of the 2 files).
>>HTH,
>>Paul Ibison
>>.
>.
>|||IIRC, data will flow to the new empty file before the old file will expand
beyond its current size. Once they both have the same free space
(relative), I'm not sure how SQL Server will decide which one to grow. To
prolong the wait, just make sure the new file is bigger than the old one.
;-)
--
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:1ac8701c44f03$2b1996d0$a101280a@.phx.gbl...
> Thanks so much Paul.
> I'll try adding the second file to the primary filegroup.
> Just one question about the proportional fill manner
> (relative to the %occupied space. If drive E:/ has 471MBs
> free and Drive F:\ (which will hold the new file) has 20
> GBs free, will Drive E: still fill up and stop the
> database or will the data just flow to the F:/ drive?
> of the 2 files). Thanks.
> Don
> >--Original Message--
> >Don,
> >yes, you can add the new file to the existing filegroup.
> >You won't be able to move objects to this file unless you
> >create a new filegroup and add the new file to it,
> however
> >in your case this is probably not required as when new
> >space is required in the database, it is added in
> >proportional fill manner (relative to the %occupied space
> >of the 2 files).
> >HTH,
> >Paul Ibison
> >.
> >|||Thanks Paul and Aaron,
I added the second primary group file to the F:/ Drive and
it looks like the data is going to there much faster than
to the E:/ drive that was filling up. It may be too early
to tell the exact ratio of E to F of data flow. It looks
like this fix saved the day.
Don
>--Original Message--
>IIRC, data will flow to the new empty file before the old
file will expand
>beyond its current size. Once they both have the same
free space
>(relative), I'm not sure how SQL Server will decide which
one to grow. To
>prolong the wait, just make sure the new file is bigger
than the old one.
>;-)
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:1ac8701c44f03$2b1996d0$a101280a@.phx.gbl...
>> Thanks so much Paul.
>> I'll try adding the second file to the primary
filegroup.
>> Just one question about the proportional fill manner
>> (relative to the %occupied space. If drive E:/ has
471MBs
>> free and Drive F:\ (which will hold the new file) has 20
>> GBs free, will Drive E: still fill up and stop the
>> database or will the data just flow to the F:/ drive?
>> of the 2 files). Thanks.
>> Don
>> >--Original Message--
>> >Don,
>> >yes, you can add the new file to the existing
filegroup.
>> >You won't be able to move objects to this file unless
you
>> >create a new filegroup and add the new file to it,
>> however
>> >in your case this is probably not required as when new
>> >space is required in the database, it is added in
>> >proportional fill manner (relative to the %occupied
space
>> >of the 2 files).
>> >HTH,
>> >Paul Ibison
>> >.
>> >
>
>.
>|||To prevent any further SQL initiated filegrowth on your E: drive you can disable automatic filegrow on the primary datafile
for this particular database all together and just allow the newly created data file on your other partition to grow, and/or add other data files as need arises. This will prevent SQL from filling up the precious remaining disk space (maybe you are already using more than 90%) on your E: drive before the system halts running out of diskspace. I occasionally have to do this to balance disk load on terabyte size dbs.|||Hi there,
One of our drives is completely filled up with a database file and so I would like to move some of the db objects to a newly created data file on another drive. Can anyone give me a hint on how to do this?
Thanks
Andreas
"Sassan Karai" wrote:
> To prevent any further SQL initiated filegrowth on your E: drive you can disable automatic filegrow on the primary datafile
> for this particular database all together and just allow the newly created data file on your other partition to grow, and/or add other data files as need arises. This will prevent SQL from filling up the precious remaining disk space (maybe you are already using more than 90%) on your E: drive before the system halts running out of diskspace. I occasionally have to do this to balance disk load on terabyte size dbs.
>|||Hi
http://support.microsoft.com/directory/article.asp?ID=kb;en-us;Q224071--
Moving SQL Server Databases to a New Location
"Andreas" <Andreas@.discussions.microsoft.com> wrote in message
news:58B7A052-6AAB-4E31-AD6F-A13F29A895CF@.microsoft.com...
> Hi there,
> One of our drives is completely filled up with a database file and so I
would like to move some of the db objects to a newly created data file on
another drive. Can anyone give me a hint on how to do this?
> Thanks
> Andreas
>
> "Sassan Karai" wrote:
> > To prevent any further SQL initiated filegrowth on your E: drive you can
disable automatic filegrow on the primary datafile
> > for this particular database all together and just allow the newly
created data file on your other partition to grow, and/or add other data
files as need arises. This will prevent SQL from filling up the precious
remaining disk space (maybe you are already using more than 90%) on your E:
drive before the system halts running out of diskspace. I occasionally have
to do this to balance disk load on terabyte size dbs.
> >
