new tempdb file shrinking

since the tempdb file was growing to large I created a new one on another
drive; now that file is getting to large; I need to shrink it. both the
original "tempdev" file and the new one "tempdb_1_data" are in the PRIMARY
group but the "tempdev" is locked to 1 MB while "tempdb_1_data" is now 36GB
and growing. only about 1GB of the 36GB is used.
remember i don't want to shrink the original sql server created temp file
"tempdev"
can I use dbcc shrinkfile (tempdb_1_data, 2000) without a problem? and
without having to restart the sql service?http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter L." <Peter L.@.discussions.microsoft.com> wrote in message
news:8FD556CF-ED9A-41BD-80EA-6C5E604EEEBB@.microsoft.com...
> since the tempdb file was growing to large I created a new one on another
> drive; now that file is getting to large; I need to shrink it. both the
> original "tempdev" file and the new one "tempdb_1_data" are in the PRIMARY
> group but the "tempdev" is locked to 1 MB while "tempdb_1_data" is now
36GB
> and growing. only about 1GB of the 36GB is used.
> remember i don't want to shrink the original sql server created temp file
> "tempdev"
> can I use dbcc shrinkfile (tempdb_1_data, 2000) without a problem? and
> without having to restart the sql service?

new sql server

hi,
i was wondering is it a good idea to use sata drive as a system\mssql
drive and scsi for data ant log files?
am i right that data and log files must be on raid 10'
one more q... if i want to have 300gb data on db how much Filegroups
should i use ?
how much disk drives should i use for best performance (7 or 8 as best
performance is achieve only if even no. of drives is used)?
thanx"benamis" <nera@.meilo.lt> wrote in message
news:OZtP$ixpFHA.3936@.TK2MSFTNGP10.phx.gbl...
> hi,
> i was wondering is it a good idea to use sata drive as a system\mssql
> drive and scsi for data ant log files?
> am i right that data and log files must be on raid 10'
> one more q... if i want to have 300gb data on db how much Filegroups
> should i use ?
> how much disk drives should i use for best performance (7 or 8 as best
> performance is achieve only if even no. of drives is used)?
> thanx
>
All of that depends!
SATA drives are find for the O/S and software. I'm not sure why you would
need those in addition to your SCSI drives. SCSI is generally going to have
double the throughput of comparable SATA drives.
As far as filegroups are concerned. Filegroups are used in two situations.
Situation 1 is to ease administration and allow for partial backups and
restores. Situation 2 is to create a poor mans RAID. If you already have
a RAID solution, then there is no reason to use filegroups to force
table-writes across multiple specific hard drives.
Now for best performance on drives. It depends? Is your application
primarily read only? If so, then a big RAID-5 would do the trick. Is your
application write intensive. Then some combination of data on RAID 0+1 and
logs on separate RAID 0+1. Do you have a single channel RAID controller or
dual channel?
All of this may be a moot point if you don't have enough RAM in your system.
Read up on performance and www.sql-server-performance.com
This should give you enough information to make better choices about your
given situation.
Rick Sawtell
MCT, MCSD, MCDBA|||thanx lots of info
Rick Sawtell wrote:
> "benamis" <nera@.meilo.lt> wrote in message
> news:OZtP$ixpFHA.3936@.TK2MSFTNGP10.phx.gbl...
>
>
> All of that depends!
> SATA drives are find for the O/S and software. I'm not sure why you would
> need those in addition to your SCSI drives. SCSI is generally going to ha
ve
> double the throughput of comparable SATA drives.
> As far as filegroups are concerned. Filegroups are used in two situations
.
> Situation 1 is to ease administration and allow for partial backups and
> restores. Situation 2 is to create a poor mans RAID. If you already hav
e
> a RAID solution, then there is no reason to use filegroups to force
> table-writes across multiple specific hard drives.
> Now for best performance on drives. It depends? Is your application
> primarily read only? If so, then a big RAID-5 would do the trick. Is you
r
> application write intensive. Then some combination of data on RAID 0+1 an
d
> logs on separate RAID 0+1. Do you have a single channel RAID controller
or
> dual channel?
> All of this may be a moot point if you don't have enough RAM in your syste
m.
>
> Read up on performance and www.sql-server-performance.com
>
> This should give you enough information to make better choices about your
> given situation.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

