How to move an empty partition on a partitioned table.

Question

Post reply

How to move an empty partition on a partitioned table.

Jeff Moden

SSC Guru

Points: 1004362
More actions
March 31, 2021 at 7:52 pm

#3864128
I'm in a bit of a bind that I need to complete before some automatic stuff happens in the morning (on the first of the month).
I have a partitioned table (Call_Recordings) and I need to move the last (currently empty) partition from the current drive to a different drive because I'm running out of space. Of course, I also have to pay attention to the partition scheme and the partition function.
If I understand correctly, I'll need to merge that partition into the partition before that, create a new filegroup and file on the new drive and then so something to the partition scheme and the partition function. I'm in the process of researching how to do that but, if you already know how to do that, I sure could use a good example especially since I'm working against a hard deadline.
Here's the info on the partition that I want to move to a different drive and I need to do it without taking the database offline. The only thing I want to change when all is said and done is the physical file name. Any help will be seriously appreciated especially if it's in the form of code.
```
  Partition Scheme Name: psCall_Recordings_Date_Added
Partition Function Name: pfCall_Recordings_Date_Added
          Boundary Type: Right
         Lower Boundary: 2021-04-01 00:00:00.000
         Upper Boundary: NULL
        File Group Name: Call_Recordings_20210401
      Logical File Name: Call_Recordings_20210401
     Physical File Name: P:\SQLData\Call_Recordings_20210401.ndf --Need to change the drive letter to O:
```
p.s. The previous partition to the one above is identical to the one above except all of the occurrences of "20210401" are "20210301"
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
April 8, 2021 at 8:50 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

Jeff,

I don't have a script ready, but this script looks like it does what you want it to do

https://dba.stackexchange.com/questions/106004/moving-a-partition-of-partitioned-table-to-other-file-group-sql-server

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ScottPletcher SSC Guru Points: 101103 More actions · Answer 2

Hmm, maybe add another file to the filegroup in the new location.

Then remove the existing file from the filegroup; since the file's empty, you should be able to remove it.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

Thanks to both of you. I'll take a look. I really appreciate it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

Saints be praised. I'm allowed up to a 15 minute outage at 9 tonight. I couldn't use Scott's suggestion 100% because I can't have two files with the same logical name and, unfortunately, the logical name of the files had to be preserved. Instead, I wrote code to forcefully take the database offline, copy the empty file (which takes no time), alter the database to repoint the logical name to the new drive/file (and the file name is also preserved, which is also and unfortunately necessary), and then bring the database back online.

I removed the release-header and footer because it has a bunch of company sensitive stuff in it and changed the database name to something generic. The run time should be measured in seconds because the file being moved is actually empty right now.

USE master; --Necessarily hardcoded for this run.

--===== Take the database offline
        RAISERROR('Taking the SomeDBName database offline...',0,0) WITH NOWAIT;
  ALTER DATABASE SomeDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  ALTER DATABASE SomeDBName SET MULTI_USER;
  ALTER DATABASE SomeDBName SET OFFLINE
;
--===== COPY the .ndf file from the current disk/directory to the new one.
        RAISERROR('COPYing the .ndf file to the O:\SQLData\ directory...',0,0) WITH NOWAIT;
   EXEC xp_CmdShell 'COPY "P:\SQLData\Call_Recordings_20210401.ndf" "O:\SQLData\Call_Recordings_20210401.ndf"'
;
--===== Repoint the logical file to the copied new physical file.
        RAISERROR('REPOINTing the logical file to the newly copied file...',0,0) WITH NOWAIT;
  ALTER DATABASE SomeDBName 
 MODIFY FILE (NAME = Call_Recordings_20210401, FILENAME = 'O:\SQLData\Call_Recordings_20210401.ndf')
;
--===== Bring the database back online.
        RAISERROR('Bringing the SomeDBName database back online...',0,0) WITH NOWAIT;
  ALTER DATABASE SomeDBName SET ONLINE
;

Thank you both again for the quick responses.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101103 More actions · Answer 5

I would have thought you could just rename the logical names to end up with the same name.

Either: rename the original logical file name before you add the new file with the logical file name you have to have.

Or: add a file with a new logical name. Drop the original file, then rename the logical file name on the new file.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101103 More actions · Answer 6

Sorry, I just have to ask: why would logical file name(s_ need to be preserved? Are they hard-coded in some existing statements? That seems dangerous, what if files are added to a filegroup?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

ScottPletcher wrote:

Sorry, I just have to ask: why would logical file name(s_ need to be preserved? Are they hard-coded in some existing statements? That seems dangerous, what if files are added to a filegroup?

All good questions, Scott. The reason for both the logical and physical file names needing to be preserved is simply due to a naming convention that we follow to make automation of the month end process much easier. There's also the established convention that there will be one file per file group for this particular table. A part of the reason for such rigidity is because the file for each month is "Closed-Out" at the end of each month in a process that ensures there is very little free space in the file and then it is set to Read_Only so that we only have to back it up one final time after it's set to Read_Only and never again. When I originally took this table on, there was no such consideration and just this one table would take hours to backup years of data that was never going to change ever again.

This one table is over 1.9 TB and only the current month partition is where data is added. Unfortunately, they won't let me get rid of any of the data. The table has data in it from way back in February of 2010.

BTW, your idea gave me the idea to do what I did because it was incredibly simple and the total outage for the database was about 1500ms. People were tickled pink and I didn't have to futz with either the partitioning scheme or function and so thank you very much for the idea. For the month end routine, all I had to do was change one variable assignment to point to the new drive/directory and, because of the rigid naming convention, it went about its business like nothing ever happened.

Looking back to your previous comment, I'll have to look into the swap and drop of logical file names. That could work a treat for your original idea, as well. I might even be able to do this to the other filegroup/files with no offline time. Thanks, again, Scott.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)