Prev: reference chain - recursive CTE ?
Next: [IF loop] OPENDATASOURCE reads file even if conditions not met?
From: Paul on 20 Jul 2010 10:03
Hi,

I have filegroups called 2006, 2007, 2008 etc.

I have files, one for each of our larger tables that belong to each filegroup.
E.G.
File Name File Group
2006_Transactions 2006
2006_Events 2006
2007_Transactions 2007
2007_Events 2007
etc...

I have the folloinwg partitioning:

CREATE PARTITION FUNCTION [PFBusDate](SMALLDATETIME)
AS RANGE RIGHT FOR VALUES
(N'2007-01-01T00:00:00', N'2008-01-01T00:00:00',
N'2009-01-01T00:00:00', N'2010-01-01T00:00:00',
N'2011-01-01T00:00:00', N'2012-01-01T00:00:00',
N'2013-01-01T00:00:00', N'2014-01-01T00:00:00',
N'2015-01-01T00:00:00', N'2016-01-01T00:00:00',
N'2017-01-01T00:00:00', N'2018-01-01T00:00:00',
N'2019-01-01T00:00:00', N'2020-01-01T00:00:00',
N'2021-01-01T00:00:00', N'2022-01-01T00:00:00',
N'2023-01-01T00:00:00', N'2024-01-01T00:00:00',
N'2025-01-01T00:00:00')

CREATE PARTITION SCHEME [PSBusDate]
AS PARTITION [PFBusDate] TO (
[2006], [2007], [2008], [2009],
[2010], [2011], [2012], [2013],
[2014], [2015], [2016], [2017],
[2018], [2019], [2020], [2021],
[2022], [2023], [2024], [2025])

My issue is when I insert data into the Transactions table for 2006, file
2006_Transactions which is physical file 2006_Transactions.NDF grows, as
expected, BUT 2006_Events.NDF also grows by the same amount even though the
Events table has not yet been populated?

All the 2006_tablename.NDF files have grown, even though only 1 table has
been populated.

This will cause the partitioned database to need much more disk space than
the unpartitioned version which doesn't seem correct.

Thanks
Paul
From: Erland Sommarskog on 20 Jul 2010 17:53
Paul (Paul(a)discussions.microsoft.com) writes:

> My issue is when I insert data into the Transactions table for 2006,
> file 2006_Transactions which is physical file 2006_Transactions.NDF
> grows, as expected, BUT 2006_Events.NDF also grows by the same amount
> even though the Events table has not yet been populated?

You are getting confused by the names of the files in the filegroup.

If memory serves files in a filegroup are filled in a round-robin fashion.
You can certainly not control which table that goes to which file in a
file group. If you want events and transactions to be in different files,
you need to put those files in different file groups.




--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Dan Guzman on 21 Jul 2010 11:25
To add on Erland's advice, you will also need to different partition schemes
with the desired partition/filegroup mappings. For Example:

CREATE PARTITION SCHEME [PSBusDate_Transactions]
AS PARTITION [PFBusDate] TO (
[Transactions_2006], [Transactions_2007], [Transactions_2008],
[Transactions_2009],
[Transactions_2010], [Transactions_2011], [Transactions_2012],
[Transactions_2013],
[Transactions_2014], [Transactions_2015], [Transactions_2016],
[Transactions_2017],
[Transactions_2018], [Transactions_2019], [Transactions_2020],
[Transactions_2021],
[Transactions_2022], [Transactions_2023], [Transactions_2024],
[Transactions_2025])


CREATE PARTITION SCHEME [PSBusDate_Events]
AS PARTITION [PFBusDate] TO (
[Events_2006], [Events_2007], [Events_2008], [Events_2009],
[Events_2010], [Events_2011], [Events_2012], [Events_2013],
[Events_2014], [Events_2015], [Events_2016], [Events_2017],
[Events_2018], [Events_2019], [Events_2020], [Events_2021],
[Events_2022], [Events_2023], [Events_2024], [Events_2025])


--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Paul" <Paul(a)discussions.microsoft.com> wrote in message
news:2DA83D48-BECC-4DB4-BCC5-E6D44AC140ED(a)microsoft.com...
> Hi,
>
> I have filegroups called 2006, 2007, 2008 etc.
>
> I have files, one for each of our larger tables that belong to each
> filegroup.
> E.G.
> File Name File Group
> 2006_Transactions 2006
> 2006_Events 2006
> 2007_Transactions 2007
> 2007_Events 2007
> etc...
>
> I have the folloinwg partitioning:
>
> CREATE PARTITION FUNCTION [PFBusDate](SMALLDATETIME)
> AS RANGE RIGHT FOR VALUES
> (N'2007-01-01T00:00:00', N'2008-01-01T00:00:00',
> N'2009-01-01T00:00:00', N'2010-01-01T00:00:00',
> N'2011-01-01T00:00:00', N'2012-01-01T00:00:00',
> N'2013-01-01T00:00:00', N'2014-01-01T00:00:00',
> N'2015-01-01T00:00:00', N'2016-01-01T00:00:00',
> N'2017-01-01T00:00:00', N'2018-01-01T00:00:00',
> N'2019-01-01T00:00:00', N'2020-01-01T00:00:00',
> N'2021-01-01T00:00:00', N'2022-01-01T00:00:00',
> N'2023-01-01T00:00:00', N'2024-01-01T00:00:00',
> N'2025-01-01T00:00:00')
>
> CREATE PARTITION SCHEME [PSBusDate]
> AS PARTITION [PFBusDate] TO (
> [2006], [2007], [2008], [2009],
> [2010], [2011], [2012], [2013],
> [2014], [2015], [2016], [2017],
> [2018], [2019], [2020], [2021],
> [2022], [2023], [2024], [2025])
>
> My issue is when I insert data into the Transactions table for 2006, file
> 2006_Transactions which is physical file 2006_Transactions.NDF grows, as
> expected, BUT 2006_Events.NDF also grows by the same amount even though
> the
> Events table has not yet been populated?
>
> All the 2006_tablename.NDF files have grown, even though only 1 table has
> been populated.
>
> This will cause the partitioned database to need much more disk space than
> the unpartitioned version which doesn't seem correct.
>
> Thanks
> Paul

 | 
Pages: 1
Prev: reference chain - recursive CTE ?
Next: [IF loop] OPENDATASOURCE reads file even if conditions not met?