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