Is it better to create a seperate filegroup for all user tables ? And if so
should all the system tables that belong to the Primary filegroup reside on
the same drive with the log file . I am using SQL 2000. Which system tables
get hit the most daily for any inserts,updates and deletes that may be
occuring on the server
ThanksDepends on the disk configuration.
I believe it is generally recommended to separate the log file from any data
files, i.e., separate physical disks... however, if you have a multiple
channel controller, with high thoroughput on each channel, you may be able
to get by w/ having the log and the data on the same logical disk.
I believe that all system tables must reside in PRIMARY, I'd have to verify.
If that is true, it doesn't matter what system tables get inserts, updates,
deletes. However, you are taking away some fault tolerance if you put the
system tables and the log files on the same physical disk.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23AKD5sEXDHA.1872@.TK2MSFTNGP12.phx.gbl...
> Is it better to create a seperate filegroup for all user tables ? And if
so
> should all the system tables that belong to the Primary filegroup reside
on
> the same drive with the log file . I am using SQL 2000. Which system
tables
> get hit the most daily for any inserts,updates and deletes that may be
> occuring on the server
> Thanks
>|||You also generally want log files on RAID 1 or 10 as they are continuously
sequentially writing.
Thanks,
Greg H
"Martin Schmid" <martinschmid@.sbcglobal.net.nospam> wrote in message
news:eoDKm2EXDHA.2212@.TK2MSFTNGP09.phx.gbl...
> Depends on the disk configuration.
> I believe it is generally recommended to separate the log file from any
data
> files, i.e., separate physical disks... however, if you have a multiple
> channel controller, with high thoroughput on each channel, you may be able
> to get by w/ having the log and the data on the same logical disk.
> I believe that all system tables must reside in PRIMARY, I'd have to
verify.
> If that is true, it doesn't matter what system tables get inserts,
updates,
> deletes. However, you are taking away some fault tolerance if you put the
> system tables and the log files on the same physical disk.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23AKD5sEXDHA.1872@.TK2MSFTNGP12.phx.gbl...
> > Is it better to create a seperate filegroup for all user tables ? And if
> so
> > should all the system tables that belong to the Primary filegroup reside
> on
> > the same drive with the log file . I am using SQL 2000. Which system
> tables
> > get hit the most daily for any inserts,updates and deletes that may be
> > occuring on the server
> >
> > Thanks
> >
> >
>|||Hassan
If you are using SQL Server 7, it is a good idea to put
your system files in a seperate filegroup. If you get a
corrupted user table, if your system tables are in a
seperate filegroup you can still perform a transaction log
backup prior to invoking DR (If you have DR of course). If
they are in the same filegroup you may not be able to.
This is no longer an issue in 2000.
Hope this helps
John
Showing posts with label filegroup. Show all posts
Showing posts with label filegroup. Show all posts
Tuesday, March 20, 2012
Monday, March 12, 2012
placing existing table on a different filegroup
Hi ,
Is there any commands to place existing tables to a new file group besides
using the EM as below ?
How to place an existing table on a different filegroup (Enterprise Manager)
To place an existing table on a different filegroup
Expand a server group, and then expand a server.
Expand Databases, expand the database in which the table belongs, and then
click Tables.
In the details pane, right-click the table, and then click Design Table.
Right-click any column, and then click Properties.
On the Tables tab, in the Table Filegroup list, select the filegroup on whic
h
to place the table.
Optionally, in the Text Filegroup list, select a filegroup on which to place
any text, image, and ntext columns.
appreciate any advise
tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1You can (re)create a clustered index for the table; the data will always
move to the FG that the clustered index is built on
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"maxzsim via webservertalk.com" <u14644@.uwe> wrote in message
news:5ff96f8c4fdba@.uwe...
> Hi ,
> Is there any commands to place existing tables to a new file group besides
> using the EM as below ?
> How to place an existing table on a different filegroup (Enterprise
> Manager)
> To place an existing table on a different filegroup
> Expand a server group, and then expand a server.
> Expand Databases, expand the database in which the table belongs, and then
> click Tables.
> In the details pane, right-click the table, and then click Design Table.
> Right-click any column, and then click Properties.
> On the Tables tab, in the Table Filegroup list, select the filegroup on
> which
> to place the table.
> Optionally, in the Text Filegroup list, select a filegroup on which to
> place
> any text, image, and ntext columns.
> appreciate any advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||Hi ,
Another is for the Text FileGroup : i seem not able to change it to the new
filegroup it keeps turning back to the PRIMARY FileGroup even though i have
changed to a new filegorup and saved it
any possibly reason ?
tks & rdgs
maxzsim wrote:
>Hi ,
> Is there any commands to place existing tables to a new file group besides
>using the EM as below ?
>How to place an existing table on a different filegroup (Enterprise Manager
)
>To place an existing table on a different filegroup
>Expand a server group, and then expand a server.
>Expand Databases, expand the database in which the table belongs, and then
>click Tables.
>In the details pane, right-click the table, and then click Design Table.
>Right-click any column, and then click Properties.
>On the Tables tab, in the Table Filegroup list, select the filegroup on whi
ch
>to place the table.
>Optionally, in the Text Filegroup list, select a filegroup on which to plac
e
>any text, image, and ntext columns.
>appreciate any advise
>tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||It is interesting to see on Profiler what Enterprise Manager is doing when
you follow that procedure. Among other commands is doing
-- to move mytable to myfilegroup
create table tmp_mytable ( ... ) on myfilegroup
insert into tmp_mytable (select ... from mytable)
drop table mytable
sp_rename tmp_mytable mytable
Ben Nevarez, MCDBA, OCP
Database Administrator
"maxzsim via webservertalk.com" wrote:
> Hi ,
> Is there any commands to place existing tables to a new file group beside
s
> using the EM as below ?
> How to place an existing table on a different filegroup (Enterprise Manage
r)
> To place an existing table on a different filegroup
> Expand a server group, and then expand a server.
> Expand Databases, expand the database in which the table belongs, and then
> click Tables.
> In the details pane, right-click the table, and then click Design Table.
> Right-click any column, and then click Properties.
> On the Tables tab, in the Table Filegroup list, select the filegroup on wh
ich
> to place the table.
> Optionally, in the Text Filegroup list, select a filegroup on which to pla
ce
> any text, image, and ntext columns.
> appreciate any advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200605/1
>|||tks
Kalen Delaney wrote:
>You can (re)create a clustered index for the table; the data will always
>move to the FG that the clustered index is built on
>
>[quoted text clipped - 20 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||hi ,
this is what i got from the profiler using the standard i.e no inclsion of
columns or new processes or any filter:
use [GL53_03_Bkup]
set implicit_transactions on SET TEXTSIZE 2147483647
sp_MStablechecks N'dbo.GL030106'
IF @.@.TRANCOUNT > 0 COMMIT TRAN
set implicit_transactions off SET TEXTSIZE 64512
use [GL53_03_Bkup]
select s1 = o.name, s2 = user_name(o.uid),o.crdate,o.id, N'SystemObj' = (cas
e
when (OBJECTPROPERTY(o.id, N'IsMSShipped')=1) then 1 else OBJECTPROPERTY(o.i
d,
N'IsSystemTable') end), o.category, 0, ObjectProperty(o.id,
N'TableHasActiveFulltextIndex'), ObjectProperty(o.id,
N'TableFulltextCatalogId'), N'FakeTable' = (case when (OBJECTPROPERTY(o.id,
N'tableisfake')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.i
d,
N'IsQuotedIdentOn')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.id,
N'IsAnsiNullsOn')=1) then 1 else 0 end) from dbo.sysobjects o, dbo.
sysindexes i where OBJECTPROPERTY(o.id, N'IsTable') = 1 and i.id = o.id and
i.
indid < 2 and o.name not like N'#%' and o.id = object_id(N'[dbo].[GL030106]
') order by s1, s2
SELECT N'Testing Connection...'
what can i actually make out of this ? or what other events i should be
included to see such changes ?
The table : Gl030106's original "Text FileGroup" is "PRIMARY" when i tried t
o
change it to my own FG : TEST2 and saved it goes back to PRIMARY
tks & rdgs
Ben Nevarez wrote:
>It is interesting to see on Profiler what Enterprise Manager is doing when
>you follow that procedure. Among other commands is doing
>-- to move mytable to myfilegroup
>create table tmp_mytable ( ... ) on myfilegroup
>insert into tmp_mytable (select ... from mytable)
>drop table mytable
>sp_rename tmp_mytable mytable
>Ben Nevarez, MCDBA, OCP
>Database Administrator
>
>[quoted text clipped - 17 lines]
Message posted via http://www.webservertalk.com
Is there any commands to place existing tables to a new file group besides
using the EM as below ?
How to place an existing table on a different filegroup (Enterprise Manager)
To place an existing table on a different filegroup
Expand a server group, and then expand a server.
Expand Databases, expand the database in which the table belongs, and then
click Tables.
In the details pane, right-click the table, and then click Design Table.
Right-click any column, and then click Properties.
On the Tables tab, in the Table Filegroup list, select the filegroup on whic
h
to place the table.
Optionally, in the Text Filegroup list, select a filegroup on which to place
any text, image, and ntext columns.
appreciate any advise
tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1You can (re)create a clustered index for the table; the data will always
move to the FG that the clustered index is built on
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"maxzsim via webservertalk.com" <u14644@.uwe> wrote in message
news:5ff96f8c4fdba@.uwe...
> Hi ,
> Is there any commands to place existing tables to a new file group besides
> using the EM as below ?
> How to place an existing table on a different filegroup (Enterprise
> Manager)
> To place an existing table on a different filegroup
> Expand a server group, and then expand a server.
> Expand Databases, expand the database in which the table belongs, and then
> click Tables.
> In the details pane, right-click the table, and then click Design Table.
> Right-click any column, and then click Properties.
> On the Tables tab, in the Table Filegroup list, select the filegroup on
> which
> to place the table.
> Optionally, in the Text Filegroup list, select a filegroup on which to
> place
> any text, image, and ntext columns.
> appreciate any advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||Hi ,
Another is for the Text FileGroup : i seem not able to change it to the new
filegroup it keeps turning back to the PRIMARY FileGroup even though i have
changed to a new filegorup and saved it
any possibly reason ?
tks & rdgs
maxzsim wrote:
>Hi ,
> Is there any commands to place existing tables to a new file group besides
>using the EM as below ?
>How to place an existing table on a different filegroup (Enterprise Manager
)
>To place an existing table on a different filegroup
>Expand a server group, and then expand a server.
>Expand Databases, expand the database in which the table belongs, and then
>click Tables.
>In the details pane, right-click the table, and then click Design Table.
>Right-click any column, and then click Properties.
>On the Tables tab, in the Table Filegroup list, select the filegroup on whi
ch
>to place the table.
>Optionally, in the Text Filegroup list, select a filegroup on which to plac
e
>any text, image, and ntext columns.
>appreciate any advise
>tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||It is interesting to see on Profiler what Enterprise Manager is doing when
you follow that procedure. Among other commands is doing
-- to move mytable to myfilegroup
create table tmp_mytable ( ... ) on myfilegroup
insert into tmp_mytable (select ... from mytable)
drop table mytable
sp_rename tmp_mytable mytable
Ben Nevarez, MCDBA, OCP
Database Administrator
"maxzsim via webservertalk.com" wrote:
> Hi ,
> Is there any commands to place existing tables to a new file group beside
s
> using the EM as below ?
> How to place an existing table on a different filegroup (Enterprise Manage
r)
> To place an existing table on a different filegroup
> Expand a server group, and then expand a server.
> Expand Databases, expand the database in which the table belongs, and then
> click Tables.
> In the details pane, right-click the table, and then click Design Table.
> Right-click any column, and then click Properties.
> On the Tables tab, in the Table Filegroup list, select the filegroup on wh
ich
> to place the table.
> Optionally, in the Text Filegroup list, select a filegroup on which to pla
ce
> any text, image, and ntext columns.
> appreciate any advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200605/1
>|||tks
Kalen Delaney wrote:
>You can (re)create a clustered index for the table; the data will always
>move to the FG that the clustered index is built on
>
>[quoted text clipped - 20 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||hi ,
this is what i got from the profiler using the standard i.e no inclsion of
columns or new processes or any filter:
use [GL53_03_Bkup]
set implicit_transactions on SET TEXTSIZE 2147483647
sp_MStablechecks N'dbo.GL030106'
IF @.@.TRANCOUNT > 0 COMMIT TRAN
set implicit_transactions off SET TEXTSIZE 64512
use [GL53_03_Bkup]
select s1 = o.name, s2 = user_name(o.uid),o.crdate,o.id, N'SystemObj' = (cas
e
when (OBJECTPROPERTY(o.id, N'IsMSShipped')=1) then 1 else OBJECTPROPERTY(o.i
d,
N'IsSystemTable') end), o.category, 0, ObjectProperty(o.id,
N'TableHasActiveFulltextIndex'), ObjectProperty(o.id,
N'TableFulltextCatalogId'), N'FakeTable' = (case when (OBJECTPROPERTY(o.id,
N'tableisfake')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.i
d,
N'IsQuotedIdentOn')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.id,
N'IsAnsiNullsOn')=1) then 1 else 0 end) from dbo.sysobjects o, dbo.
sysindexes i where OBJECTPROPERTY(o.id, N'IsTable') = 1 and i.id = o.id and
i.
indid < 2 and o.name not like N'#%' and o.id = object_id(N'[dbo].[GL030106]
') order by s1, s2
SELECT N'Testing Connection...'
what can i actually make out of this ? or what other events i should be
included to see such changes ?
The table : Gl030106's original "Text FileGroup" is "PRIMARY" when i tried t
o
change it to my own FG : TEST2 and saved it goes back to PRIMARY
tks & rdgs
Ben Nevarez wrote:
>It is interesting to see on Profiler what Enterprise Manager is doing when
>you follow that procedure. Among other commands is doing
>-- to move mytable to myfilegroup
>create table tmp_mytable ( ... ) on myfilegroup
>insert into tmp_mytable (select ... from mytable)
>drop table mytable
>sp_rename tmp_mytable mytable
>Ben Nevarez, MCDBA, OCP
>Database Administrator
>
>[quoted text clipped - 17 lines]
Message posted via http://www.webservertalk.com
Placing a table on multiple files
I have very two big tables and I want to put them on
multiple files within a file group. I know that a
filegroup can have multiple files,but I am not sure how
exactly the table spans across the files.
Is there any way that I can put the table on multiple
files?
Please provide me some examples.
Thanks for help.
RajIf you have multiple files in a filegroup, and create the table on that =filegroup then the table will automatically be allocated space across =the two files. This space will be taken as the table grows, space is =taken in the same ratio as the currently available free space within the =file.
What would be interesting is:
What do you mean by big?
What are you looking to achieve?
Are the two(or more) files all physically on different drives or merely =separate files on the same array.
The benefits you may see (or not see) are all affected by the above.
Mike John
"Raj" <vmresumes@.yahoo.com> wrote in message =news:909a01c345b3$0b4f6140$a401280a@.phx.gbl...
> I have very two big tables and I want to put them on > multiple files within a file group. I know that a > filegroup can have multiple files,but I am not sure how > exactly the table spans across the files.
> > Is there any way that I can put the table on multiple > files?
> > Please provide me some examples.
> > Thanks for help.
> > Raj|||Thanks for the response.
We have 2 fact tables each 10 GB. To gain the performance and aslo easy
management of files, I wanted to put on different physical disks.
Raj.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:OuDOvSdRDHA.2144@.TK2MSFTNGP11.phx.gbl...
If you have multiple files in a filegroup, and create the table on that
filegroup then the table will automatically be allocated space across the
two files. This space will be taken as the table grows, space is taken in
the same ratio as the currently available free space within the file.
What would be interesting is:
What do you mean by big?
What are you looking to achieve?
Are the two(or more) files all physically on different drives or merely
separate files on the same array.
The benefits you may see (or not see) are all affected by the above.
Mike John
"Raj" <vmresumes@.yahoo.com> wrote in message
news:909a01c345b3$0b4f6140$a401280a@.phx.gbl...
> I have very two big tables and I want to put them on
> multiple files within a file group. I know that a
> filegroup can have multiple files,but I am not sure how
> exactly the table spans across the files.
> Is there any way that I can put the table on multiple
> files?
> Please provide me some examples.
> Thanks for help.
> Raj|||Sounds very sensible then - sorry for the questions but you often get =people worrying about a huge (2Gb) table and then creating multiple =filegroups on the same raid 5 array, which all gets a bit pointless!
Mike
"Vish" <mocherla_v@.hotmail.com> wrote in message =news:eHjqdPeRDHA.2480@.tk2msftngp13.phx.gbl...
> Thanks for the response.
> > We have 2 fact tables each 10 GB. To gain the performance and aslo =easy
> management of files, I wanted to put on different physical disks.
> > Raj.
> > "Mike John" <Mike.John@.knowledgepool.com> wrote in message
> news:OuDOvSdRDHA.2144@.TK2MSFTNGP11.phx.gbl...
> If you have multiple files in a filegroup, and create the table on =that
> filegroup then the table will automatically be allocated space across =the
> two files. This space will be taken as the table grows, space is taken =in
> the same ratio as the currently available free space within the file.
> > What would be interesting is:
> > What do you mean by big?
> What are you looking to achieve?
> Are the two(or more) files all physically on different drives or =merely
> separate files on the same array.
> > The benefits you may see (or not see) are all affected by the above.
> > Mike John
> > "Raj" <vmresumes@.yahoo.com> wrote in message
> news:909a01c345b3$0b4f6140$a401280a@.phx.gbl...
> > I have very two big tables and I want to put them on
> > multiple files within a file group. I know that a
> > filegroup can have multiple files,but I am not sure how
> > exactly the table spans across the files.
> >
> > Is there any way that I can put the table on multiple
> > files?
> >
> > Please provide me some examples.
> >
> > Thanks for help.
> >
> > Raj
> > >=20
multiple files within a file group. I know that a
filegroup can have multiple files,but I am not sure how
exactly the table spans across the files.
Is there any way that I can put the table on multiple
files?
Please provide me some examples.
Thanks for help.
RajIf you have multiple files in a filegroup, and create the table on that =filegroup then the table will automatically be allocated space across =the two files. This space will be taken as the table grows, space is =taken in the same ratio as the currently available free space within the =file.
What would be interesting is:
What do you mean by big?
What are you looking to achieve?
Are the two(or more) files all physically on different drives or merely =separate files on the same array.
The benefits you may see (or not see) are all affected by the above.
Mike John
"Raj" <vmresumes@.yahoo.com> wrote in message =news:909a01c345b3$0b4f6140$a401280a@.phx.gbl...
> I have very two big tables and I want to put them on > multiple files within a file group. I know that a > filegroup can have multiple files,but I am not sure how > exactly the table spans across the files.
> > Is there any way that I can put the table on multiple > files?
> > Please provide me some examples.
> > Thanks for help.
> > Raj|||Thanks for the response.
We have 2 fact tables each 10 GB. To gain the performance and aslo easy
management of files, I wanted to put on different physical disks.
Raj.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:OuDOvSdRDHA.2144@.TK2MSFTNGP11.phx.gbl...
If you have multiple files in a filegroup, and create the table on that
filegroup then the table will automatically be allocated space across the
two files. This space will be taken as the table grows, space is taken in
the same ratio as the currently available free space within the file.
What would be interesting is:
What do you mean by big?
What are you looking to achieve?
Are the two(or more) files all physically on different drives or merely
separate files on the same array.
The benefits you may see (or not see) are all affected by the above.
Mike John
"Raj" <vmresumes@.yahoo.com> wrote in message
news:909a01c345b3$0b4f6140$a401280a@.phx.gbl...
> I have very two big tables and I want to put them on
> multiple files within a file group. I know that a
> filegroup can have multiple files,but I am not sure how
> exactly the table spans across the files.
> Is there any way that I can put the table on multiple
> files?
> Please provide me some examples.
> Thanks for help.
> Raj|||Sounds very sensible then - sorry for the questions but you often get =people worrying about a huge (2Gb) table and then creating multiple =filegroups on the same raid 5 array, which all gets a bit pointless!
Mike
"Vish" <mocherla_v@.hotmail.com> wrote in message =news:eHjqdPeRDHA.2480@.tk2msftngp13.phx.gbl...
> Thanks for the response.
> > We have 2 fact tables each 10 GB. To gain the performance and aslo =easy
> management of files, I wanted to put on different physical disks.
> > Raj.
> > "Mike John" <Mike.John@.knowledgepool.com> wrote in message
> news:OuDOvSdRDHA.2144@.TK2MSFTNGP11.phx.gbl...
> If you have multiple files in a filegroup, and create the table on =that
> filegroup then the table will automatically be allocated space across =the
> two files. This space will be taken as the table grows, space is taken =in
> the same ratio as the currently available free space within the file.
> > What would be interesting is:
> > What do you mean by big?
> What are you looking to achieve?
> Are the two(or more) files all physically on different drives or =merely
> separate files on the same array.
> > The benefits you may see (or not see) are all affected by the above.
> > Mike John
> > "Raj" <vmresumes@.yahoo.com> wrote in message
> news:909a01c345b3$0b4f6140$a401280a@.phx.gbl...
> > I have very two big tables and I want to put them on
> > multiple files within a file group. I know that a
> > filegroup can have multiple files,but I am not sure how
> > exactly the table spans across the files.
> >
> > Is there any way that I can put the table on multiple
> > files?
> >
> > Please provide me some examples.
> >
> > Thanks for help.
> >
> > Raj
> > >=20
Subscribe to:
Posts (Atom)