Multiple files/filegroups

Hi,
We have a database which has reached our disk capacity of 340GB and we
are looking at our options on how to resolve the disk space issue. The
problem is I'm not too familiar with setting up additional filegroups
and/or secondary files, or how they function.
The database consists of only one table which stores all our product
hi-res photography, each image being about 20MBs. The table is not
heavily accessed - either read or write - but it is our central storage
location for our studio photographs.
We could delete all images for discontinued product, but we would then
have to rely on a tape backup if we needed access to them in the
future.
Other options I think we have are to: -
1. Create a new database on a separate disk and have a scheduled
archive task to move any discontinued product images to a table in this
database. Place this DB on a separate disk. This means our 'live' db
would be much smaller and quicker to backup
2. Create a new filegroup for existing DB and create an archive table
on this group (file on different disk). Archive periodically and use a
view to join the two tables. Only perform regular database backups on
the 'live' filegroup
3. Create a secondary file on the same filegroup (primary) as the
'live' table and let the system start to fill this up with data as new
records are added. Downside of this is the backup size will not reduce.
On point 3. I'm not sure how this works - will the primary file start
to reduce in size, or will it remain at capacity and the secondary file
will start to increase in size? Is there something I can do to start
balancing the file sizes?
Also would be interested to know opinions on what the best approach
would be and if I have any misconceptions about the above options (or
if I've missed any)!
Thanks,
Andrew Duncan
Hi Tibor,
Thanks for the advice. Option 1 does seem the simplest, so I'll be
opting for that.
Rgds,
Andrew
Tibor Karaszi wrote:[vbcol=seagreen]
> Some thoughts:
>
> Sounds like a lot of manual work.
>
> This is probably what I would look for, but it require that your code adapts for this.
>
> You still need to adapt your code. But you now have a more complex backup strategy compared to 1. If
> you are on 2005, you can archive, set file group to read-only, backup the filegroup. If you now
> restore that filegroup backup, you don't need to restore all subsequent log backup. In 2000, you
> *do* need to restore all subsequent log backups, even if the file group was in read-only since the
> file group backup was performed.
>
> Space will be used from the file according to free space on each file. So you want to pre-allocate
> file size for the new file, and then shrink the old file to push data over to the new file (and then
> possibly increase the old file again so you have some free space).
> I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of the data when you
> restore but backup/restore is a bit more complex. 2 doesn't really buy you anything if you are on
> 2000.
> If you go for 1, you can get data from different point in time when you restore (as you have two
> databases).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <andrew@.duncanhcmc.com> wrote in message
> news:1163749909.367437.223570@.e3g2000cwe.googlegro ups.com...
sql

Multiple files/filegroups

Hi,
We have a database which has reached our disk capacity of 340GB and we
are looking at our options on how to resolve the disk space issue. The
problem is I'm not too familiar with setting up additional filegroups
and/or secondary files, or how they function.
The database consists of only one table which stores all our product
hi-res photography, each image being about 20MBs. The table is not
heavily accessed - either read or write - but it is our central storage
location for our studio photographs.
We could delete all images for discontinued product, but we would then
have to rely on a tape backup if we needed access to them in the
future.
Other options I think we have are to: -
1. Create a new database on a separate disk and have a scheduled
archive task to move any discontinued product images to a table in this
database. Place this DB on a separate disk. This means our 'live' db
would be much smaller and quicker to backup
2. Create a new filegroup for existing DB and create an archive table
on this group (file on different disk). Archive periodically and use a
view to join the two tables. Only perform regular database backups on
the 'live' filegroup
3. Create a secondary file on the same filegroup (primary) as the
'live' table and let the system start to fill this up with data as new
records are added. Downside of this is the backup size will not reduce.
On point 3. I'm not sure how this works - will the primary file start
to reduce in size, or will it remain at capacity and the secondary file
will start to increase in size? Is there something I can do to start
balancing the file sizes?
Also would be interested to know opinions on what the best approach
would be and if I have any misconceptions about the above options (or
if I've missed any)!
Thanks,
Andrew DuncanSome thoughts:
> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
Sounds like a lot of manual work.
> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
This is probably what I would look for, but it require that your code adapts for this.
> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
You still need to adapt your code. But you now have a more complex backup strategy compared to 1. If
you are on 2005, you can archive, set file group to read-only, backup the filegroup. If you now
restore that filegroup backup, you don't need to restore all subsequent log backup. In 2000, you
*do* need to restore all subsequent log backups, even if the file group was in read-only since the
file group backup was performed.
> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
Space will be used from the file according to free space on each file. So you want to pre-allocate
file size for the new file, and then shrink the old file to push data over to the new file (and then
possibly increase the old file again so you have some free space).
I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of the data when you
restore but backup/restore is a bit more complex. 2 doesn't really buy you anything if you are on
2000.
If you go for 1, you can get data from different point in time when you restore (as you have two
databases).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<andrew@.duncanhcmc.com> wrote in message
news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...
> Hi,
> We have a database which has reached our disk capacity of 340GB and we
> are looking at our options on how to resolve the disk space issue. The
> problem is I'm not too familiar with setting up additional filegroups
> and/or secondary files, or how they function.
> The database consists of only one table which stores all our product
> hi-res photography, each image being about 20MBs. The table is not
> heavily accessed - either read or write - but it is our central storage
> location for our studio photographs.
> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
> Other options I think we have are to: -
> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
> Also would be interested to know opinions on what the best approach
> would be and if I have any misconceptions about the above options (or
> if I've missed any)!
> Thanks,
> Andrew Duncan
>|||Hi Tibor,
Thanks for the advice. Option 1 does seem the simplest, so I'll be
opting for that.
Rgds,
Andrew
Tibor Karaszi wrote:
> Some thoughts:
> > We could delete all images for discontinued product, but we would then
> > have to rely on a tape backup if we needed access to them in the
> > future.
> Sounds like a lot of manual work.
>
> > 1. Create a new database on a separate disk and have a scheduled
> > archive task to move any discontinued product images to a table in this
> > database. Place this DB on a separate disk. This means our 'live' db
> > would be much smaller and quicker to backup
> This is probably what I would look for, but it require that your code adapts for this.
>
> > 2. Create a new filegroup for existing DB and create an archive table
> > on this group (file on different disk). Archive periodically and use a
> > view to join the two tables. Only perform regular database backups on
> > the 'live' filegroup
> You still need to adapt your code. But you now have a more complex backup strategy compared to 1. If
> you are on 2005, you can archive, set file group to read-only, backup the filegroup. If you now
> restore that filegroup backup, you don't need to restore all subsequent log backup. In 2000, you
> *do* need to restore all subsequent log backups, even if the file group was in read-only since the
> file group backup was performed.
>
> > 3. Create a secondary file on the same filegroup (primary) as the
> > 'live' table and let the system start to fill this up with data as new
> > records are added. Downside of this is the backup size will not reduce.
> > On point 3. I'm not sure how this works - will the primary file start
> > to reduce in size, or will it remain at capacity and the secondary file
> > will start to increase in size? Is there something I can do to start
> > balancing the file sizes?
> Space will be used from the file according to free space on each file. So you want to pre-allocate
> file size for the new file, and then shrink the old file to push data over to the new file (and then
> possibly increase the old file again so you have some free space).
> I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of the data when you
> restore but backup/restore is a bit more complex. 2 doesn't really buy you anything if you are on
> 2000.
> If you go for 1, you can get data from different point in time when you restore (as you have two
> databases).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <andrew@.duncanhcmc.com> wrote in message
> news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...
> > Hi,
> >
> > We have a database which has reached our disk capacity of 340GB and we
> > are looking at our options on how to resolve the disk space issue. The
> > problem is I'm not too familiar with setting up additional filegroups
> > and/or secondary files, or how they function.
> >
> > The database consists of only one table which stores all our product
> > hi-res photography, each image being about 20MBs. The table is not
> > heavily accessed - either read or write - but it is our central storage
> > location for our studio photographs.
> >
> > We could delete all images for discontinued product, but we would then
> > have to rely on a tape backup if we needed access to them in the
> > future.
> >
> > Other options I think we have are to: -
> >
> > 1. Create a new database on a separate disk and have a scheduled
> > archive task to move any discontinued product images to a table in this
> > database. Place this DB on a separate disk. This means our 'live' db
> > would be much smaller and quicker to backup
> >
> > 2. Create a new filegroup for existing DB and create an archive table
> > on this group (file on different disk). Archive periodically and use a
> > view to join the two tables. Only perform regular database backups on
> > the 'live' filegroup
> >
> > 3. Create a secondary file on the same filegroup (primary) as the
> > 'live' table and let the system start to fill this up with data as new
> > records are added. Downside of this is the backup size will not reduce.
> >
> > On point 3. I'm not sure how this works - will the primary file start
> > to reduce in size, or will it remain at capacity and the secondary file
> > will start to increase in size? Is there something I can do to start
> > balancing the file sizes?
> >
> > Also would be interested to know opinions on what the best approach
> > would be and if I have any misconceptions about the above options (or
> > if I've missed any)!
> >
> > Thanks,
> >
> > Andrew Duncan
> >

Multiple files/filegroups

Hi,
We have a database which has reached our disk capacity of 340GB and we
are looking at our options on how to resolve the disk space issue. The
problem is I'm not too familiar with setting up additional filegroups
and/or secondary files, or how they function.
The database consists of only one table which stores all our product
hi-res photography, each image being about 20MBs. The table is not
heavily accessed - either read or write - but it is our central storage
location for our studio photographs.
We could delete all images for discontinued product, but we would then
have to rely on a tape backup if we needed access to them in the
future.
Other options I think we have are to: -
1. Create a new database on a separate disk and have a scheduled
archive task to move any discontinued product images to a table in this
database. Place this DB on a separate disk. This means our 'live' db
would be much smaller and quicker to backup
2. Create a new filegroup for existing DB and create an archive table
on this group (file on different disk). Archive periodically and use a
view to join the two tables. Only perform regular database backups on
the 'live' filegroup
3. Create a secondary file on the same filegroup (primary) as the
'live' table and let the system start to fill this up with data as new
records are added. Downside of this is the backup size will not reduce.
On point 3. I'm not sure how this works - will the primary file start
to reduce in size, or will it remain at capacity and the secondary file
will start to increase in size? Is there something I can do to start
balancing the file sizes?
Also would be interested to know opinions on what the best approach
would be and if I have any misconceptions about the above options (or
if I've missed any)!
Thanks,
Andrew DuncanSome thoughts:

> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
Sounds like a lot of manual work.

> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
This is probably what I would look for, but it require that your code adapts
for this.

> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
You still need to adapt your code. But you now have a more complex backup st
rategy compared to 1. If
you are on 2005, you can archive, set file group to read-only, backup the fi
legroup. If you now
restore that filegroup backup, you don't need to restore all subsequent log
backup. In 2000, you
*do* need to restore all subsequent log backups, even if the file group was
in read-only since the
file group backup was performed.

> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
Space will be used from the file according to free space on each file. So yo
u want to pre-allocate
file size for the new file, and then shrink the old file to push data over t
o the new file (and then
possibly increase the old file again so you have some free space).
I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all of
the data when you
restore but backup/restore is a bit more complex. 2 doesn't really buy you a
nything if you are on
2000.
If you go for 1, you can get data from different point in time when you rest
ore (as you have two
databases).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<andrew@.duncanhcmc.com> wrote in message
news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...
> Hi,
> We have a database which has reached our disk capacity of 340GB and we
> are looking at our options on how to resolve the disk space issue. The
> problem is I'm not too familiar with setting up additional filegroups
> and/or secondary files, or how they function.
> The database consists of only one table which stores all our product
> hi-res photography, each image being about 20MBs. The table is not
> heavily accessed - either read or write - but it is our central storage
> location for our studio photographs.
> We could delete all images for discontinued product, but we would then
> have to rely on a tape backup if we needed access to them in the
> future.
> Other options I think we have are to: -
> 1. Create a new database on a separate disk and have a scheduled
> archive task to move any discontinued product images to a table in this
> database. Place this DB on a separate disk. This means our 'live' db
> would be much smaller and quicker to backup
> 2. Create a new filegroup for existing DB and create an archive table
> on this group (file on different disk). Archive periodically and use a
> view to join the two tables. Only perform regular database backups on
> the 'live' filegroup
> 3. Create a secondary file on the same filegroup (primary) as the
> 'live' table and let the system start to fill this up with data as new
> records are added. Downside of this is the backup size will not reduce.
> On point 3. I'm not sure how this works - will the primary file start
> to reduce in size, or will it remain at capacity and the secondary file
> will start to increase in size? Is there something I can do to start
> balancing the file sizes?
> Also would be interested to know opinions on what the best approach
> would be and if I have any misconceptions about the above options (or
> if I've missed any)!
> Thanks,
> Andrew Duncan
>|||Hi Tibor,
Thanks for the advice. Option 1 does seem the simplest, so I'll be
opting for that.
Rgds,
Andrew
Tibor Karaszi wrote:[vbcol=seagreen]
> Some thoughts:
>
> Sounds like a lot of manual work.
>
> This is probably what I would look for, but it require that your code adap
ts for this.
>
> You still need to adapt your code. But you now have a more complex backup
strategy compared to 1. If
> you are on 2005, you can archive, set file group to read-only, backup the
filegroup. If you now
> restore that filegroup backup, you don't need to restore all subsequent lo
g backup. In 2000, you
> *do* need to restore all subsequent log backups, even if the file group wa
s in read-only since the
> file group backup was performed.
>
> Space will be used from the file according to free space on each file. So
you want to pre-allocate
> file size for the new file, and then shrink the old file to push data over
to the new file (and then
> possibly increase the old file again so you have some free space).
> I'd go for 1 or 2. If you go for 2, you will always get a snapshot of all
of the data when you
> restore but backup/restore is a bit more complex. 2 doesn't really buy you
anything if you are on
> 2000.
> If you go for 1, you can get data from different point in time when you re
store (as you have two
> databases).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <andrew@.duncanhcmc.com> wrote in message
> news:1163749909.367437.223570@.e3g2000cwe.googlegroups.com...

Multiple Filegroups.

Hi Everyone,
I added a secondary filegroup to a production database and moved some tables
to it from the Primary Filegroup.In the process I changed the database
recovery model to Simple and back to Full,backed up the Transaction Log and
shrunk it,shrunk the primary filegroup datafile. The database recovery model
is Full at the moment. I have noticed 2 oddities:
1. The Transaction Log appears to grow much slower than before(only 2 - 5 MB
a day,the database is being used as usual)
2. sp_spaceused and Enterprise Manager Console report strange values for
database available space:
db_name size unallocated_space
DB_LIVE 55812.06 MB -4257.63 MB
reserved data index_size unused
61507528 KB 61203424 KB 291632 KB 12472 KB
Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE' or
is there somethig else I have to do?
Best Regards.I have another question. I took a Full Backup of the database before adding
the secondary filegroup(single filegroup database) , the backup file size is
about 55,4 GB.
Today I backed up the Primary Filegroup and the newly added Filegroup1. The
backup file size for the Primary filegroup is about 45 GB and backup file
size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
As far as I know no data has been deleted. Is there any explanation
regarding the cause of the total size reduction ?
Regards.
Sezgin Rafet
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> Hi Everyone,
> I added a secondary filegroup to a production database and moved some
> tables to it from the Primary Filegroup.In the process I changed the
> database recovery model to Simple and back to Full,backed up the
> Transaction Log and shrunk it,shrunk the primary filegroup datafile. The
> database recovery model is Full at the moment. I have noticed 2 oddities:
> 1. The Transaction Log appears to grow much slower than before(only 2 - 5
> MB a day,the database is being used as usual)
> 2. sp_spaceused and Enterprise Manager Console report strange values for
> database available space:
> db_name size unallocated_space
> DB_LIVE 55812.06 MB -4257.63 MB
> reserved data index_size
> unused
> 61507528 KB 61203424 KB 291632 KB 12472 KB
>
> Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> or is there somethig else I have to do?
> Best Regards.
>|||Hi
You don't say how you moved the tables, I assumed that you re-created the
clustered index in the new filegroup, in which case it would have been
re-built. If you had not defragged the index for a while then this may be
where the space has been found. You may want to look at what fill factors yo
u
are using or whether the clustered index is suitable.
John
"Sezgin Rafet" wrote:

> I have another question. I took a Full Backup of the database before addin
g
> the secondary filegroup(single filegroup database) , the backup file size
is
> about 55,4 GB.
> Today I backed up the Primary Filegroup and the newly added Filegroup1. Th
e
> backup file size for the Primary filegroup is about 45 GB and backup file
> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
> As far as I know no data has been deleted. Is there any explanation
> regarding the cause of the total size reduction ?
> Regards.
> Sezgin Rafet
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
>
>|||Hello John,
Thanks for the reply.
I moved the tables using Enterprise Manager -> Design Table -> Properties ->
Selected Filegroup1 as the Table Filegroup -> Close -> Save
All the tables I moved this way have the same clustered index(the tables
have the same structuce,each table holds 1 month's worth of data) , so I
presume the index was rebuit for each table(my knowledge about indexing is
limited).We don't do anything regarding index defragmentation , thanks to
you it is only now that I realize it is an important issue to us.
In the beginning of each month a new data table is created(programmaticaly
by a custom Windows Service).
Data is fed into the table at very high rate from a SCADA system. Older data
tables are not modified.
Last month's data table has about 70 000 000 rows and is about 6.3 GB in
size.
As each new data table is created I will move the oldest data table to the
secondary filegroup.
I intend to keep 2 data tables in the Primary Filegroup(it is the Default
Filegroup as well).
Clustered Index Fill Factor is 0 % - is this a good choice ?.
What would be the best method to defragment the clustered index ? (This may
be no longer necessary , because when moving the tables it will be rebilt.
Any data table will be having data changes for only a period of 1 month)
What would be the advantage of using a non-clustered index in our case ,
would the query performance loss be acceptable ?
Best Regards.
Sezgin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...[vbcol=seagreen]
> Hi
> You don't say how you moved the tables, I assumed that you re-created the
> clustered index in the new filegroup, in which case it would have been
> re-built. If you had not defragged the index for a while then this may be
> where the space has been found. You may want to look at what fill factors
> you
> are using or whether the clustered index is suitable.
> John
> "Sezgin Rafet" wrote:
>|||Hi
I don't know what commands EM uses to move tables, but at a guess it creates
a new table, sucks the data into from the old table and then renames the new
table after dropping it. This would mean that any clustered index would need
to be in the same filegroup. Without knowing what your indexes are it is har
d
to comment on whether the fill factor is good or whether you would benefit
from a different from a different clustered index or even if your system
would work better without one (if speed of inserts is more important than
speed of retrieval).
You may want to consider changing your processing to load into a "standard"
set of tables, then do a monthly archive process into a new table (which is
basically what I think EM does for you). This could be automated and you the
n
have the choice of having different indexes on the load table to the archive
d
data; you would also be able to build the indexes on the archived data at th
e
end of the load process so that they would be fully optimized and if no data
changes are made they could have 100% fill factor. If this data is rarely
accessed you may even consider archiving it to a separate database.
For defragging look at the topic DBCC SHOWCONTIG in Books Online and there
is an example script you can use as a starting point for any manual
maintenance task. You can use DBCC DBREINDEX instead of DBCC INDEXDEFRAG if
you want a defragment the whole index. Alternatively look at the database
maintenance wizard and set up a job which will create a maintenance plan for
your database(s).
HTH
John
"Sezgin Rafet" wrote:

> Hello John,
> Thanks for the reply.
> I moved the tables using Enterprise Manager -> Design Table -> Properties
->
> Selected Filegroup1 as the Table Filegroup -> Close -> Save
> All the tables I moved this way have the same clustered index(the tables
> have the same structuce,each table holds 1 month's worth of data) , so I
> presume the index was rebuit for each table(my knowledge about indexing is
> limited).We don't do anything regarding index defragmentation , thanks to
> you it is only now that I realize it is an important issue to us.
> In the beginning of each month a new data table is created(programmaticaly
> by a custom Windows Service).
> Data is fed into the table at very high rate from a SCADA system. Older da
ta
> tables are not modified.
> Last month's data table has about 70 000 000 rows and is about 6.3 GB in
> size.
> As each new data table is created I will move the oldest data table to the
> secondary filegroup.
> I intend to keep 2 data tables in the Primary Filegroup(it is the Default
> Filegroup as well).
> Clustered Index Fill Factor is 0 % - is this a good choice ?.
> What would be the best method to defragment the clustered index ? (This ma
y
> be no longer necessary , because when moving the tables it will be rebilt.
> Any data table will be having data changes for only a period of 1 month)
> What would be the advantage of using a non-clustered index in our case ,
> would the query performance loss be acceptable ?
> Best Regards.
>
> Sezgin
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...
>
>

Multiple Filegroups.

Hi Everyone,
I added a secondary filegroup to a production database and moved some tables
to it from the Primary Filegroup.In the process I changed the database
recovery model to Simple and back to Full,backed up the Transaction Log and
shrunk it,shrunk the primary filegroup datafile. The database recovery model
is Full at the moment. I have noticed 2 oddities:
1. The Transaction Log appears to grow much slower than before(only 2 - 5 MB
a day,the database is being used as usual)
2. sp_spaceused and Enterprise Manager Console report strange values for
database available space:
db_name size unallocated_space
DB_LIVE 55812.06 MB -4257.63 MB
reserved data index_size unused
61507528 KB 61203424 KB 291632 KB 12472 KB
Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE' or
is there somethig else I have to do?
Best Regards.I have another question. I took a Full Backup of the database before adding
the secondary filegroup(single filegroup database) , the backup file size is
about 55,4 GB.
Today I backed up the Primary Filegroup and the newly added Filegroup1. The
backup file size for the Primary filegroup is about 45 GB and backup file
size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
As far as I know no data has been deleted. Is there any explanation
regarding the cause of the total size reduction ?
Regards.
Sezgin Rafet
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> Hi Everyone,
> I added a secondary filegroup to a production database and moved some
> tables to it from the Primary Filegroup.In the process I changed the
> database recovery model to Simple and back to Full,backed up the
> Transaction Log and shrunk it,shrunk the primary filegroup datafile. The
> database recovery model is Full at the moment. I have noticed 2 oddities:
> 1. The Transaction Log appears to grow much slower than before(only 2 - 5
> MB a day,the database is being used as usual)
> 2. sp_spaceused and Enterprise Manager Console report strange values for
> database available space:
> db_name size unallocated_space
> DB_LIVE 55812.06 MB -4257.63 MB
> reserved data index_size
> unused
> 61507528 KB 61203424 KB 291632 KB 12472 KB
>
> Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> or is there somethig else I have to do?
> Best Regards.
>|||Hi
You don't say how you moved the tables, I assumed that you re-created the
clustered index in the new filegroup, in which case it would have been
re-built. If you had not defragged the index for a while then this may be
where the space has been found. You may want to look at what fill factors you
are using or whether the clustered index is suitable.
John
"Sezgin Rafet" wrote:
> I have another question. I took a Full Backup of the database before adding
> the secondary filegroup(single filegroup database) , the backup file size is
> about 55,4 GB.
> Today I backed up the Primary Filegroup and the newly added Filegroup1. The
> backup file size for the Primary filegroup is about 45 GB and backup file
> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51 GB.
> As far as I know no data has been deleted. Is there any explanation
> regarding the cause of the total size reduction ?
> Regards.
> Sezgin Rafet
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> > Hi Everyone,
> >
> > I added a secondary filegroup to a production database and moved some
> > tables to it from the Primary Filegroup.In the process I changed the
> > database recovery model to Simple and back to Full,backed up the
> > Transaction Log and shrunk it,shrunk the primary filegroup datafile. The
> > database recovery model is Full at the moment. I have noticed 2 oddities:
> >
> > 1. The Transaction Log appears to grow much slower than before(only 2 - 5
> > MB a day,the database is being used as usual)
> >
> > 2. sp_spaceused and Enterprise Manager Console report strange values for
> > database available space:
> >
> > db_name size unallocated_space
> > DB_LIVE 55812.06 MB -4257.63 MB
> >
> > reserved data index_size
> > unused
> > 61507528 KB 61203424 KB 291632 KB 12472 KB
> >
> >
> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> > or is there somethig else I have to do?
> >
> > Best Regards.
> >
> >
>
>|||Hello John,
Thanks for the reply.
I moved the tables using Enterprise Manager -> Design Table -> Properties ->
Selected Filegroup1 as the Table Filegroup -> Close -> Save
All the tables I moved this way have the same clustered index(the tables
have the same structuce,each table holds 1 month's worth of data) , so I
presume the index was rebuit for each table(my knowledge about indexing is
limited).We don't do anything regarding index defragmentation , thanks to
you it is only now that I realize it is an important issue to us.
In the beginning of each month a new data table is created(programmaticaly
by a custom Windows Service).
Data is fed into the table at very high rate from a SCADA system. Older data
tables are not modified.
Last month's data table has about 70 000 000 rows and is about 6.3 GB in
size.
As each new data table is created I will move the oldest data table to the
secondary filegroup.
I intend to keep 2 data tables in the Primary Filegroup(it is the Default
Filegroup as well).
Clustered Index Fill Factor is 0 % - is this a good choice ?.
What would be the best method to defragment the clustered index ? (This may
be no longer necessary , because when moving the tables it will be rebilt.
Any data table will be having data changes for only a period of 1 month)
What would be the advantage of using a non-clustered index in our case ,
would the query performance loss be acceptable ?
Best Regards.
Sezgin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...
> Hi
> You don't say how you moved the tables, I assumed that you re-created the
> clustered index in the new filegroup, in which case it would have been
> re-built. If you had not defragged the index for a while then this may be
> where the space has been found. You may want to look at what fill factors
> you
> are using or whether the clustered index is suitable.
> John
> "Sezgin Rafet" wrote:
>> I have another question. I took a Full Backup of the database before
>> adding
>> the secondary filegroup(single filegroup database) , the backup file size
>> is
>> about 55,4 GB.
>> Today I backed up the Primary Filegroup and the newly added Filegroup1.
>> The
>> backup file size for the Primary filegroup is about 45 GB and backup file
>> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51
>> GB.
>> As far as I know no data has been deleted. Is there any explanation
>> regarding the cause of the total size reduction ?
>> Regards.
>> Sezgin Rafet
>>
>> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
>> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
>> > Hi Everyone,
>> >
>> > I added a secondary filegroup to a production database and moved some
>> > tables to it from the Primary Filegroup.In the process I changed the
>> > database recovery model to Simple and back to Full,backed up the
>> > Transaction Log and shrunk it,shrunk the primary filegroup datafile.
>> > The
>> > database recovery model is Full at the moment. I have noticed 2
>> > oddities:
>> >
>> > 1. The Transaction Log appears to grow much slower than before(only 2 -
>> > 5
>> > MB a day,the database is being used as usual)
>> >
>> > 2. sp_spaceused and Enterprise Manager Console report strange values
>> > for
>> > database available space:
>> >
>> > db_name size unallocated_space
>> > DB_LIVE 55812.06 MB -4257.63 MB
>> >
>> > reserved data index_size
>> > unused
>> > 61507528 KB 61203424 KB 291632 KB 12472 KB
>> >
>> >
>> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
>> > or is there somethig else I have to do?
>> >
>> > Best Regards.
>> >
>> >
>>|||Hi
I don't know what commands EM uses to move tables, but at a guess it creates
a new table, sucks the data into from the old table and then renames the new
table after dropping it. This would mean that any clustered index would need
to be in the same filegroup. Without knowing what your indexes are it is hard
to comment on whether the fill factor is good or whether you would benefit
from a different from a different clustered index or even if your system
would work better without one (if speed of inserts is more important than
speed of retrieval).
You may want to consider changing your processing to load into a "standard"
set of tables, then do a monthly archive process into a new table (which is
basically what I think EM does for you). This could be automated and you then
have the choice of having different indexes on the load table to the archived
data; you would also be able to build the indexes on the archived data at the
end of the load process so that they would be fully optimized and if no data
changes are made they could have 100% fill factor. If this data is rarely
accessed you may even consider archiving it to a separate database.
For defragging look at the topic DBCC SHOWCONTIG in Books Online and there
is an example script you can use as a starting point for any manual
maintenance task. You can use DBCC DBREINDEX instead of DBCC INDEXDEFRAG if
you want a defragment the whole index. Alternatively look at the database
maintenance wizard and set up a job which will create a maintenance plan for
your database(s).
HTH
John
"Sezgin Rafet" wrote:
> Hello John,
> Thanks for the reply.
> I moved the tables using Enterprise Manager -> Design Table -> Properties ->
> Selected Filegroup1 as the Table Filegroup -> Close -> Save
> All the tables I moved this way have the same clustered index(the tables
> have the same structuce,each table holds 1 month's worth of data) , so I
> presume the index was rebuit for each table(my knowledge about indexing is
> limited).We don't do anything regarding index defragmentation , thanks to
> you it is only now that I realize it is an important issue to us.
> In the beginning of each month a new data table is created(programmaticaly
> by a custom Windows Service).
> Data is fed into the table at very high rate from a SCADA system. Older data
> tables are not modified.
> Last month's data table has about 70 000 000 rows and is about 6.3 GB in
> size.
> As each new data table is created I will move the oldest data table to the
> secondary filegroup.
> I intend to keep 2 data tables in the Primary Filegroup(it is the Default
> Filegroup as well).
> Clustered Index Fill Factor is 0 % - is this a good choice ?.
> What would be the best method to defragment the clustered index ? (This may
> be no longer necessary , because when moving the tables it will be rebilt.
> Any data table will be having data changes for only a period of 1 month)
> What would be the advantage of using a non-clustered index in our case ,
> would the query performance loss be acceptable ?
> Best Regards.
>
> Sezgin
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:86C16903-A902-4706-951C-D5E25E7C68C2@.microsoft.com...
> > Hi
> >
> > You don't say how you moved the tables, I assumed that you re-created the
> > clustered index in the new filegroup, in which case it would have been
> > re-built. If you had not defragged the index for a while then this may be
> > where the space has been found. You may want to look at what fill factors
> > you
> > are using or whether the clustered index is suitable.
> >
> > John
> >
> > "Sezgin Rafet" wrote:
> >
> >> I have another question. I took a Full Backup of the database before
> >> adding
> >> the secondary filegroup(single filegroup database) , the backup file size
> >> is
> >> about 55,4 GB.
> >> Today I backed up the Primary Filegroup and the newly added Filegroup1.
> >> The
> >> backup file size for the Primary filegroup is about 45 GB and backup file
> >> size for the Filegroup1 filegroup is about 6 GB and that adds up to 51
> >> GB.
> >> As far as I know no data has been deleted. Is there any explanation
> >> regarding the cause of the total size reduction ?
> >>
> >> Regards.
> >>
> >> Sezgin Rafet
> >>
> >>
> >>
> >> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> >> news:OnAw0z1aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> >> > Hi Everyone,
> >> >
> >> > I added a secondary filegroup to a production database and moved some
> >> > tables to it from the Primary Filegroup.In the process I changed the
> >> > database recovery model to Simple and back to Full,backed up the
> >> > Transaction Log and shrunk it,shrunk the primary filegroup datafile.
> >> > The
> >> > database recovery model is Full at the moment. I have noticed 2
> >> > oddities:
> >> >
> >> > 1. The Transaction Log appears to grow much slower than before(only 2 -
> >> > 5
> >> > MB a day,the database is being used as usual)
> >> >
> >> > 2. sp_spaceused and Enterprise Manager Console report strange values
> >> > for
> >> > database available space:
> >> >
> >> > db_name size unallocated_space
> >> > DB_LIVE 55812.06 MB -4257.63 MB
> >> >
> >> > reserved data index_size
> >> > unused
> >> > 61507528 KB 61203424 KB 291632 KB 12472 KB
> >> >
> >> >
> >> > Do I need to run DBCC UPDATEUSAGE or sp_spaceused @.updateusage = 'TRUE'
> >> > or is there somethig else I have to do?
> >> >
> >> > Best Regards.
> >> >
> >> >
> >>
> >>
> >>
>
>

Multiple Filegroups

Hi experts,

I'm new to SQLServer 2000 and would like some advice on filegroups.

Is there any advantage to seperate the filegroups for different type of data.

For example:

Data_1 for tables
Index_1 for Indexes
Audit_1 for Audit tables

The files for these filegroups would be placed on RAID Disk.

Thanks for any suggestions.Any comments?

I need to know whether there are any performance gain in seperating types of data in different filegroups or is it just good enough to set it to a default secondary filegroup for user data objects.

Please comment.|||Hi,
you can save a lot of system time if you are able to store smart your tables, indexes, ....
This is part from SQL books:

Placing Tables on Filegroups

A table can be created on a specific filegroup rather than the default filegroup. If the filegroup comprises multiple files spread across various physical disks, each with its own disk controller, then queries for data from the table will be spread across the disks, thereby improving performance. The same effect can be accomplished by creating a single file on a RAID (redundant array of independent disks) level 0, 1, or 5 device.

If the computer has multiple processors, Microsoft? SQL Server? 2000 can perform parallel scans of the data. Multiple parallel scans can be executed for a single table regardless of the number of files that are in its filegroup. Additionally, any text, ntext, or image columns within a table can be created on a filegroup other than the one that contains the base table.

Eventually, there is a saturation point when there are too many outstanding I/O's causing bottlenecks in the disk I/O subsystem. These bottlenecks can be identified by using Windows NT? Performance Monitor to monitor the PhysicalDisk object and Disk Queue Length counter. If the Disk Queue Length counter is greater than three, consider spreading the file across more disk drives. For more information, see Monitoring Disk Activity.

It is advantageous to get as much data spread across as many physical drives as possible in order to improve throughput through parallel data access. To spread data evenly across all disks, you can place a single file across striped disks or maintain each disk separately and place a file on each disk.

I hope it will help you. It's very good text.
Bye

|||No, there is no appreciable gain. Filegroups are mainly for ease of administration across multiple volumes, not performance.sql

Multiple Filegroups

When dealing with a vldb, multiple filegroups are obviously a necessity from
an admin point of view.. ..but from a performance p.o.v. is there any point
at which you can have too many filegroups? - Will a high number of filegroups
on a single array actually degrade performance?
I'm using sql 2005, and I'm looking at partitioning a table, the obvious
column to partition on would mean the table would end up on 100+ filegroups..
...this would mean we would have 30+ filegroups per array and I'm worried this
would cause us more problems than benefits..
TIA BenUK
Depands on what kind of RAID structure you use/Have.
SQL2005 is Scalable enough to give you the freedom of partitioning.
you can place diff accessed table on diff Raid(if available) Static
Tables ca be on RAID 5 and/or non clustered indexes can be on a RAID5
is they re static , i they are highly updated use RAID1.
Distribution of Filegroups according to their access patterns and
appropriately using RAID's, shoule be the main concern.
Maninder
MCDBA

Multiple Filegroups

When dealing with a vldb, multiple filegroups are obviously a necessity from
an admin point of view.. ..but from a performance p.o.v. is there any point
at which you can have too many filegroups? - Will a high number of filegroups
on a single array actually degrade performance?
I'm using sql 2005, and I'm looking at partitioning a table, the obvious
column to partition on would mean the table would end up on 100+ filegroups..
..this would mean we would have 30+ filegroups per array and I'm worried this
would cause us more problems than benefits..
TIA BenUKDepands on what kind of RAID structure you use/Have.
SQL2005 is Scalable enough to give you the freedom of partitioning.
you can place diff accessed table on diff Raid(if available) Static
Tables ca be on RAID 5 and/or non clustered indexes can be on a RAID5
is they re static , i they are highly updated use RAID1.
Distribution of Filegroups according to their access patterns and
appropriately using RAID's, shoule be the main concern.
Maninder
MCDBA

Multiple Filegroups

When dealing with a vldb, multiple filegroups are obviously a necessity from
an admin point of view.. ..but from a performance p.o.v. is there any point
at which you can have too many filegroups? - Will a high number of filegroup
s
on a single array actually degrade performance?
I'm using sql 2005, and I'm looking at partitioning a table, the obvious
column to partition on would mean the table would end up on 100+ filegroups.
.
..this would mean we would have 30+ filegroups per array and I'm worried th
is
would cause us more problems than benefits..
TIA BenUKDepands on what kind of RAID structure you use/Have.
SQL2005 is Scalable enough to give you the freedom of partitioning.
you can place diff accessed table on diff Raid(if available) Static
Tables ca be on RAID 5 and/or non clustered indexes can be on a RAID5
is they re static , i they are highly updated use RAID1.
Distribution of Filegroups according to their access patterns and
appropriately using RAID's, shoule be the main concern.
Maninder
MCDBA

Multiple Databases/One SQL Server instance

We would like to put several production databases (approx. 8) on one
sql server instance.
Each database has 3 filegroups(primary, index, log), what is the best
way to set up these files on the server? We will have 3 or 4 drives.
Is it best to spread the log files across the different drives, or keep
the log files all on one drive etc?
Hi
What do you mean by 3 or 4 drives? If these are physically separate drives
then if you want resilience then you would probably be best of mirroring
them, therefore leaving you with two pairs of which log files can go on one
pair and data files can go on the other pair. If you want more resilience
then you would need a minimum of 3 discs for Raid 5 and 4 discs for Raid 10.
You may want to read more on raid such as at
http://msdn2.microsoft.com/en-us/library/ms190764.aspx and
http://www.acnc.com/04_01_10.html
As the type of I/O for log files is different to that of the data files you
are best keeping them separate. If you subsequently have I/O problems then
you will need to analyse what is causing this, and possibly move one or more
filegroups and their data files onto their own set of discs.
John
"Jayme" wrote:

> We would like to put several production databases (approx. 8) on one
> sql server instance.
> Each database has 3 filegroups(primary, index, log), what is the best
> way to set up these files on the server? We will have 3 or 4 drives.
> Is it best to spread the log files across the different drives, or keep
> the log files all on one drive etc?
>

Multiple Databases/One SQL Server instance

We would like to put several production databases (approx. 8) on one
sql server instance.
Each database has 3 filegroups(primary, index, log), what is the best
way to set up these files on the server? We will have 3 or 4 drives.
Is it best to spread the log files across the different drives, or keep
the log files all on one drive etc?Hi
What do you mean by 3 or 4 drives? If these are physically separate drives
then if you want resilience then you would probably be best of mirroring
them, therefore leaving you with two pairs of which log files can go on one
pair and data files can go on the other pair. If you want more resilience
then you would need a minimum of 3 discs for Raid 5 and 4 discs for Raid 10.
You may want to read more on raid such as at
http://msdn2.microsoft.com/en-us/library/ms190764.aspx and
http://www.acnc.com/04_01_10.html
As the type of I/O for log files is different to that of the data files you
are best keeping them separate. If you subsequently have I/O problems then
you will need to analyse what is causing this, and possibly move one or more
filegroups and their data files onto their own set of discs.
John
"Jayme" wrote:
> We would like to put several production databases (approx. 8) on one
> sql server instance.
> Each database has 3 filegroups(primary, index, log), what is the best
> way to set up these files on the server? We will have 3 or 4 drives.
> Is it best to spread the log files across the different drives, or keep
> the log files all on one drive etc?
>

Multiple Databases/One SQL Server instance

We would like to put several production databases (approx. 8) on one
sql server instance.
Each database has 3 filegroups(primary, index, log), what is the best
way to set up these files on the server? We will have 3 or 4 drives.
Is it best to spread the log files across the different drives, or keep
the log files all on one drive etc?Hi
What do you mean by 3 or 4 drives? If these are physically separate drives
then if you want resilience then you would probably be best of mirroring
them, therefore leaving you with two pairs of which log files can go on one
pair and data files can go on the other pair. If you want more resilience
then you would need a minimum of 3 discs for Raid 5 and 4 discs for Raid 10.
You may want to read more on raid such as at
http://msdn2.microsoft.com/en-us/library/ms190764.aspx and
http://www.acnc.com/04_01_10.html
As the type of I/O for log files is different to that of the data files you
are best keeping them separate. If you subsequently have I/O problems then
you will need to analyse what is causing this, and possibly move one or more
filegroups and their data files onto their own set of discs.
John
"Jayme" wrote:

> We would like to put several production databases (approx. 8) on one
> sql server instance.
> Each database has 3 filegroups(primary, index, log), what is the best
> way to set up these files on the server? We will have 3 or 4 drives.
> Is it best to spread the log files across the different drives, or keep
> the log files all on one drive etc?
>

Multiple data files in a filegroup

I have a storage subsystem which allows five independant RAID 10 devices. I am creating five filegroups each with 2 data files distributed across the devices in a smart round robin fashion.

I have assigned tables which are commonly joined between the filegroups. For example, Table A is in Filegroup 1 while Table B is in Filegroup 2. The most common and high volume query is the join between these two tables.

My question is this: I understand SQL Server is theaded by data file, not filegroup. But for filegroups which have 2 or more data files, do they both grow evenly? Or once one data file is full, SQL Server then writes all new data to the new data file in the same filegroup.

Hopefully this is not too confusing.

Thanks.

BryanSQL Server will write data to the members of a filegroup evenly, so if you create 2 100MB files in a filegroup, then insert 150MB of data into this filegroup, you will have 75 MB in each. Figuring out what you have after you delete 80 MB of data is virtually impossible, however. Hope this helps.