Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Partitioning Error Expand / Collapse
Author
Message
Posted Thursday, December 20, 2012 4:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
USE Master;
GO
--- Step 1 : Create New Test Database with two different filegroups.


IF EXISTS (SELECT name FROM sys.databases WHERE name = N'PartitionDB')
DROP DATABASE PartitionDB;
GO
CREATE DATABASE PartitionDB ON PRIMARY
(NAME='PartitionDB_Part1',FILENAME= 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part1.mdf',SIZE=3, MAXSIZE=100,FILEGROWTH=1 );
GO

ALTER DATABASE PartitionDB ADD FILEGROUP PartitionDB_Part2
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionDB_Part3
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionDB_Part4
GO

-------------------------------------------------------------------------------

alter database PartitionDB
add file (NAME = 'PartitionDB_Part2',FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part2.ndf',
SIZE =3,MAXSIZE=100,FILEGROWTH=1 ) to filegroup PartitionDB_Part2;

alter database PartitionDB
add file (NAME = 'PartitionDB_Part3',FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part3.ndf',
SIZE = 3,MAXSIZE=100,FILEGROWTH=1) to filegroup PartitionDB_Part3;
GO
alter database PartitionDB
add file (NAME = 'PartitionDB_Part4',FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\PartitionDB_Part4.ndf',
SIZE = 3,MAXSIZE=100,FILEGROWTH=1) to filegroup PartitionDB_Part4;
GO

USE PartitionDB;
GO

--- Step 2 : Create Partition Range Function
CREATE PARTITION FUNCTION PartitionDB_PartitionRange (INT)
AS RANGE LEFT FOR VALUES (500,1000,2500);
GO



--- Step 3 : Attach Partition Scheme to FileGroups
CREATE PARTITION SCHEME PartitionDB_PartitionScheme
AS PARTITION PartitionDB_PartitionRange TO ([PRIMARY],[PartitionDB_Part2],[PartitionDB_Part3],[PartitionDB_Part4]);
GO


--- Step 4 : Create Table with Partition Key and Partition Scheme
CREATE TABLE TestTable
(
ID INT identity
,Date DATETIME
) ON PartitionDB_PartitionScheme (ID);
GO

--- Step 5 : (Optional/Recommended) Create Index on Partitioned Table
CREATE UNIQUE CLUSTERED INDEX IX_TestTable ON TestTable(ID)
ON PartitionDB_PartitionScheme (ID);
GO

declare @count int
set @count =1

While (@count < = 5000)
begin
INSERT INTO TestTable (Date) VALUES (GETDATE());
set @count = @count + 1
end
GO



--- Step 7 : Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO





CREATE TABLE TestTable_Archive
(
ID INT identity
,Date DATETIME
)
GO

declare @count int
set @count =1

While (@count < = 40)
begin
INSERT INTO TestTable_Archive (Date) VALUES (GETDATE());
set @count = @count + 1
end
GO


CREATE UNIQUE CLUSTERED INDEX IX_TestTable_Archive ON TestTable_Archive(ID)

GO


Query

-- make the switch 
ALTER TABLE TestTable_Archive SWITCH TO TestTable PARTITION 1;
GO

Error

Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints of source table 'PartitionDB.dbo.TestTable_Archive' allow values
that are not allowed by range defined by partition 1 on target table 'PartitionDB.dbo.TestTable'.



HOw can i identify that what partition1 range is ?




-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1398903
Posted Thursday, December 20, 2012 7:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 42,437, Visits: 35,491
You specified the partition ranges yourself.

CREATE PARTITION FUNCTION PartitionDB_PartitionRange (INT)
AS RANGE LEFT FOR VALUES (500,1000,2500);



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1398964
Posted Thursday, December 20, 2012 10:56 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
GilaMonster (12/20/2012)
You specified the partition ranges yourself.

CREATE PARTITION FUNCTION PartitionDB_PartitionRange (INT)
AS RANGE LEFT FOR VALUES (500,1000,2500);
So here partition 1 means "Less than 500" ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1399196
Posted Friday, December 21, 2012 12:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 42,437, Visits: 35,491
Bhuvnesh (12/20/2012)
GilaMonster (12/20/2012)
You specified the partition ranges yourself.

CREATE PARTITION FUNCTION PartitionDB_PartitionRange (INT)
AS RANGE LEFT FOR VALUES (500,1000,2500);
So here partition 1 means "Less than 500" ?


That's what your partition function says.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1399224
Posted Friday, December 21, 2012 1:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
GilaMonster (12/21/2012)
That's what your partition function says.
what steps i followed .

1 I creaed a table TestTable_Archive (NOn partitioned table )

2) then i moved data from testtable by
ALTER TABLE TestTable SWITCH PARTITION 1 TO TestTable_Archive ; 

3) then i populated the table TestTable_Archive
declare @count int 
set @count =31

While (@count < = 40)
begin
INSERT INTO TestTable_Archive (id,Date) VALUES (@count,GETDATE());
set @count = @count + 1
end
GO

4) then i tried
ALTER TABLE TestTable_Archive SWITCH TO TestTable PARTITION 1; 



but failed with error

Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints of source table 'PartitionDB.dbo.TestTable_Archive' allow values
that are not allowed by range defined by partition 1 on target table 'PartitionDB.dbo.TestTable'.

Can you please explain why ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1399252
Posted Friday, December 21, 2012 1:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 42,437, Visits: 35,491
Bhuvnesh (12/21/2012)
Can you please explain why ?


What does the error message say you are missing?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1399259
Posted Friday, December 21, 2012 1:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
GilaMonster (12/21/2012)
What does the error message say you are missing?
what i can get from error is testtable_archive data is "falling out of range" from partition 1 but actually this table contains data range 31 to 40 (basicaly less then 500) ? then y its failing or i am overlooking something here ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1399269
Posted Friday, December 21, 2012 2:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 42,437, Visits: 35,491
Look at the error message. Read what it says. It says nothing at all about the data being out of range.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1399271
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse