Partitioning existing Databas - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Partitioning existing Databas

Author

Topic

Slepc
Starting Member

4 Posts

Posted - 2014-10-23 : 05:08:56

Hello,

i have 200 gb big database, and i would like to create partition on this database.

I have to big tables raw and calc(i would like to make partition only for this two tables), they have two columns timestamp and value.

I would like to have new partition on this two tables for every year.

So i will have files:

db2011

db2012

db2013

db2014

db2015

Which steps i need to take, to make this posible?

Slepc
Starting Member

4 Posts

Posted - 2014-10-23 : 09:50:04

For now i have created:

ALTER DATABASE Test
ADD FILEGROUP RESULTSBEFORE2011;
GO
ALTER DATABASE Test
ADD FILEGROUP Leto2011;
GO
ALTER DATABASE Test
ADD FILEGROUP Leto2012;
GO
ALTER DATABASE Test
ADD FILEGROUP Leto2013;
GO
ALTER DATABASE Test
ADD FILEGROUP Leto2014;
GO
ALTER DATABASE Test
ADD FILEGROUP Leto2015;

ALTER DATABASE Test
ADD FILE 
(
    NAME = PredLetom2011,
    FILENAME = 'C:\Test\PredLetom2011.ndf',
    FILEGROWTH = 5000MB
)
TO FILEGROUP RESULTSBEFORE2011;
ALTER DATABASE Test
ADD FILE 
(
    NAME = PodatkiLeto2011,
    FILENAME = 'C:\Test\PodatkiLeto2011.ndf',
    FILEGROWTH = 5000MB
)
TO FILEGROUP Leto2011;
ALTER DATABASE Test
ADD FILE 
(
    NAME = PodatkiLeto2012,
    FILENAME = 'C:\Test\PodatkiLeto2012.ndf',
    FILEGROWTH = 5000MB
)
TO FILEGROUP Leto2012;
GO
ALTER DATABASE Test
ADD FILE 
(
    NAME = PodatkiLeto2013,
    FILENAME = 'C:\Test\PodatkiLeto2013.ndf',
    FILEGROWTH = 5000MB
)
TO FILEGROUP Leto2013;
GO
ALTER DATABASE Test 
ADD FILE 
(
    NAME = PodatkiLeto2014,
    FILENAME = 'C:\Test\PodatkiLeto2014.ndf',
    FILEGROWTH = 5000MB
)
TO FILEGROUP Leto2014;
GO
ALTER DATABASE Test 
ADD FILE 
(
    NAME = PodatkiLeto2015,
    FILENAME = 'C:\Test\PodatkiLeto2015.ndf',
    FILEGROWTH = 5000MB
)
TO FILEGROUP Leto2015;

	CREATE PARTITION FUNCTION [RESULTS_PF](datetime) AS RANGE RIGHT 
FOR VALUES (
N'2011-01-01T00:00:00.000', 
N'2012-01-01T00:00:00.000', 
N'2013-01-01T00:00:00.000', 
N'2014-01-01T00:00:00.000', 
N'2015-01-01T00:00:00.000')
GO

CREATE PARTITION SCHEME [RESULTS_PS] AS PARTITION [RESULTS_PF] 
TO (
[RESULTSBEFORE2011], 
[Leto2011], 
[Leto2012], 
[Leto2013], 
[Leto2014], 
[Leto2015])
GO

Is everthing ok till now?