Filegroup Question

I know that tables and indexes can belong to a certain filegroup, and that
this can be specified at creation time of the object, but can stored
procedures, views, or functions belong to a specific filegroup? Or are they
defaulted to the Primary filegroup since their information is stored in the
system tables for the database?
Also, is there a Information Schema view that will return a list of
filesgroups and the objects that belong to those filegroups?
Thanks!
Jason
No you can not specify where those objects live. Only the data and indexes.
I don't believe there is a view for what you want. You will most likely
have to do something custom to get that without using some third party tool.
Andrew J. Kelly SQL MVP
"Jason Delaune" <JasonDelaune@.discussions.microsoft.com> wrote in message
news:BAA293F3-D130-4EC9-8589-8858EEB3A75C@.microsoft.com...
>I know that tables and indexes can belong to a certain filegroup, and that
> this can be specified at creation time of the object, but can stored
> procedures, views, or functions belong to a specific filegroup? Or are
> they
> defaulted to the Primary filegroup since their information is stored in
> the
> system tables for the database?
> Also, is there a Information Schema view that will return a list of
> filesgroups and the objects that belong to those filegroups?
> Thanks!
> Jason
|||Thanks for the quick response Andrew. That's what I thought, but I figured I
would ask the group to see if my thoughts were wrong.
Jason
"Andrew J. Kelly" wrote:

> No you can not specify where those objects live. Only the data and indexes.
> I don't believe there is a view for what you want. You will most likely
> have to do something custom to get that without using some third party tool.
> --
> Andrew J. Kelly SQL MVP
>
> "Jason Delaune" <JasonDelaune@.discussions.microsoft.com> wrote in message
> news:BAA293F3-D130-4EC9-8589-8858EEB3A75C@.microsoft.com...
>
>

Filegroup Question

I know that tables and indexes can belong to a certain filegroup, and that
this can be specified at creation time of the object, but can stored
procedures, views, or functions belong to a specific filegroup? Or are they
defaulted to the Primary filegroup since their information is stored in the
system tables for the database?
Also, is there a Information Schema view that will return a list of
filesgroups and the objects that belong to those filegroups?
Thanks!
JasonNo you can not specify where those objects live. Only the data and indexes.
I don't believe there is a view for what you want. You will most likely
have to do something custom to get that without using some third party tool.
--
Andrew J. Kelly SQL MVP
"Jason Delaune" <JasonDelaune@.discussions.microsoft.com> wrote in message
news:BAA293F3-D130-4EC9-8589-8858EEB3A75C@.microsoft.com...
>I know that tables and indexes can belong to a certain filegroup, and that
> this can be specified at creation time of the object, but can stored
> procedures, views, or functions belong to a specific filegroup? Or are
> they
> defaulted to the Primary filegroup since their information is stored in
> the
> system tables for the database?
> Also, is there a Information Schema view that will return a list of
> filesgroups and the objects that belong to those filegroups?
> Thanks!
> Jason|||Thanks for the quick response Andrew. That's what I thought, but I figured I
would ask the group to see if my thoughts were wrong.
Jason
"Andrew J. Kelly" wrote:
> No you can not specify where those objects live. Only the data and indexes.
> I don't believe there is a view for what you want. You will most likely
> have to do something custom to get that without using some third party tool.
> --
> Andrew J. Kelly SQL MVP
>
> "Jason Delaune" <JasonDelaune@.discussions.microsoft.com> wrote in message
> news:BAA293F3-D130-4EC9-8589-8858EEB3A75C@.microsoft.com...
> >I know that tables and indexes can belong to a certain filegroup, and that
> > this can be specified at creation time of the object, but can stored
> > procedures, views, or functions belong to a specific filegroup? Or are
> > they
> > defaulted to the Primary filegroup since their information is stored in
> > the
> > system tables for the database?
> >
> > Also, is there a Information Schema view that will return a list of
> > filesgroups and the objects that belong to those filegroups?
> >
> > Thanks!
> > Jason
>
>

File group space error

Hi,

Received the following error during index creation of the tables. The
data & log files are set to 'unrestricted growth' and enough space
available on the disk. Any reasons?

___________
Microsoft OLE DB Provider for SQL Server (80040e14): Could not allocate
new page for database 'Ultimareports'. There are no more pages available
in filegroup PRIMARY. Space can be created by dropping objects, adding
additional files, or allowing file growth
___________

