MSSQL 数据表 删除记录然后添加记录 表的大小,和数据大小的变化

突然想起一个问题, 这个表里面我 如何删除了数据,这个表的大小 应该是不会变动,比如有
10000条记录 占到校10MB 那么我删除5000条数据之后,表应该还是10MB , 但是我又再填充5000条,刚才删除的数据,我想这个表的大小会变化吗. 数据库文件的大小会如何变化呢, 求解答.

70 个解决方案

#1

如果空间足够存入这新增的5000条，大小应该不会变化很明显，不过一些元数据会改变、索引等信息也会改变，所以不排除会变化。

#2

逻辑上，删除后的空余空间足够存储这部分数据时，空间不会增大

#3

引用 2 楼 DBA_Huangzj 的回复:

逻辑上，删除后的空余空间足够存储这部分数据时，空间不会增大

情况是这样的我接受的数据库,有一个大小是525G ,我删除了很多数据之后还是525G,但是我做收缩的时候 老是失败...我也就不收缩了 

#4

收缩失败是什么报错？你要收缩数据文件的话，先重建一下大表的聚集索引

#5

delete数据后，会有ghost record，不一定马上清除的

#6

另外要分清525G是主要由数据文件产生的还是日志文件产生的

#7

日字文件我已经删除了的,我使用的是简单模式.525G重建索引 会不会有点慢哦

#8

引用 5 楼 DBA_Huangzj 的回复:

delete数据后，会有ghost record，不一定马上清除的

name rows reserved data index_size unused
STOCK 2720144809 170584496 KB 169145272 KB 1382808 KB 56416 KB
SALES 2761741296 114223816 KB 113301496 KB 879560 KB 42760 KB
TRANS 80095062 10007264 KB 3216056 KB 6616032 KB 175176 KB
ORDER 171336977 13644464 KB 8439864 KB 5172096 KB 32504 KB
Item 287137970 39221512 KB 38983240 KB 218384 KB 19888 KB
SUM_SALES 127987380 2757568 KB 2697848 KB 30088 KB 29632 KB
看下我用sp_spaceused'tb'出来的结果

#9

直接重建会慢，可以用联机重建的方式，并且在闲时重建，另外删除日志文件相当危险，不建议这样做

#10

总之，重建聚集索引，再进行数据文件收缩，每次收缩100~500M，不要一次收缩太大，否则会出现问题的

#11

针对重点的几个大表重建索引试试..

DBCC DBREINDEX('[数据库名].dbo.[表名]', '', 90)

#12

引用 8 楼 jadilee 的回复:

Quote: 引用 5 楼 DBA_Huangzj 的回复:

delete数据后，会有ghost record，不一定马上清除的

name rows reserved data index_size unused
STOCK 2720144809 170584496 KB 169145272 KB 1382808 KB 56416 KB
SALES 2761741296 114223816 KB 113301496 KB 879560 KB 42760 KB
TRANS 80095062 10007264 KB 3216056 KB 6616032 KB 175176 KB
ORDER 171336977 13644464 KB 8439864 KB 5172096 KB 32504 KB
Item 287137970 39221512 KB 38983240 KB 218384 KB 19888 KB
SUM_SALES 127987380 2757568 KB 2697848 KB 30088 KB 29632 KB
看下我用sp_spaceused'tb'出来的结果

看了你的这个数据。
你也就是 TRANS 和ORDER 表删了几G数据，
当然 收缩不了多少空间了。。
然后也没必要收缩，，你每天有数据的话，几G数据 很快就填充了。

 -- 麻烦楼主，贴一下这个结果

SELECT df.[file_id]        
           ,df.[size]
           ,(CAST(df.[size] AS BIGINT) * 8 / 1024.0) AS [ziseMB]
           ,(size-FILEPROPERTY(df.name,'SpaceUsed'))/128.0 as free_space_MB
           ,df.max_size,
           df.[growth],
           df.is_percent_growth 
             ,df.[name]
           ,df.physical_name
    FROM   sys.database_files df
           LEFT JOIN sys.filegroups f
                ON  df.data_space_id = f.data_space_id
             CROSS APPLY(
				select name,suser_sname(owner_sid) AS ownerName,d.create_date,recovery_model_desc
				from sys.databases d WHERE d.name=DB_NAME()
             ) r1

#13

