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?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-23 : 11:06:17
Looks perfect so far!

Can you test it in a sandbox with a subset of your data?
Go to Top of Page

Slepc
Starting Member

4 Posts

Posted - 2014-10-24 : 02:39:20
How can i now enable this partitioning only on this two tables?

CALC
RAW
The structure looks like this(the same structure for both tables):


Sample of data:
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-24 : 08:54:10
The next thing to do is to DROP and reCREATE the Clustered indexes of those tables specifying ON RESULTS_PS(<partitioning column>)
Go to Top of Page

Slepc
Starting Member

4 Posts

Posted - 2014-10-24 : 09:40:42
CREATE CLUSTERED INDEX cdx_raw ON dbo.raw([TS]);

I need to do this ? There is no partitioning now, do i need to drop it?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-24 : 10:56:32
Almost

[code]

CREATE CLUSTERED INDEX cdx_raw ON dbo.raw([TS])
ON RESULTS_PS(partitioncolumn)
;

Good discussion here: http://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/

Go to Top of Page
   

- Advertisement -