Thanks
John Jayaseelan

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!John Jayaseelan <john.jayaseelan@.caravan-club.co.uk> wrote in message news:<3fc34206$0$195$75868355@.news.frii.net>...
> Hi,
> Received the following error during index creation of the tables. The
> data & log files are set to 'unrestricted growth' and enough space
> available on the disk. Any reasons?
> ___________
> Microsoft OLE DB Provider for SQL Server (80040e14): Could not allocate
> new page for database 'Ultimareports'. There are no more pages available
> in filegroup PRIMARY. Space can be created by dropping objects, adding
> additional files, or allowing file growth
> ___________
> Thanks
> John Jayaseelan
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

It's not clear what the issue is without some more information. Do
sp_helpfile and sp_helpfilegroup return what you expect to see? How
big is the database, what is the autogrow increment set to, and what
is the filesystem? Are there any possible external factors, like disk
quotas?

Simon

file group creation .

We have database of 3 GB . Database growth is 2% every week . I would like to know whether I should use secondory filegroups . Will creation of filegroups will increase the performance of SQL server . What is the impact of creation of these filegroups in
longer terms ?
How should I map important tables and their indexes to one filegroup .
Thanks in advance .
Swati.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Hi
Only put filegroups in place if you have multiple volumes, running against
different disks on your server, preferably on different controllers. 3Gb is
not big and growth is not high enough to justify it.
On Table and Index creation, you can specify where to put the object with
the "ON" option for those commands. See BOL.
Cheers
Mike
"swati zingade" wrote:

> We have database of 3 GB . Database growth is 2% every week . I would like to know whether I should use secondory filegroups . Will creation of filegroups will increase the performance of SQL server . What is the impact of creation of these filegroups
in longer terms ?
> How should I map important tables and their indexes to one filegroup .
>
> Thanks in advance .
> Swati.
>
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>
|||> We have database of 3 GB . Database growth is 2% every week . I would like
to know whether I should use secondory filegroups . Will creation of
filegroups will increase the performance of SQL server . What is the impact
of creation of these filegroups in longer terms ?
> How should I map important tables and their indexes to one filegroup .
Here are some tips: http://www.sql-server-performance.com/filegroups.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Hi
Unless you have a separate disc sub-system to locate the additional
filegroup, there will probably be little performance benefit to gain by
having the extra one. Even then, it will depend on how the tables/indexes are
used as to whether multiple data files for a single filegroup or two separate
files groups will produce the most benefit.
You may want to check out the "SQL Server 2000 Performance Tuning Technical
Reference" ISBN 0-7356-1270-6
http://www.sql-server-performance.com/
John
"swati zingade" wrote:

> We have database of 3 GB . Database growth is 2% every week . I would like to know whether I should use secondory filegroups . Will creation of filegroups will increase the performance of SQL server . What is the impact of creation of these filegroups
in longer terms ?
> How should I map important tables and their indexes to one filegroup .
>
> Thanks in advance .
> Swati.
>
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>
|||Swati,
I have done extensive testing on this and have found that performance
can be DEGRADED by having multiple data files IF the data files reside
on the same volume.
You can get some performance benefit if the filegroups are placed on
separate physical disks preferably through separate disk or RAID
controllers. You will need to carefully decide what data you will be
putting in the filegroups to reap this performance gain.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
swati.zingade@.ugamsolutions.com wrote:
> We have database of 3 GB . Database growth is 2% every week . I would like to know whether I should use secondory filegroups . Will creation of filegroups will increase the performance of SQL server . What is the impact of creation of these filegroups
in longer terms ?
> How should I map important tables and their indexes to one filegroup .
>
> Thanks in advance .
> Swati.
>
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

file group creation .

