locked
removing old partition data - best practices RRS feed

  • Question

  • In SQL server 2008 I have a big partitioned table. I have yearly partitions. I want to create job/script that will backup the oldest partition files into another disk and remove them from database when disk usage > 80%.
    What is the best practice to accomplish this task? Is there some mechanism to attach/detach partition files from database ? 
    Monday, November 9, 2009 9:00 AM

Answers

  • Hi,
    You achive this by the following method.

    1) Create a Job that will Monitor the Disk Space usage
    2) this job will run a Procedure/Package/Job when the disk space usage is more than 80%
    3) The tasks for the Procedure/Package/Job will be 
       i) This will do the partition Switching to a different table (Staging Table)
      ii) From the staging table you can transfer the data to any other Disk you want using BCP/SSIS etc.

    Please Vote & "Mark As Answer" if this post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP
    Tuesday, November 10, 2009 6:51 AM

All replies

  • You cannot attach/detach partition files. What you can do is swap the partition to be archived to a stagging table so it is not accessible from end users. Then use BCP to export it to a file and delete the staging table.
     
    Monday, November 9, 2009 2:20 PM
  • Hi,
    You achive this by the following method.

    1) Create a Job that will Monitor the Disk Space usage
    2) this job will run a Procedure/Package/Job when the disk space usage is more than 80%
    3) The tasks for the Procedure/Package/Job will be 
       i) This will do the partition Switching to a different table (Staging Table)
      ii) From the staging table you can transfer the data to any other Disk you want using BCP/SSIS etc.

    Please Vote & "Mark As Answer" if this post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP
    Tuesday, November 10, 2009 6:51 AM