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!
> > > >

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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||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[vbcol=seagreen]
> night from upstream systems.
> "Shishir Viriyala" wrote:
intend to[vbcol=seagreen]
already[vbcol=seagreen]
cap[vbcol=seagreen]
[vbcol=seagreen]
main file[vbcol=seagreen]
to the[vbcol=seagreen]
you want[vbcol=seagreen]
write[vbcol=seagreen]
main file[vbcol=seagreen]
the files[vbcol=seagreen]
The server[vbcol=seagreen]
5[vbcol=seagreen]
into some[vbcol=seagreen]
these[vbcol=seagreen]
created, the[vbcol=seagreen]
has[vbcol=seagreen]
the[vbcol=seagreen]
there be a[vbcol=seagreen]
of my[vbcol=seagreen]
way, I[vbcol=seagreen]
sql

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 serve
r
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 wan
t
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 file
s
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 ser
ver
> 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 so
me
> 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, t
he
> 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 b
e 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 t
o
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:
[vbcol=seagreen]
> 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 fi
le
> and created secondary files of fixed size(20GB). As data was written to t
he
> DB, SQL managed the process of balancing where data was located. If you w
ant
> 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 fi
le
> when you get a chance, you will see the data redistributed over all the fi
les
> and start to equal out.
> "Shishir Viriyala" wrote:
>|||I found that this was a process that took several weeks to drop the main fil
e
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:
[vbcol=seagreen]
> 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 alrea
dy
> so very big? How do I get some of the used space reduced so that I can ca
p
> off each main data file at around 40 GB?
> Thanks again!
> "Jeffrey K. Ericson" wrote:
>|||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:
[vbcol=seagreen]
> I found that this was a process that took several weeks to drop the main f
ile
> down. Beyond that, you'd have to relocate specific tables to specific fil
es.
> This would include dropping and transferring data. I wasn't willing to d
o
> that but this was also a decision support system where all the data came a
t
> night from upstream systems.
> "Shishir Viriyala" wrote:
>|||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[vbcol=seagreen]
> night from upstream systems.
> "Shishir Viriyala" wrote:
>
intend to[vbcol=seagreen]
already[vbcol=seagreen]
cap[vbcol=seagreen]
[vbcol=seagreen]
main file[vbcol=seagreen]
to the[vbcol=seagreen]
you want[vbcol=seagreen]
write[vbcol=seagreen]
main file[vbcol=seagreen]
the files[vbcol=seagreen]
The server[vbcol=seagreen]
5[vbcol=seagreen]
into some[vbcol=seagreen]
these[vbcol=seagreen]
created, the[vbcol=seagreen]
has[vbcol=seagreen]
the[vbcol=seagreen]
there be a[vbcol=seagreen]
of my[vbcol=seagreen]
way, I[vbcol=seagreen]

Data Driven Subscription - odd issue

Hi,
my result set for a Data Driven Subscription consists of 24 subscribers with
associated data so I would expect 24 emails. For some reason 32 emails are
sent - i.e. there are some double-ups.
Any ideas on this?
thanks
MattDo you always get 32? RS should not send duplicate emails. In some rare
cases it could happen, but these cases only have to do with system crashes.
You are not on Beta2 are you? Beta2 did have issues with duplicate emails.
If you can post the log file that might also shed some light. It would be
best to get a log file with just a single execution of the subscription.
You can do this by restarting the ReportServer service and then having the
subscription kick off. After the subscription finishes sending mails, copy
off the log file. Just make sure no other subscriptions are firing at the
same time.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:uSoQBN6kEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Hi,
> my result set for a Data Driven Subscription consists of 24 subscribers
with
> associated data so I would expect 24 emails. For some reason 32 emails
are
> sent - i.e. there are some double-ups.
> Any ideas on this?
> thanks
> Matt
>

data conversion

I have a View that multiplies a decimal (8,5) data type * money data type (no cast or convert) and for some odd reason comes up with a bit result (0 or 1). If I take the select statement out of the View, paste it into Query analyzer and execute it I get a decimal result.

It's easy enough to put a cast into the view but I'm wondering what is going on in the view that returns the bit data type.Huh? Post the code and sample data, please.|||I don't see that happening.

set nocount on
create table test1
(col1 decimal (8, 5),
col2 money)

insert into test1 values (30.2, .6)
insert into test1 values (20.0, 1.5)

go
create view test11
as
select col1 * col2 as prod, col1, col2
from test1
go
select *
from test11
go
drop view test11
drop table test1