SPLIT RANGE TOOK 10 HRS to Complete

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SPLIT RANGE TOOK 10 HRS to Complete

SPLIT RANGE TOOK 10 HRS to Complete

Rate Topic

Display Mode

Topic Options

Author

Message

Ramdas Baghel

Posted Monday, December 03, 2012 8:15 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:57 AM
Points: 224, Visits: 825

Hi All,

I just split the Range (introduced new range) of my existing Partition Function but it took 10HRS to complete and my database transaction log grew around 120GB.
My File Groups are : only one table using these file groups by Table Partitioning using below Partition Scheme & Function.

1. PRIMARY - 5GB
2. JulData - 20 GB
3. AugData - 17 GB
4. SepData - 19 GB
5. OctData - 20 GB
6. NovData - 22 GB

My Partition Scheme & Function is:
CREATE PARTITION SCHEME [psch__DateTime] AS PARTITION [pfn__DateTime] TO ([PRIMARY], [JulData], [AugData], [SepData], [OctData], [NovData])

CREATE PARTITION FUNCTION [pfn__DateTime] (datetime) AS RANGE RIGHT FOR VALUES ('2012-7-01', '2012-08-01', '2012-09-01', '2012-10-01', '2012-11-01')

Everything was fine. Today, I like to introduce new boundary for Dec Month so I followed following steps:-

1. I created new File Group “DecData” and assign a file for Dec Data.
2. ALTER PARTITION SCHEME [psch__DateTime] NEXT USED [DecData]
3. ALTER PARTITION FUNCTION [pfn__DateTime]() SPLIT RANGE ('2012-12-01')

But my 3rd step took around 10hrs to complete and my database log size increased from 5GB to 120GB, I surprised why this happen? I just introduced a new boundary that's it. What was wrong in my task? I still investigate, if any one idea plz help to understand why it took so much time to just introduce new boundary.

Harware Details:
OS : Windows Server 2008 DC SP2 64bit
CPU : 2.66GHz - 2 Quad Core
RAM : 24 GB
Only one SQL Server 2008 64bit Instance running on it.
:)

Ram
MSSQL DBA

Post #1391967

GilaMonster

Posted Monday, December 03, 2012 8:45 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37,635, Visits: 29,886

The general recommendation when splitting ranges is to ensure that no existing data will have to move to a new partition. If it does, trhen you get what you saw, large time requirement and large log growth.

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 #1391993

Ramdas Baghel

Posted Monday, December 03, 2012 9:02 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:57 AM
Points: 224, Visits: 825

Thank u Gail for your update. Yes I ware have Dec month data in "NovData" FG before spliting.

So u mean we have to split/introduce new range before new data arraving to avoid loong time to complete?

Ram
MSSQL DBA

Post #1392014

GilaMonster

Posted Monday, December 03, 2012 3:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37,635, Visits: 29,886

Yup. If you split the range when there's data that will qualify for the new range, SQL has to move that data to a new partition, which kinda defeats the point of the sliding window setup.

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 #1392169

ScottPletcher

Posted Monday, December 03, 2012 4:26 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 1,318, Visits: 1,763

What was the status of the log file when you ran the command? How large was the log, how much space was free, and what was the Autogrowth amount?

Be sure to pre-allocate, and thus pre-format, the space required for the log. Make sure the growth is not a percentage or too small (or too large, but that's much less common).

If you are not using IFI, do the same for the data file/partition as well (or, better yet, activate IFI immediately!).

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1392177

« Prev Topic | Next Topic »

Permissions