new sql server

hi,
i was wondering is it a good idea to use sata drive as a system\mssql
drive and scsi for data ant log files?
am i right that data and log files must be on raid 10?
one more q... if i want to have 300gb data on db how much Filegroups
should i use ?
how much disk drives should i use for best performance (7 or 8 as best
performance is achieve only if even no. of drives is used)?
thanx
"benamis" <nera@.meilo.lt> wrote in message
news:OZtP$ixpFHA.3936@.TK2MSFTNGP10.phx.gbl...
> hi,
> i was wondering is it a good idea to use sata drive as a system\mssql
> drive and scsi for data ant log files?
> am i right that data and log files must be on raid 10?
> one more q... if i want to have 300gb data on db how much Filegroups
> should i use ?
> how much disk drives should i use for best performance (7 or 8 as best
> performance is achieve only if even no. of drives is used)?
> thanx
>
All of that depends!
SATA drives are find for the O/S and software. I'm not sure why you would
need those in addition to your SCSI drives. SCSI is generally going to have
double the throughput of comparable SATA drives.
As far as filegroups are concerned. Filegroups are used in two situations.
Situation 1 is to ease administration and allow for partial backups and
restores. Situation 2 is to create a poor mans RAID. If you already have
a RAID solution, then there is no reason to use filegroups to force
table-writes across multiple specific hard drives.
Now for best performance on drives. It depends? Is your application
primarily read only? If so, then a big RAID-5 would do the trick. Is your
application write intensive. Then some combination of data on RAID 0+1 and
logs on separate RAID 0+1. Do you have a single channel RAID controller or
dual channel?
All of this may be a moot point if you don't have enough RAM in your system.
Read up on performance and www.sql-server-performance.com
This should give you enough information to make better choices about your
given situation.
Rick Sawtell
MCT, MCSD, MCDBA
|||thanx lots of info
Rick Sawtell wrote:
> "benamis" <nera@.meilo.lt> wrote in message
> news:OZtP$ixpFHA.3936@.TK2MSFTNGP10.phx.gbl...
>
> All of that depends!
> SATA drives are find for the O/S and software. I'm not sure why you would
> need those in addition to your SCSI drives. SCSI is generally going to have
> double the throughput of comparable SATA drives.
> As far as filegroups are concerned. Filegroups are used in two situations.
> Situation 1 is to ease administration and allow for partial backups and
> restores. Situation 2 is to create a poor mans RAID. If you already have
> a RAID solution, then there is no reason to use filegroups to force
> table-writes across multiple specific hard drives.
> Now for best performance on drives. It depends? Is your application
> primarily read only? If so, then a big RAID-5 would do the trick. Is your
> application write intensive. Then some combination of data on RAID 0+1 and
> logs on separate RAID 0+1. Do you have a single channel RAID controller or
> dual channel?
> All of this may be a moot point if you don't have enough RAM in your system.
>
> Read up on performance and www.sql-server-performance.com
>
> This should give you enough information to make better choices about your
> given situation.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

new sql server