引用 12 楼 feiazifeiazi 的回复:

Quote: 引用 8 楼 jadilee 的回复:

Quote: 引用 5 楼 DBA_Huangzj 的回复:

  

结果有点多

#14

file_id size ziseMB free_space_MB max_size growth is_percent_growth name physical_name
1 68846392 537862.4375 498565.0625 -1 128 0 DATA F:\DataFile\DATA.mdf
2 435056 3398.875 3358.640625 268435456 10 1 DATA_log D:\DataFile\DATA_log.ldf
3 3840 30 29.9375 -1 1280 0 DATA201105 F:\DataFile\DATA201105.ndf
4 3072 24 23.9375 -1 1280 0 DATA201106 F:\DataFile\DATA201106.ndf
5 3840 30 29.9375 -1 1280 0 DATA201107 F:\DataFile\DATA201107.ndf
6 2178176 17017 7750.875 -1 1280 0 IMPORT D:\DataFile\IMPORT.ndf
7 128 1 0.9375 -1 1280 0 DATA201001 F:\DataFile\DATA201001.ndf
8 128 1 0.9375 -1 1280 0 DATA201002 F:\DataFile\DATA201002.ndf
9 128 1 0.9375 -1 1280 0 DATA201003 F:\DataFile\DATA201003.ndf
10 128 1 0.9375 -1 1280 0 DATA201004 F:\DataFile\DATA201004.ndf
11 256 2 1.875 -1 1280 0 DATA201101 F:\DataFile\DATA201101.ndf
12 768 6 5.9375 -1 1280 0 DATA201102 F:\DataFile\DATA201102.ndf
13 896 7 6.9375 -1 1280 0 DATA201103 F:\DataFile\DATA201103.ndf
14 768 6 5.9375 -1 1280 0 DATA201104 F:\DataFile\DATA201104.ndf
15 128 1 0.9375 -1 1280 0 DATA201005 F:\DataFile\DATA201005.ndf
16 128 1 0.9375 -1 1280 0 DATA201006 F:\DataFile\DATA201006.ndf
17 128 1 0.9375 -1 1280 0 DATA201007 F:\DataFile\DATA201007.ndf
18 128 1 0.9375 -1 1280 0 DATA201008 F:\DataFile\DATA201008.ndf
19 3840 30 29.9375 -1 1280 0 DATA201108 F:\DataFile\DATA201108.ndf
20 3072 24 23.9375 -1 1280 0 DATA201109 F:\DataFile\DATA201109.ndf
21 3328 26 25.9375 -1 1280 0 DATA201110 F:\DataFile\DATA201110.ndf
22 3072 24 23.9375 -1 1280 0 DATA201111 F:\DataFile\DATA201111.ndf
23 3328 26 19.75 -1 1280 0 DATA201112 F:\DataFile\DATA201112.ndf
24 1158040 9047.1875 8557.5625 -1 1280 0 DATA201201 F:\DataFile\DATA201201.ndf
25 1120016 8750.125 0 -1 1280 0 DATA201202 F:\DataFile\DATA201202.ndf
26 1202536 9394.8125 8.125 -1 1280 0 DATA201203 F:\DataFile\DATA201203.ndf
27 1202816 9397 7.6875 -1 1280 0 DATA201204 F:\DataFile\DATA201204.ndf
28 1280760 10005.9375 2.8125 -1 1280 0 DATA201205 F:\DataFile\DATA201205.ndf
29 1259152 9837.125 4.25 -1 1280 0 DATA201206 F:\DataFile\DATA201206.ndf
30 1315504 10277.375 1.6875 -1 1280 0 DATA201207 F:\DataFile\DATA201207.ndf
31 1337272 10447.4375 0.625 -1 1280 0 DATA201208 F:\DataFile\DATA201208.ndf
32 1288080 10063.125 1.375 -1 1280 0 DATA201209 F:\DataFile\DATA201209.ndf
33 1247784 9748.3125 3.4375 -1 1280 0 DATA201210 F:\DataFile\DATA201210.ndf
34 1192056 9312.9375 6.0625 -1 1280 0 DATA201211 F:\DataFile\DATA201211.ndf
35 1221600 9543.75 2.75 -1 1280 0 DATA201212 F:\DataFile\DATA201212.ndf
36 1573784 12295.1875 5.875 -1 1280 0 DATA201301 F:\DataFile\DATA201301.ndf
37 1103360 8620 5.625 -1 1280 0 DATA201302 F:\DataFile\DATA201302.ndf
38 1518080 11860 1.3125 -1 1280 0 DATA201303 F:\DataFile\DATA201303.ndf
39 1539840 12030 6.9375 -1 1280 0 DATA201304 F:\DataFile\DATA201304.ndf
40 1619200 12650 8.75 -1 1280 0 DATA201305 F:\DataFile\DATA201305.ndf
41 1624320 12690 1.125 -1 1280 0 DATA201306 F:\DataFile\DATA201306.ndf
42 1644800 12850 8.625 -1 1280 0 DATA201307 F:\DataFile\DATA201307.ndf
43 1693440 13230 3.8125 -1 1280 0 DATA201308 F:\DataFile\DATA201308.ndf
44 1656320 12940 7.625 -1 1280 0 DATA201309 F:\DataFile\DATA201309.ndf
45 1697720 13263.4375 0.0625 -1 1280 0 DATA201310 F:\DataFile\DATA201310.ndf
46 1463040 11430 5.9375 -1 1280 0 DATA201311 F:\DataFile\DATA201311.ndf
47 1702400 13300 5.1875 -1 1280 0 DATA201312 F:\DataFile\DATA201312.ndf
48 1900800 14850 1.3125 -1 1280 0 DATA201401 F:\DataFile\DATA201401.ndf
49 1649920 12890 16.8125 -1 1280 0 DATA201402 F:\DataFile\DATA201402.ndf
50 1864960 14570 1.25 -1 1280 0 DATA201403 F:\DataFile\DATA201403.ndf
51 1832960 14320 2.75 -1 1280 0 DATA201404 F:\DataFile\DATA201404.ndf
52 1804800 14100 8.3125 -1 1280 0 DATA201405 F:\DataFile\DATA201405.ndf
53 1251840 9780 4 -1 1280 0 DATA201406 F:\DataFile\DATA201406.ndf
54 3840 30 29.9375 -1 1280 0 DATA201407 F:\DataFile\DATA201407.ndf

#15

引用 9 楼 DBA_Huangzj 的回复:

直接重建会慢，可以用联机重建的方式，并且在闲时重建，另外删除日志文件相当危险，不建议这样做

我删除日志文件 是指   shrink 不是网上那种牛逼的删掉LOG文件

#16

那叫收缩，不叫删除

引用 15 楼 jadilee 的回复:

Quote: 引用 9 楼 DBA_Huangzj 的回复:

直接重建会慢，可以用联机重建的方式，并且在闲时重建，另外删除日志文件相当危险，不建议这样做

我删除日志文件 是指   shrink 不是网上那种牛逼的删掉LOG文件

#17

一个月一个次要文件略微不妥，一年或者半年还差不多，另外2011年那些整合成一个文件都不会很大

#18

file_id size ziseMB free_space_MB max_size growth is_percent_growth name physical_name
1 68846392 537862.437500 498565.062500 -1 128 0 DATA F:\DataFile\DATA.mdf
这个文件就是我想解决的文件....不知道为啥这么大了...

#19

2011年的数据已经在数据库里删除了,保留了文本文档在其他地方.
这个查数据偏重于 昨天, 本周,当月,去年同期 这一类的. 
这个是做了按天分区的,后来分区超过1000的时候就会出现提醒,分区不够了
于是我删除了数据,修改了分区函数,然后把11年的分区合并和.但是这些文件我应该怎么搞,

#20

因为是mdf，默认情况下，如果你创建对象没有指定文件组，会全部写入主文件组中，而无论是否指定文件组，元数据信息还是会写入主文件组中，如果你的所有文件都在一个文件组中，会按空余空间的百分比等比例写入各个文件，容易造成hot-page等问题。

#21

引用 20 楼 DBA_Huangzj 的回复:

因为是mdf，默认情况下，如果你创建对象没有指定文件组，会全部写入主文件组中，而无论是否指定文件组，元数据信息还是会写入主文件组中，如果你的所有文件都在一个文件组中，会按空余空间的百分比等比例写入各个文件，容易造成hot-page等问题。

所以我应该去找下每一个表都生产生在那个文件组上面的吗?
说个大概方向我去弄弄嘛

#22

1、先检查你的库有多少个文件组。
2、检查一下表在哪些文件组中。右键某个表→属性→存储，可以看到

#23

重点关注大表

#24

引用 23 楼 DBA_Huangzj 的回复:

重点关注大表

DATA这个文件空闲的空间应该是来源于
name rows reserved date index_size unused
PPPitemDaily 216030747   40619624 KB 24553648 KB 15750096 KB 315880 KB
PPPDailyCount 1174286172  288415120 KB 238117104 KB 50248104 KB 49912 KB

这两个表被我truncate了. 这里面记录的东西使用率不是很高,我干掉了

#25

truncate之后可以收缩一下这个文件

#26

引用 25 楼 DBA_Huangzj 的回复:

truncate之后可以收缩一下这个文件

我TRUNCATE之后...我就把表DROP了.....扎个办?

#27

没用的话drop也没所谓，一样可以收缩

#28

但是收缩4,5个小时候就 就显示失败...我不想再尝试了.

#29

引用 3 楼 jadilee 的回复:

Quote: 引用 2 楼 DBA_Huangzj 的回复:

逻辑上，删除后的空余空间足够存储这部分数据时，空间不会增大

情况是这样的我接受的数据库,有一个大小是525G ,我删除了很多数据之后还是525G,但是我做收缩的时候 老是失败...我也就不收缩了 

收缩报了什么错 还没说吧。

  
-- 收缩语句，会执行很慢的。。。(数据会大量的在移动)
DBCC SHRINKFILE (1 , 102400) --将fileid为2收缩到100GB
GO


DBCC SHRINKFILE (1 , 102400) --将fileid为1收缩到100GB
GO

#30

该回复于2014-07-01 09:13:20被管理员删除

#31

该回复于2014-07-01 07:52:01被版主删除

#32

引用 28 楼 jadilee 的回复:

但是收缩4,5个小时候就 就显示失败...我不想再尝试了.

4~5个小时？你一次收缩多少啊？一次100M就差不多拉，1G一次都比较容易导致超时。

#33

文件大小并不一定成比例

#34

我

引用 32 楼 DBA_Huangzj 的回复:

Quote: 引用 28 楼 jadilee 的回复:

但是收缩4,5个小时候就 就显示失败...我不想再尝试了.

4~5个小时？你一次收缩多少啊？一次100M就差不多拉，1G一次都比较容易导致超时。

我收缩的时候一般是直接把空闲空间找到了,直接收缩.
537,862   最小为39464
我就填写成 缩小到39464

#35

引用 29 楼 feiazifeiazi 的回复:

Quote: 引用 3 楼 jadilee 的回复:

Quote: 引用 2 楼 DBA_Huangzj 的回复:

逻辑上，删除后的空余空间足够存储这部分数据时，空间不会增大

情况是这样的我接受的数据库,有一个大小是525G ,我删除了很多数据之后还是525G,但是我做收缩的时候 老是失败...我也就不收缩了 

收缩报了什么错 还没说吧。

  
-- 收缩语句，会执行很慢的。。。(数据会大量的在移动)
DBCC SHRINKFILE (1 , 102400) --将fileid为2收缩到100GB
GO


DBCC SHRINKFILE (1 , 102400) --将fileid为1收缩到100GB
GO

先释放空间,然后点收缩对吗?
上次出错,我顺手就点了...忘记切图了.

#36

引用 34 楼 jadilee 的回复:

我

Quote: 引用 32 楼 DBA_Huangzj 的回复:

Quote: 引用 28 楼 jadilee 的回复:

但是收缩4,5个小时候就 就显示失败...我不想再尝试了.

4~5个小时？你一次收缩多少啊？一次100M就差不多拉，1G一次都比较容易导致超时。

我收缩的时候一般是直接把空闲空间找到了,直接收缩.
537,862   最小为39464
我就填写成 缩小到39464

那肯定死的人多了，收缩是极其消耗资源的操作，并且导致整个库最多只读，严重情况下无法访问。还是那句，每次100~500M，用个while循环反而快很多

#37

这个不太好说啊

#38

引用 36 楼 DBA_Huangzj 的回复:

Quote: 引用 34 楼 jadilee 的回复:

我
 用个while循环反而快很多

等于给找个数据库文件来个"凌迟" 一点一点的削 好哇,今天晚上我试试.

#39

如果每次收缩50~100M，你现在就可以收缩

#40

行啊 我试试52MB的 嘿嘿 行了  . 大兄弟,谢谢啦
 

引用 39 楼 DBA_Huangzj 的回复:

如果每次收缩50~100M，你现在就可以收缩

我写了个脚本你看看 是这样的对不对

declare @StartSize int 
DECLARE @EndSize int
declare @step int

SET @StartSize=537802
SET @EndSize=40000*2
set @step=50

while @n<=@EndSize begin
set @StartSize=@StartSize-@step
DBCC SHRINKFILE (1 , @StartSize)

WAITFOR  DELAY 5 second

end

#41

你先看看现在有多大，然后能收缩到多少（你这个库的规模建议预留10G左右的空间，不要收缩得太少了，一旦插入数据又要重新分配，影响性能得不偿失），在你本地开一个库运行一下就好了

#42

引用 41 楼 DBA_Huangzj 的回复:

你先看看现在有多大，然后能收缩到多少（你这个库的规模建议预留10G左右的空间，不要收缩得太少了，一旦插入数据又要重新分配，影响性能得不偿失），在你本地开一个库运行一下就好了

行啊,那 回头我试试哈.  那里改成40000*3 就行了这个很方便来着.
我只是不确定 收缩数据库是这样写的咩DBCC SHRINKFILE (1 , @StartSize)
1代表的应该就是那个DATA

#43

大数据的话收缩没有下面这样快吧：
新开一个数据库，直接指定大小为比估计收缩后的略大。
复制表结构，先不要主键、索引。
块复制数据，按主键序。
创建主键、索引。
删除旧数据库，改用新数据库。

#44

引用 43 楼 Tiger_Zhao 的回复:

大数据的话收缩没有下面这样快吧：
新开一个数据库，直接指定大小为比估计收缩后的略大。
复制表结构，先不要主键、索引。
块复制数据，按主键序。
创建主键、索引。
删除旧数据库，改用新数据库。

赵兄弟,是这样的,我接手这个数据库的时候. 这个数据库文件分成三部分
primery, data, YYYYMM(文件组)
这三个. 现在开始清理时候 名叫data的文件 大概是525G  但是实际大小只有200G多点, 我就想把这个文件弄小的.
这个DATA文件包括的表太多了....

#45

declare @StartSize int 
DECLARE @EndSize int
declare @step int

SET @StartSize=537650
SET @EndSize=537550
set @step=50

while @StartSize<=@EndSize
 begin
set @StartSize=@StartSize-@step
DBCC SHRINKFILE (1 , @StartSize)
 
 WAITFOR DELAY '00:00:03'
end


我执行这个语句是直接一闪而过...没有任何效果

#46

最后两列的数据没有什么变化,为什么呢,这个情况我要如何处理呢,

#47

引用 46 楼 jadilee 的回复:

最后两列的数据没有什么变化,为什么呢,这个情况我要如何处理呢,

第三列不是在减少了吗？

#48

引用 45 楼 jadilee 的回复:

declare @StartSize int 
DECLARE @EndSize int
declare @step int

SET @StartSize=537650
SET @EndSize=537550
set @step=50

while @StartSize<=@EndSize
 begin
set @StartSize=@StartSize-@step
DBCC SHRINKFILE (1 , @StartSize)
 
 WAITFOR DELAY '00:00:03'
end


我执行这个语句是直接一闪而过...没有任何效果

你这语句也就执行了两次而已吧

#49

发现问题了，你的endsize根本没用到啊。逻辑有错自己改改吧

#50

引用 44 楼 jadilee 的回复:

赵兄弟,是这样的,我接手这个数据库的时候. 这个数据库文件分成三部分
primery, data, YYYYMM(文件组)
这三个. 现在开始清理时候 名叫data的文件 大概是525G  但是实际大小只有200G多点, 我就想把这个文件弄小的.
这个DATA文件包括的表太多了....

如果这是数据大头，那么先备份、删除数据库重建（不用建表）、最好恢复数据库，这样也能达到整理空间的目的。

表多不是问题。
从 SYSOBJECTS 可以得到所有的用户表名，在 Excel 中用公式生成 SQL 脚本很方便的。
如果要区分文件，只需关联上 SYSINDEXES、SYSFILES 表。