Post reply

Delete all data in a partition?

  • March 18, 2011 at 6:31 am

    #145317

    Hi,

    This may be a simple question, because I'm new to partitioning.

    I have a huge table that is partitioned so that old data from 2009 is in it's own file (partition2009.ndf).

    This file is huge and is not accessed very often (if at all).

    I need to backup and then delete this partition to save disk space.

    If I use:

    ALTER DATABASE [Test] REMOVE FILE [PARTITION2009]

    SQL server will tell me the file is not empty. Fair enough!

    If I use:

    DBCC SHRINKFILE (N'PARTITION2009' , EMPTYFILE)

    SQL server tells me:

    Cannot move all contents of file "PARTITION2009" to other places to complete the emptyfile operation.

    Could not allocate space for object 'dbo.Files' in database 'Test' because the 'PARTITION2009' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

    Autogrowth is on for partition2009 filegroup and I have plenty of free disk space.

    What am I doing wrong?

    Thank you in advance

  • March 21, 2011 at 1:59 am

    #1301133

    Anyone? Please?

  • March 21, 2011 at 7:08 am

    #1301269

    Create a staging table on the same filegroup as your partition, use SWITCH to move the data from your table partition into the staging table. You can then TRUNCATE the staging table and drop the partition.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 22, 2011 at 1:39 am

    #1301697

    I will try that.

    Thank you, opc.three!

Viewing 4 posts - 1 through 4 (of 4 total)

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