hi,
i was wondering is it a good idea to use sata drive as a system\mssql
drive and scsi for data ant log files?
am i right that data and log files must be on raid 10'
one more q... if i want to have 300gb data on db how much Filegroups
should i use ?
how much disk drives should i use for best performance (7 or 8 as best
performance is achieve only if even no. of drives is used)?
thanx"benamis" <nera@.meilo.lt> wrote in message
news:OZtP$ixpFHA.3936@.TK2MSFTNGP10.phx.gbl...
> hi,
> i was wondering is it a good idea to use sata drive as a system\mssql
> drive and scsi for data ant log files?
> am i right that data and log files must be on raid 10'
> one more q... if i want to have 300gb data on db how much Filegroups
> should i use ?
> how much disk drives should i use for best performance (7 or 8 as best
> performance is achieve only if even no. of drives is used)?
> thanx
>
All of that depends!
SATA drives are find for the O/S and software. I'm not sure why you would
need those in addition to your SCSI drives. SCSI is generally going to have
double the throughput of comparable SATA drives.
As far as filegroups are concerned. Filegroups are used in two situations.
Situation 1 is to ease administration and allow for partial backups and
restores. Situation 2 is to create a poor mans RAID. If you already have
a RAID solution, then there is no reason to use filegroups to force
table-writes across multiple specific hard drives.
Now for best performance on drives. It depends? Is your application
primarily read only? If so, then a big RAID-5 would do the trick. Is your
application write intensive. Then some combination of data on RAID 0+1 and
logs on separate RAID 0+1. Do you have a single channel RAID controller or
dual channel?
All of this may be a moot point if you don't have enough RAM in your system.
Read up on performance and www.sql-server-performance.com
This should give you enough information to make better choices about your
given situation.
Rick Sawtell
MCT, MCSD, MCDBA|||thanx lots of info :)
Rick Sawtell wrote:
> "benamis" <nera@.meilo.lt> wrote in message
> news:OZtP$ixpFHA.3936@.TK2MSFTNGP10.phx.gbl...
>>hi,
>>i was wondering is it a good idea to use sata drive as a system\mssql
>>drive and scsi for data ant log files?
>>am i right that data and log files must be on raid 10'
>>one more q... if i want to have 300gb data on db how much Filegroups
>>should i use ?
>>how much disk drives should i use for best performance (7 or 8 as best
>>performance is achieve only if even no. of drives is used)?
>>thanx
>
> All of that depends!
> SATA drives are find for the O/S and software. I'm not sure why you would
> need those in addition to your SCSI drives. SCSI is generally going to have
> double the throughput of comparable SATA drives.
> As far as filegroups are concerned. Filegroups are used in two situations.
> Situation 1 is to ease administration and allow for partial backups and
> restores. Situation 2 is to create a poor mans RAID. If you already have
> a RAID solution, then there is no reason to use filegroups to force
> table-writes across multiple specific hard drives.
> Now for best performance on drives. It depends? Is your application
> primarily read only? If so, then a big RAID-5 would do the trick. Is your
> application write intensive. Then some combination of data on RAID 0+1 and
> logs on separate RAID 0+1. Do you have a single channel RAID controller or
> dual channel?
> All of this may be a moot point if you don't have enough RAM in your system.
>
> Read up on performance and www.sql-server-performance.com
>
> This should give you enough information to make better choices about your
> given situation.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

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

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:
> Hi
> > 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
> >|||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:
>> Hi
>> > 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
>> >
>|||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:
> 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:
> >
> >> Hi
> >>
> >> > 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
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:
>> 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:
>> >
>> >> Hi
>> >>
>> >> > 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
>> >> >
>> >
>|||Thanks for your help!
Uri Dimant wrote:
> 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:
> >
> >> 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:
> >> >
> >> >> Hi
> >> >>
> >> >> > 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
> >> >> >
> >> >
> >

New database on other local network drive

Hello,

I need help with the setup of my sql server express 2005 and more specifically, with the location of the database files. Normally, database files are located on the c: drive. In my situation, being in a high school environnement, I need to have the database files on the network drive ... lets call it g:\drive. The reason why it cannot be on c:\drive is because this drive is protected using DeepFreeze. Now, I was able to change the location of the new drive as I went into the properties window of the sql server instance but when I try to create this new database, sql gives me grief ;-|

I am from the group of people that thinks that to anything, there is a solution and am hoping that to this problem, there is hope of a solution.

Can anyone help?

Thank you

Yep there is Trace flag 1807 when you add that to your CREATE DATABASE statement it will be accepted, I found Microsoft documentation of the requirement and a link showing how easy it is to run DBCC TRACEON before executing your CREATE DATABASE statement. Hope this helps.

http://support.microsoft.com/default.aspx?scid=304261

http://www.yafla.com/dennisforbes/SAN-NAS-and-iSCSI-SQL-Server-/SAN-NAS-and-iSCSI-SQL-Server-.html