Cool. Will reply in detail over coffee tomorrow morning (~12 hours from now).

No.

I mean, you can do that, but you're looking at a couple of days for each split, and to get that table back into the correct partitions, you...

??? You posted the alter function and alter schema commands in the first post. They're correct, but the split will run for ages, because it's moving a whole pile of...

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...

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,...

I dug into this a while back while testing how to recover from a crashed instance.

If the TempDB files are there, they're reset to the starting size of...

Add a hint to the UPDATE statement WITH (XLOCK)
That means that the update goes for an exclusive lock straight off, not update lock first (which is done for performance...

In SSMS object explorer, right click the database -> tasks -> generate scripts. That will let you script tables, views, procedures, etc.

Set it in the GUI (or via ALTER DATABASE), then on the next restart TempDB will start with those values. Check autogrow settings as well, should be the same on...