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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Partition Switching Problem
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 01/15/2013 :  11:23:48  Show Profile  Reply with Quote
Hi All,

I have monthly partition (Size 150 GB per month). I forgot to create partition for January-2013. So all the January Data went to the dummy partition I created for the last range. As microsoft recomends the last partition should be empty.

I tried to create a partition for January and switch the data in the dummy partition to the newly created partition. But the script ran for more than a day without any data transfer.
I am not sure how to create new partitions for the table now. The server is having 32 cores and 128GB memory. So I don't think transfering 50GB data (till Jan 12th) witll be a problem for it.

Masters.....CAN ANYBODY HELP???


Starting Member

4 Posts

Posted - 01/24/2013 :  13:10:22  Show Profile  Reply with Quote
is it your partition right or left. run this 2 script below, which will give you some more insight as from script (B) you need to find filegroup, partitionID for Nextused column.
Also check if you have any waittype PageIOLatch_EX .
Script (A)
OBJECT_NAME(SI.object_id) AS PartitionedTable
, AS PartitionScheme
, AS PartitionFunction
, P.partition_number
, P.rows
FROM sys.partitions AS P
JOIN sys.indexes AS SI
ON P.object_id = SI.object_id AND P.index_id = SI.index_id
JOIN sys.data_spaces AS DS
ON DS.data_space_id = SI.data_space_id
JOIN sys.partition_schemes AS PS
ON PS.data_space_id = SI.data_space_id
JOIN sys.partition_functions AS PF
ON PF.function_id = PS.function_id
WHERE DS.type = 'PS'
AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U'
AND SI.type IN(0,1);
--AND OBJECT_NAME(SI.object_id) = 'table Name';
Script (B)
SELECT AS PartitionSchemeName
, CASE WHEN SDD.destination_id <= SPF.fanout THEN SDD.destination_id
, AS PartitionFunctionName
, SPRV.value AS BoundaryValue
, CASE WHEN SDD.destination_id > SPF.fanout THEN 1
ELSE 0 END AS NextUsed
, AS FileGroup
FROM sys.partition_schemes AS SPS
JOIN sys.partition_functions AS SPF
ON SPS.function_id = SPF.function_id
JOIN sys.destination_data_spaces AS SDD
ON SDD.partition_scheme_id = SPS.data_space_id
JOIN sys.filegroups AS SF
ON SF.data_space_id = SDD.data_space_id
LEFT JOIN sys.partition_range_values AS SPRV
ON SPRV.function_id = SPF.function_id
AND SDD.destination_id =
CASE WHEN SPF.boundary_value_on_right = 0 THEN SPRV.boundary_id
ELSE SPRV.boundary_id + 1 END
WHERE = 'Scheme Name'
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000