New Datafile

Hi
The diskspace for the primary datafile for my sql database is running
low, so I had to create a new datafile on another drive to allocate
more space for my database. However, enterprise manager tends to put
all objects on the primary filegroup even when i have specified the new
datafile on a secondary filegroup.
Is there any configuration that i have to do for the database to access
the secondary filegroup instead?
Any help is much appreciated
Kind Regards
EHi

> Is there any configuration that i have to do for the database to access
> the secondary filegroup instead?
Primary Group is a default filegroup
Take a look at this example
create database test
on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
filegroup user_fg
(name = 'datafile2', filename = 'c:\temp\datafile2')
log on
(name = 'logfile1', filename = 'c:\temp\logfile1')
go
use test
go
create table t1(col1 int)
create table t2(col1 int) on [primary]
create table t3(col1 int) on user_fg
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
and i.indid < 2
and i.groupid = s.groupid
table_name filegroup
-- --
t1 PRIMARY
t2 PRIMARY
t3 user_fg
alter database test modify filegroup user_fg default
create table t4(col1 int)
select
object_name(i.id) as table_name,
groupname as [filegroup]
from sysfilegroups s, sysindexes i
where i.id in (object_id('t1'), object_id('t2'),
object_id('t3'),object_id('t4'))
and i.indid < 2
and i.groupid = s.groupid
table_name filegroup
-- --
t1 PRIMARY
t2 PRIMARY
t3 user_fg
drop database test
"apricotz" <estheryong13@.googlemail.com> wrote in message
news:1154512953.663965.317450@.m79g2000cwm.googlegroups.com...
> Hi
> The diskspace for the primary datafile for my sql database is running
> low, so I had to create a new datafile on another drive to allocate
> more space for my database. However, enterprise manager tends to put
> all objects on the primary filegroup even when i have specified the new
> datafile on a secondary filegroup.
> Is there any configuration that i have to do for the database to access
> the secondary filegroup instead?
> Any help is much appreciated
> Kind Regards
> E
>|||Hi
I am a newbie in sql.. can you explain what the script does to me
please?
Many thanks
Uri Dimant wrote:
[vbcol=seagreen]
> Hi
>
> Primary Group is a default filegroup
> Take a look at this example
> create database test
> on primary(name = 'datafile1', filename = 'c:\temp\datafile1'),
> filegroup user_fg
> (name = 'datafile2', filename = 'c:\temp\datafile2')
> log on
> (name = 'logfile1', filename = 'c:\temp\logfile1')
> go
> use test
> go
> create table t1(col1 int)
> create table t2(col1 int) on [primary]
> create table t3(col1 int) on user_fg
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'), object_id('t3'))
> and i.indid < 2
> and i.groupid = s.groupid
> table_name filegroup
> -- --
> t1 PRIMARY
> t2 PRIMARY
> t3 user_fg
> alter database test modify filegroup user_fg default
> create table t4(col1 int)
> select
> object_name(i.id) as table_name,
> groupname as [filegroup]
> from sysfilegroups s, sysindexes i
> where i.id in (object_id('t1'), object_id('t2'),
> object_id('t3'),object_id('t4'))
> and i.indid < 2
> and i.groupid = s.groupid
> table_name filegroup
> -- --
> t1 PRIMARY
> t2 PRIMARY
> t3 user_fg
> drop database test
> "apricotz" <estheryong13@.googlemail.com> wrote in message
> news:1154512953.663965.317450@.m79g2000cwm.googlegroups.com...|||1) creates a database with two file group
2) creates three table with specify on which file group to be located
3)qyering sytem table to ensure that we created tables on different groups
4) alter database to modify a default filegroup , now it will be 'user_fg'
"apricotz" <estheryong13@.googlemail.com> wrote in message
news:1154523549.692151.325370@.p79g2000cwp.googlegroups.com...
> Hi
> I am a newbie in sql.. can you explain what the script does to me
> please?
> Many thanks
>
> Uri Dimant wrote:
>
>|||I;ve managed to change the default filegroup to Secondary (in my case)
but whenever i run a DTS package which adds to the database it says
that the primary datafile is full. What do I have to do to make
enterprise manager think that the primary datafile is full and make use
of the secondary datafile?
Uri Dimant wrote:
[vbcol=seagreen]
> 1) creates a database with two file group
> 2) creates three table with specify on which file group to be located
> 3)qyering sytem table to ensure that we created tables on different groups
> 4) alter database to modify a default filegroup , now it will be 'user_fg'
>
> "apricotz" <estheryong13@.googlemail.com> wrote in message
> news:1154523549.692151.325370@.p79g2000cwp.googlegroups.com...|||Hi
It is because during the data transmission (DTS Package) the data file
tries to grow up and it may take some time , meanwhile it throws the
error
Make sure that the file is "big" enough and does not need to grow , you
can expand the size's file by usin alter database commad a
"apricotz" <estheryong13@.googlemail.com> wrote in message
news:1154525392.738042.89920@.b28g2000cwb.googlegroups.com...
> I;ve managed to change the default filegroup to Secondary (in my case)
> but whenever i run a DTS package which adds to the database it says
> that the primary datafile is full. What do I have to do to make
> enterprise manager think that the primary datafile is full and make use
> of the secondary datafile?
>
> Uri Dimant wrote:
>
>|||Thanks for your help!
Uri Dimant wrote:
[vbcol=seagreen]
> Hi
> It is because during the data transmission (DTS Package) the data file
> tries to grow up and it may take some time , meanwhile it throws the
> error
> Make sure that the file is "big" enough and does not need to grow , you
> can expand the size's file by usin alter database commad a
> "apricotz" <estheryong13@.googlemail.com> wrote in message
> news:1154525392.738042.89920@.b28g2000cwb.googlegroups.com...