Data File Management on SQL Server 2000

Hi Guys,
I have got some serious data management issues with my company's
implementation of about 70 some odd databases on SQL Server 2000. The server
where the databases are physically located has been partitioned into 5
drives, 4 of which are used to store our data files. I am running into some
problems managing the disk space because the used space on each of these
drives is growing disproportionately.
The problem arises from the fact that when these databases were created, the
main data files were allowed to have unrestricted growth, so what has
happened now is that one of the 4 drives is chock full of data while the
others have a lot of free space. My question to you all is, would there be a
way to take say about half the data from the main data file in each of my
databases and redistribute it among the secondary data files? This way, I
can have much more proportionate growth amongst my server drives.
Any ideas would be greatly apprecited. Thanks!I had a simliar problem. A 260GB database had one data file which was
impossible to work with. What I did was to stop any growth on the main file
and created secondary files of fixed size(20GB). As data was written to the
DB, SQL managed the process of balancing where data was located. If you want
a particualr file to grow, trun itsgrowth on, SQL will be forced to write
data to it. Just ensure you have enough free space and shrink the main file
when you get a chance, you will see the data redistributed over all the files
and start to equal out.
"Shishir Viriyala" wrote:
> Hi Guys,
> I have got some serious data management issues with my company's
> implementation of about 70 some odd databases on SQL Server 2000. The server
> where the databases are physically located has been partitioned into 5
> drives, 4 of which are used to store our data files. I am running into some
> problems managing the disk space because the used space on each of these
> drives is growing disproportionately.
> The problem arises from the fact that when these databases were created, the
> main data files were allowed to have unrestricted growth, so what has
> happened now is that one of the 4 drives is chock full of data while the
> others have a lot of free space. My question to you all is, would there be a
> way to take say about half the data from the main data file in each of my
> databases and redistribute it among the secondary data files? This way, I
> can have much more proportionate growth amongst my server drives.
> Any ideas would be greatly apprecited. Thanks!
>|||Thanks for the prompt reply! That is a great idea and I definitely intend to
use it, but what about some of my main data files right now that are already
so very big? How do I get some of the used space reduced so that I can cap
off each main data file at around 40 GB?
Thanks again!
"Jeffrey K. Ericson" wrote:
> I had a simliar problem. A 260GB database had one data file which was
> impossible to work with. What I did was to stop any growth on the main file
> and created secondary files of fixed size(20GB). As data was written to the
> DB, SQL managed the process of balancing where data was located. If you want
> a particualr file to grow, trun itsgrowth on, SQL will be forced to write
> data to it. Just ensure you have enough free space and shrink the main file
> when you get a chance, you will see the data redistributed over all the files
> and start to equal out.
> "Shishir Viriyala" wrote:
> > Hi Guys,
> >
> > I have got some serious data management issues with my company's
> > implementation of about 70 some odd databases on SQL Server 2000. The server
> > where the databases are physically located has been partitioned into 5
> > drives, 4 of which are used to store our data files. I am running into some
> > problems managing the disk space because the used space on each of these
> > drives is growing disproportionately.
> >
> > The problem arises from the fact that when these databases were created, the
> > main data files were allowed to have unrestricted growth, so what has
> > happened now is that one of the 4 drives is chock full of data while the
> > others have a lot of free space. My question to you all is, would there be a
> > way to take say about half the data from the main data file in each of my
> > databases and redistribute it among the secondary data files? This way, I
> > can have much more proportionate growth amongst my server drives.
> >
> > Any ideas would be greatly apprecited. Thanks!
> >|||I found that this was a process that took several weeks to drop the main file
down. Beyond that, you'd have to relocate specific tables to specific files.
This would include dropping and transferring data. I wasn't willing to do
that but this was also a decision support system where all the data came at
night from upstream systems.
"Shishir Viriyala" wrote:
> Thanks for the prompt reply! That is a great idea and I definitely intend to
> use it, but what about some of my main data files right now that are already
> so very big? How do I get some of the used space reduced so that I can cap
> off each main data file at around 40 GB?
> Thanks again!
> "Jeffrey K. Ericson" wrote:
> > I had a simliar problem. A 260GB database had one data file which was
> > impossible to work with. What I did was to stop any growth on the main file
> > and created secondary files of fixed size(20GB). As data was written to the
> > DB, SQL managed the process of balancing where data was located. If you want
> > a particualr file to grow, trun itsgrowth on, SQL will be forced to write
> > data to it. Just ensure you have enough free space and shrink the main file
> > when you get a chance, you will see the data redistributed over all the files
> > and start to equal out.
> >
> > "Shishir Viriyala" wrote:
> >
> > > Hi Guys,
> > >
> > > I have got some serious data management issues with my company's
> > > implementation of about 70 some odd databases on SQL Server 2000. The server
> > > where the databases are physically located has been partitioned into 5
> > > drives, 4 of which are used to store our data files. I am running into some
> > > problems managing the disk space because the used space on each of these
> > > drives is growing disproportionately.
> > >
> > > The problem arises from the fact that when these databases were created, the
> > > main data files were allowed to have unrestricted growth, so what has
> > > happened now is that one of the 4 drives is chock full of data while the
> > > others have a lot of free space. My question to you all is, would there be a
> > > way to take say about half the data from the main data file in each of my
> > > databases and redistribute it among the secondary data files? This way, I
> > > can have much more proportionate growth amongst my server drives.
> > >
> > > Any ideas would be greatly apprecited. Thanks!
> > >|||Thank you. I think I will have to go the route of manually dropping and
transferring tables to manage used space. Appreciate the help!
"Jeffrey K. Ericson" wrote:
> I found that this was a process that took several weeks to drop the main file
> down. Beyond that, you'd have to relocate specific tables to specific files.
> This would include dropping and transferring data. I wasn't willing to do
> that but this was also a decision support system where all the data came at
> night from upstream systems.
> "Shishir Viriyala" wrote:
> > Thanks for the prompt reply! That is a great idea and I definitely intend to
> > use it, but what about some of my main data files right now that are already
> > so very big? How do I get some of the used space reduced so that I can cap
> > off each main data file at around 40 GB?
> >
> > Thanks again!
> >
> > "Jeffrey K. Ericson" wrote:
> >
> > > I had a simliar problem. A 260GB database had one data file which was
> > > impossible to work with. What I did was to stop any growth on the main file
> > > and created secondary files of fixed size(20GB). As data was written to the
> > > DB, SQL managed the process of balancing where data was located. If you want
> > > a particualr file to grow, trun itsgrowth on, SQL will be forced to write
> > > data to it. Just ensure you have enough free space and shrink the main file
> > > when you get a chance, you will see the data redistributed over all the files
> > > and start to equal out.
> > >
> > > "Shishir Viriyala" wrote:
> > >
> > > > Hi Guys,
> > > >
> > > > I have got some serious data management issues with my company's
> > > > implementation of about 70 some odd databases on SQL Server 2000. The server
> > > > where the databases are physically located has been partitioned into 5
> > > > drives, 4 of which are used to store our data files. I am running into some
> > > > problems managing the disk space because the used space on each of these
> > > > drives is growing disproportionately.
> > > >
> > > > The problem arises from the fact that when these databases were created, the
> > > > main data files were allowed to have unrestricted growth, so what has
> > > > happened now is that one of the 4 drives is chock full of data while the
> > > > others have a lot of free space. My question to you all is, would there be a
> > > > way to take say about half the data from the main data file in each of my
> > > > databases and redistribute it among the secondary data files? This way, I
> > > > can have much more proportionate growth amongst my server drives.
> > > >
> > > > Any ideas would be greatly apprecited. Thanks!
> > > >|||Actually, you could accomplish exactly what Jeff was discribing, turn
AUTOGROW off of the main file and shrink it down to its minimum size, then,
all you have to do is find some of your large tables and rebuild the cluster
index.
Because rebuilding the index must process the pages in sorted order, the
system must copy the information to new pages. If the main file is capped,
then the data must go to the secondary files.
If you rebuild the Clustered index, you will be relocating the data pages
themselves. Because a rebuild of the Cluster Index forces a rebuild of the
non-clustered indexes, the Index Pages for that table will move as well.
Just make sure you shrink the main file down again before you start
rebuilding indexes on the next table.
It's a little slow going, but faster than letting it happen just through
normal usage.
Sincerely,
Anthony Thomas
"Shishir Viriyala" <ShishirViriyala@.discussions.microsoft.com> wrote in
message news:0B400C78-8FD5-416B-AE77-6CFD75CAFEB9@.microsoft.com...
Thank you. I think I will have to go the route of manually dropping and
transferring tables to manage used space. Appreciate the help!
"Jeffrey K. Ericson" wrote:
> I found that this was a process that took several weeks to drop the main
file
> down. Beyond that, you'd have to relocate specific tables to specific
files.
> This would include dropping and transferring data. I wasn't willing to
do
> that but this was also a decision support system where all the data came
at
> night from upstream systems.
> "Shishir Viriyala" wrote:
> > Thanks for the prompt reply! That is a great idea and I definitely
intend to
> > use it, but what about some of my main data files right now that are
already
> > so very big? How do I get some of the used space reduced so that I can
cap
> > off each main data file at around 40 GB?
> >
> > Thanks again!
> >
> > "Jeffrey K. Ericson" wrote:
> >
> > > I had a simliar problem. A 260GB database had one data file which was
> > > impossible to work with. What I did was to stop any growth on the
main file
> > > and created secondary files of fixed size(20GB). As data was written
to the
> > > DB, SQL managed the process of balancing where data was located. If
you want
> > > a particualr file to grow, trun itsgrowth on, SQL will be forced to
write
> > > data to it. Just ensure you have enough free space and shrink the
main file
> > > when you get a chance, you will see the data redistributed over all
the files
> > > and start to equal out.
> > >
> > > "Shishir Viriyala" wrote:
> > >
> > > > Hi Guys,
> > > >
> > > > I have got some serious data management issues with my company's
> > > > implementation of about 70 some odd databases on SQL Server 2000.
The server
> > > > where the databases are physically located has been partitioned into
5
> > > > drives, 4 of which are used to store our data files. I am running
into some
> > > > problems managing the disk space because the used space on each of
these
> > > > drives is growing disproportionately.
> > > >
> > > > The problem arises from the fact that when these databases were
created, the
> > > > main data files were allowed to have unrestricted growth, so what
has
> > > > happened now is that one of the 4 drives is chock full of data while
the
> > > > others have a lot of free space. My question to you all is, would
there be a
> > > > way to take say about half the data from the main data file in each
of my
> > > > databases and redistribute it among the secondary data files? This
way, I
> > > > can have much more proportionate growth amongst my server drives.
> > > >
> > > > Any ideas would be greatly apprecited. Thanks!
> > > >