We have database of 3 GB . Database growth is 2% every week . I would like to know whether I should use secondory filegroups . Will creation of filegroups will increase the performance of SQL server . What is the impact of creation of these filegroups in longer terms ?
How should I map important tables and their indexes to one filegroup .
Thanks in advance .
Swati.
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...Hi
Only put filegroups in place if you have multiple volumes, running against
different disks on your server, preferably on different controllers. 3Gb is
not big and growth is not high enough to justify it.
On Table and Index creation, you can specify where to put the object with
the "ON" option for those commands. See BOL.
Cheers
Mike
"swati zingade" wrote:
> We have database of 3 GB . Database growth is 2% every week . I would like to know whether I should use secondory filegroups . Will creation of filegroups will increase the performance of SQL server . What is the impact of creation of these filegroups in longer terms ?
> How should I map important tables and their indexes to one filegroup .
>
> Thanks in advance .
> Swati.
>
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>|||> We have database of 3 GB . Database growth is 2% every week . I would like
to know whether I should use secondory filegroups . Will creation of
filegroups will increase the performance of SQL server . What is the impact
of creation of these filegroups in longer terms ?
> How should I map important tables and their indexes to one filegroup .
Here are some tips: http://www.sql-server-performance.com/filegroups.asp.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Hi
Unless you have a separate disc sub-system to locate the additional
filegroup, there will probably be little performance benefit to gain by
having the extra one. Even then, it will depend on how the tables/indexes are
used as to whether multiple data files for a single filegroup or two separate
files groups will produce the most benefit.
You may want to check out the "SQL Server 2000 Performance Tuning Technical
Reference" ISBN 0-7356-1270-6
http://www.sql-server-performance.com/
John
"swati zingade" wrote:
> We have database of 3 GB . Database growth is 2% every week . I would like to know whether I should use secondory filegroups . Will creation of filegroups will increase the performance of SQL server . What is the impact of creation of these filegroups in longer terms ?
> How should I map important tables and their indexes to one filegroup .
>
> Thanks in advance .
> Swati.
>
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>|||Swati,
I have done extensive testing on this and have found that performance
can be DEGRADED by having multiple data files IF the data files reside
on the same volume.
You can get some performance benefit if the filegroups are placed on
separate physical disks preferably through separate disk or RAID
controllers. You will need to carefully decide what data you will be
putting in the filegroups to reap this performance gain.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
swati.zingade@.ugamsolutions.com wrote:
> We have database of 3 GB . Database growth is 2% every week . I would like to know whether I should use secondory filegroups . Will creation of filegroups will increase the performance of SQL server . What is the impact of creation of these filegroups in longer terms ?
> How should I map important tables and their indexes to one filegroup .
>
> Thanks in advance .
> Swati.
>
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

file group creation .

We have database of 3 GB . Database growth is 2% every week . I would like t
o know whether I should use secondory filegroups . Will creation of filegrou
ps will increase the performance of SQL server . What is the impact of crea
tion of these filegroups in
longer terms ?
How should I map important tables and their indexes to one filegroup .
Thanks in advance .
Swati.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Hi
Only put filegroups in place if you have multiple volumes, running against
different disks on your server, preferably on different controllers. 3Gb is
not big and growth is not high enough to justify it.
On Table and Index creation, you can specify where to put the object with
the "ON" option for those commands. See BOL.
Cheers
Mike
"swati zingade" wrote:

> We have database of 3 GB . Database growth is 2% every week . I would like to know
whether I should use secondory filegroups . Will creation of filegroups will increa
se the performance of SQL server . What is the impact of creation of these filegrou
ps
in longer terms ?
> How should I map important tables and their indexes to one filegroup .
>
> Thanks in advance .
> Swati.
>
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NE
T resources...
>|||> We have database of 3 GB . Database growth is 2% every week . I would like
to know whether I should use secondory filegroups . Will creation of
filegroups will increase the performance of SQL server . What is the impact
of creation of these filegroups in longer terms ?
> How should I map important tables and their indexes to one filegroup .
Here are some tips: http://www.sql-server-performance.com/filegroups.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Hi
Unless you have a separate disc sub-system to locate the additional
filegroup, there will probably be little performance benefit to gain by
having the extra one. Even then, it will depend on how the tables/indexes ar
e
used as to whether multiple data files for a single filegroup or two separat
e
files groups will produce the most benefit.
You may want to check out the "SQL Server 2000 Performance Tuning Technical
Reference" ISBN 0-7356-1270-6
http://www.sql-server-performance.com/
John
"swati zingade" wrote:

> We have database of 3 GB . Database growth is 2% every week . I would like to know
whether I should use secondory filegroups . Will creation of filegroups will increa
se the performance of SQL server . What is the impact of creation of these filegrou
ps
in longer terms ?
> How should I map important tables and their indexes to one filegroup .
>
> Thanks in advance .
> Swati.
>
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NE
T resources...
>|||Swati,
I have done extensive testing on this and have found that performance
can be DEGRADED by having multiple data files IF the data files reside
on the same volume.
You can get some performance benefit if the filegroups are placed on
separate physical disks preferably through separate disk or RAID
controllers. You will need to carefully decide what data you will be
putting in the filegroups to reap this performance gain.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
swati.zingade@.ugamsolutions.com wrote:
> We have database of 3 GB . Database growth is 2% every week . I would like to know
whether I should use secondory filegroups . Will creation of filegroups will increa
se the performance of SQL server . What is the impact of creation of these filegrou
ps
in longer terms ?
> How should I map important tables and their indexes to one filegroup .
>
> Thanks in advance .
> Swati.
>
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resour
ces...