none
[求助]急!怎样能将没用的数据文件及文件组删掉(部分解决) RRS feed

  • 问题

  • 数据库版本:SQL SERVER 2008

    问题概要:

    由于物理硬盘空间有限(4T左右),而数据的输入量很大,每个月200G左右。现在的需求是只要保存一年的数据,之前的都可以删除掉。目前数据文件按照月份分成了12个文件组,也就是每个月一个组,每组中就一个数据文件。想问下各位达人,有没有什么办法能方便的将这些数据文件删除掉。

    之前我试过了两种方法:

    1、使用数据压缩,就是dbcc shrinkfile,然而释放的空间有限,最好还是能把这些无用的数据文件从物理上消灭掉。书上说空的数据文件可以用alter database remove file直接删除,但是这里都有数据的所以也用不来。至于dbcc shrinkfile(emptyfile)只是移动数据文件,也不是真正意义上的删除。

    2、使用导入导出,将需要的数据通过sql文控制导出,然后新建一个数据库导入,最后用新库替代旧库,这样可以把旧库直接删掉。不过由于数据量过大,风险也太大,所以不到万不得已不能这样做。

    大家还有没有什么好的办法啊,小弟我已经黔驴技穷了,就指望大家了^_^

    --------------------------------------------------------------------------------------------------------------------------------------------------------------

    谢谢版主和rmiao大虾的回复,让小弟有了点思路。昨天回去做了下试验,向大家汇报下结果。

    以数据库中tMXXX表为例,这张表目前占用了5G的空间,分为24个文件组,每个文件组一个数据文件,分别保存了从2009-1-1日到2011-1-1日的数据,分区函数是以每个月为一个区间的。boss的要求是只要保存一年的数据即可,所以目前要做的工作是将2009-1-1日到2009-12-31日的数据删除,但是保留2010-1-1日到2011-1-1的数据。

    按照两位老大的观点,直接用drop命令删除表或者用truncate清空表后,都是可以用alter database remove file将数据文件删除的。那么依据这个思路,如果我用delete命令只删除部分分区中的数据,那么这些分区是不是也同样能remove掉呢?下面就是我的操作:

    1、DELETE FROM tMXXX  WHERE RevDate < '2010-1-1'

    经过n分钟的等待后,提示‘(n行受影响)’,删除完成。

    2、SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('tMXXX');

    查看分区状况,发现2009年的12个分区DB0901~DB0912所对应的ROWS都变为0了。那么这些分区上的文件是不是都算是空文件了呢,如果是空文件那么就应该可以被REMOVE掉。

    3、

    USE [DBXXX]
    GO
    ALTER DATABASE [DBXXX] REMOVE FILE [DB0901]

    GO

    提示:消息5042,级别16,状态1,第1行 无法删除 文件'DB0901',因为它不为空

    看来就算是没有数据行,也不一定就属于‘空文件’,是否只要进行了页分配就是非空文件了,这样的话要怎么清除页分配呢?

    4、在分区文件DB0901对应的文件组FG0901上再添加一个分区文件DB0901_1,将其初始容量设置为最小1M,然后执行下面的命令:

    USE [DBXXX]
    GO
    DBCC SHRINKFILE ('DB0901',EMPTYFILE)
    GO

    返回的结果是该命令正常执行,DB0901上的数据转移至DB0901_1上,然后可以用REMOVE命令将DB0901删除掉。DB0901原来要占用近1G的空间,而DB0901_1只占用1M。至此,减少无用数据空间占用的目的算是基本上达成了,可是依然不能完全删除相关的分区文件。希望各位兄弟继续帮忙想办法~



    • 已编辑 XQ_NEW 2011年6月25日 3:00
    2011年6月24日 6:43

答案

  • 你要先删除这个文件组上的数据,比如,删除上面的表,把这些文件组上的分区切出来删掉。

    然后就可以删除这些文件组了。


    想不想时已是想,不如不想都不想。
    • 已标记为答案 XQ_NEW 2011年6月25日 2:49
    2011年6月24日 9:19
    版主

全部回复

  • 你要先删除这个文件组上的数据,比如,删除上面的表,把这些文件组上的分区切出来删掉。

    然后就可以删除这些文件组了。


    想不想时已是想,不如不想都不想。
    • 已标记为答案 XQ_NEW 2011年6月25日 2:49
    2011年6月24日 9:19
    版主
  • You need drop table first then delete related data file and file group for that table.
    2011年6月24日 12:54

  • 谢谢版主和rmiao大虾的回复,让小弟有了点思路。昨天回去做了下试验,向大家汇报下结果。

    以数据库中tMXXX表为例,这张表目前占用了5G的空间,分为24个文件组,每个文件组一个数据文件,分别保存了从2009-1-1日到2011-1-1日的数据,分区函数是以每个月为一个区间的。boss的要求是只要保存一年的数据即可,所以目前要做的工作是将2009-1-1日到2009-12-31日的数据删除,但是保留2010-1-1日到2011-1-1的数据。

    按照两位老大的观点,直接用drop命令删除表或者用truncate清空表后,都是可以用alter database remove file将数据文件删除的。那么依据这个思路,如果我用delete命令只删除部分分区中的数据,那么这些分区是不是也同样能remove掉呢?下面就是我的操作:

    1、DELETE FROM tMXXX  WHERE RevDate < '2010-1-1'

    经过n分钟的等待后,提示‘(n行受影响)’,删除完成。

    2、SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('tMXXX');

    查看分区状况,发现2009年的12个分区DB0901~DB0912所对应的ROWS都变为0了。那么这些分区上的文件是不是都算是空文件了呢,如果是空文件那么就应该可以被REMOVE掉。

    3、

    USE [DBXXX]
    GO
    ALTER DATABASE [DBXXX] REMOVE FILE [DB0901]

    GO

    提示:消息5042,级别16,状态1,第1行 无法删除 文件'DB0901',因为它不为空

    看来就算是没有数据行,也不一定就属于‘空文件’,是否只要进行了页分配就是非空文件了,这样的话要怎么清除页分配呢?

    4、在分区文件DB0901对应的文件组FG0901上再添加一个分区文件DB0901_1,将其初始容量设置为最小1M,然后执行下面的命令:

    USE [DBXXX]
    GO
    DBCC SHRINKFILE ('DB0901',EMPTYFILE)
    GO

    返回的结果是该命令正常执行,DB0901上的数据转移至DB0901_1上,然后可以用REMOVE命令将DB0901删除掉。DB0901原来要占用近1G的空间,而DB0901_1只占用1M。至此,减少无用数据空间占用的目的算是基本上达成了,可是依然不能完全删除相关的分区文件。希望各位兄弟继续帮忙想办法~


    2011年6月25日 2:49
  • For partitioned table that cross multiple file groups, you should drop partitions don't need to keep then delete files and groups used for dropped partitions. 
    2011年6月25日 3:02
  • For partitioned table that cross multiple file groups, you should drop partitions don't need to keep then delete files and groups used for dropped partitions. 

    看来既要保持当前的表又要删除表上的分区是不可行的阿。。。。
    2011年6月25日 3:22
  • You can spin off partition from partitioned table, 'Designing Partitions to Manage Subsets of Data' in books online tells how.
    2011年6月25日 3:50
  • Merge empty partitions.
    想不想时已是想,不如不想都不想。
    2011年6月25日 8:20
    版主