April 8, 2017 at 5:41 pm
hello all,
I have a table that has 2,948,231,398 billion rows...I know...last week I had to figure out how to remove the old files, 2014, from the table so I could create new ones this week. I created a new filegroup inv_201605. I created a new inv201605.ndf file to move the data into and associated the ndf file with the new filegroup. I have read several articles. I ran the next used command then tried running the split but that lasted for over a day. below are the queries. I did create a temp table at one point and move over the default file, ran the below and a rebuild but that also last a whole day and grew the file to an outrageous amount, I thought this can't be right it's rebuilding the default into the new filegroup when I only want 05012016 to 05312016.
Please help me if you can.
ALTER PARTITION SCHEME psch_Inv
NEXT USED [INV_201605];
ALTER PARTITION FUNCTION pfn_Inv()
SPLIT RANGE ('2016-05-31');
CREATE
UNIQUE CLUSTERED INDEX PK_tblInventory_NewerToOlderTemp
ON dbo.tblInventory_NewerToOlderTemp(Transdate, StoreID, Productid)
WITH(DROP_EXISTING=ON)
ON [INV_201605];
MCSE SQL Server 2012\2014\2016
April 9, 2017 at 6:30 pm
I have been reading more and found this article;
http://stackoverflow.com/questions/37239582/switch-partition-in-sql-server/37241198#37241198
I really just want to scream and throw my laptop...lol
MCSE SQL Server 2012\2014\2016
April 10, 2017 at 11:22 am
lkennedy76 - Sunday, April 9, 2017 6:30 PMI have been reading more and found this article;http://stackoverflow.com/questions/37239582/switch-partition-in-sql-server/37241198#37241198
I really just want to scream and throw my laptop...lol
this didn't work, I want to create new files to an existing partition function and scheme not switch them.
MCSE SQL Server 2012\2014\2016
April 11, 2017 at 10:07 am
I am pretty sure this is what I need to do below, I am deleting zero items in the table to get it down from 2.982 billion to a doable amount
Alter database DBName add filegroup [inv_201605];
alter database DBName add file (name = inv_201605, file name = 'd:\sqldata\inv_201605.ndf', size = 102400, filegrowth = 256MB) to filegroup [inv_201605];
alter partition scheme [psch_inv] next used {inv_201605];
alter partition function [pfun_inv] () split range ('2016-05-31 00:00:00.000');
am I right? anyone?
MCSE SQL Server 2012\2014\2016
April 18, 2017 at 1:19 pm
Can anyone help me? Are my steps correct? Every time I run the split it never finishes. The next used didn't give the file a partition number so I am guessing that's why the data never moved....
MCSE SQL Server 2012\2014\2016
April 18, 2017 at 1:45 pm
If the split takes time then there's data that needs to be moved to the new partition. It's generally recommended to split so that the new partition is empty. If you can't do that, then the split is going to take time, it's essentially deleting and inserting the data that needs to be moved to the new partition.
What does this return?
SELECT COUNT(*) FROM <partitioned table name> WHERE <partition column> > '2016-05-31 00:00:00.000'
If there are lots of rows that have to move to the new partition, perhaps create a new table in the destination filegroup, identical in structure to your partitioned table, insert into that new table the rows you want in the new partition, delete them from the monster and then split the partition (instant this time since there's no data to move) and switch in the table that you created. But that's going to take time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2017 at 2:01 pm
Gail,
It ran for 10 minutes with no result. Let me restore the database again, I ran the split for a few minutes today and then canceled it. I am thinking the person before me use to switch it out using temp tables like you suggested because they did not leave an empty partition for the next used.
I am testing all this on a dev server so I can mess it up all I want until I can get a process for this.
MCSE SQL Server 2012\2014\2016
April 18, 2017 at 2:14 pm
Leave it to run please. We need an idea of how many rows are going to have to move in order to decide on a strategy
And I didn't suggest using a temp table, you can't switch a temp table into a partition. You'll need a permanent table, in the user database, created on the filegroup that you want the new partition's data to reside on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2017 at 8:13 pm
ok, I will let you know when it finishes.
MCSE SQL Server 2012\2014\2016
April 19, 2017 at 6:30 am
GilaMonster - Tuesday, April 18, 2017 1:45 PMIf the split takes time then there's data that needs to be moved to the new partition. It's generally recommended to split so that the new partition is empty. If you can't do that, then the split is going to take time, it's essentially deleting and inserting the data that needs to be moved to the new partition.What does this return?
SELECT COUNT(*) FROM <partitioned table name> WHERE <partition column> > '2016-05-31 00:00:00.000'If there are lots of rows that have to move to the new partition, perhaps create a new table in the destination filegroup, identical in structure to your partitioned table, insert into that new table the rows you want in the new partition, delete them from the monster and then split the partition (instant this time since there's no data to move) and switch in the table that you created. But that's going to take time.
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
MCSE SQL Server 2012\2014\2016
April 19, 2017 at 6:58 am
Well that partially answers the question. At least 2.1 billion rows need to move to the new partition. :crying:
This is NOT going to be a quick process, no matter how you do it. I recommend the new table, but even then you'll probably need to insert and delete in batches to avoid blowing the transaction log up.
Can you also run the following please?SELECT partition_number,
row_count
FROM sys.dm_db_partition_stats
WHERE OBJECT_ID = OBJECT_ID('<name of partitioned table>') AND index_id = 1
and if possible can you post the current definitions of the partition function and partition scheme?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2017 at 7:03 am
lkennedy76 - Wednesday, April 19, 2017 6:30 AMGilaMonster - Tuesday, April 18, 2017 1:45 PMIf the split takes time then there's data that needs to be moved to the new partition. It's generally recommended to split so that the new partition is empty. If you can't do that, then the split is going to take time, it's essentially deleting and inserting the data that needs to be moved to the new partition.What does this return?
SELECT COUNT(*) FROM <partitioned table name> WHERE <partition column> > '2016-05-31 00:00:00.000'If there are lots of rows that have to move to the new partition, perhaps create a new table in the destination filegroup, identical in structure to your partitioned table, insert into that new table the rows you want in the new partition, delete them from the monster and then split the partition (instant this time since there's no data to move) and switch in the table that you created. But that's going to take time.
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
The number of records matching your criteria is greater than the max number for INT. You need to use COUNT_BIG(*) which returns a BIGINT
April 19, 2017 at 7:16 am
GilaMonster - Wednesday, April 19, 2017 6:58 AMWell that partially answers the question. At least 2.1 billion rows need to move to the new partition. :crying:This is NOT going to be a quick process, no matter how you do it. I recommend the new table, but even then you'll probably need to insert and delete in batches to avoid blowing the transaction log up.
Can you also run the following please?
SELECT partition_number,
row_count
FROM sys.dm_db_partition_stats
WHERE OBJECT_ID = OBJECT_ID('<name of partitioned table>') AND index_id = 1
and if possible can you post the current definitions of the partition function and partition scheme?
no results returned. I did not set the function or the schema up, is there a query that I can get the definitions for you. I do know that they choose the date column in the inventory table. I am running the count_big stmt now to see if I can get a row count.
Thanks Des for the tip on the row count for big data!
MCSE SQL Server 2012\2014\2016
April 19, 2017 at 7:22 am
I am also cleaning out the zero quantity items which make up 90% of the inventory table. I have about 12 - 14 million rows a day and end up with 140K when I am done deleting the zero quantity items. This however is going to take a few months. I can only do it one day at a time so the log doesn't explode.
MCSE SQL Server 2012\2014\2016
April 19, 2017 at 7:27 am
In object explorer, expand out the database -> storage - > partition functions. right click the function -> script. Do the same with the partition scheme
That query should have returned results. Check that you ran it in the correct database and specified the table name correctly (with schema if necessary)
Maybe change to:
SELECT partition_number,
row_count
FROM sys.dm_db_partition_stats
WHERE OBJECT_ID = OBJECT_ID('<name of partitioned table>') AND index_id in (0, 1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply