SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

swaroop.sahoo
Starting Member

Rwanda
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???


-Swaroop

serverdba
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)
SELECT
OBJECT_NAME(SI.object_id) AS PartitionedTable
, DS.name AS PartitionScheme
, PF.name 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
SPS.name AS PartitionSchemeName
, CASE WHEN SDD.destination_id <= SPF.fanout THEN SDD.destination_id
ELSE NULL END AS PartitionID
, SPF.name AS PartitionFunctionName
, SPRV.value AS BoundaryValue
, CASE WHEN SDD.destination_id > SPF.fanout THEN 1
ELSE 0 END AS NextUsed
, SF.name 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 SPS.name = 'Scheme Name'
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000