笔记9-徐 DBCC SHRINKFILE不起作用的原因
1 --DBCC SHRINKFILE不起作用的原因
2
3
4 --由于sp_spaceused这个存储过程依赖于SQLSERVER存储在一些系统视图里的空间使用统计信息来算出结果，但是SQLSERVER并不保证实时更新空间使用统计信息。尤其是数据库刚刚发生大的变化之后，sp_spaceused的结果常常不准确
5
6 --可以在SSMS里，在数据库上点右键-》报表-》标准报表-》
7 --磁盘使用情况、
8 --按排在前面的表的磁盘使用情况、
9 --按表的磁盘使用情况、
10 --按分区的磁盘使用情况
11 --4个报表来从不同角度统计出数据库空间使用情况
12
13
14 CREATE DATABASE test_shrink
15 GO
16 USE test_shrink
17 GO
18 CREATE TABLE show_extent(a INT,b NVARCHAR(3900))
19 GO
20 USE test_shrink
21 DECLARE @i INT
22 SET @i=1
23 WHILE @i<=1000
24 BEGIN
25 INSERT INTO show_extent VALUES(1,REPLICATE(N'a',3900))
26 INSERT INTO show_extent VALUES(2,REPLICATE(N'b',3900))
27 INSERT INTO show_extent VALUES(3,REPLICATE(N'c',3900))
28 INSERT INTO show_extent VALUES(4,REPLICATE(N'd',3900))
29 INSERT INTO show_extent VALUES(5,REPLICATE(N'e',3900))
30 INSERT INTO show_extent VALUES(6,REPLICATE(N'f',3900))
31 INSERT INTO show_extent VALUES(7,REPLICATE(N'g',3900))
32 INSERT INTO show_extent VALUES(8,REPLICATE(N'h',3900))
33 SET @i=@i+1
34 END
35 DBCC SHOWCONTIG('show_extent')
36 GO
37
38 ------------------------------
39 --删除a列不是5的数据
40 DELETE dbo.show_extent WHERE a<>5
41 --显示数据文件有64MB
42 EXEC sys.sp_spaceused @objname = N'show_extent' -- nvarchar(776)
43
44 DBCC SHOWCONTIG('show_extent')
45
46 ----------------------------------------
47 --收缩文件
48 EXEC sys.sp_helpfile
49 DBCC SHRINKFILE(2,40) --fileid为1 收缩到40MB
50 --DBCC SHRINKFILE
51 --(
52 -- { file_name | file_id }
53 -- { [ , EMPTYFILE ]
54 -- | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
55 -- }
56 --)
57
58 --建立索引释放没有使用的区
59 CREATE CLUSTERED INDEX show_I ON dbo.show_extent(a)
60 DBCC SHOWCONTIG('show_extent')
61 DBCC SHRINKFILE(1,1)
62 EXEC sys.sp_spaceused @objname = N'show_extent' -- nvarchar(776)
63
64 SELECT * FROM dbo.show_extent
65
66
67 --找出每个区的对象理论上区数目和实际数目,然后重建大对象类型的表
68 USE test_shrink
69 GO
70 DROP TABLE #extentinfo
71 GO
72 CREATE TABLE #extentinfo
73 (
74 [file_id] SMALLINT,
75 page_id INT,
76 pg_alloc INT,
77 ext_size INT,
78 obj_id INT,
79 index_id INT,
80 partition_number INT,
81 partition_id BIGINT,
82 iam_chain_type VARCHAR(50),
83 pfs_bytes VARBINARY(10)
84 )
85 GO
86 DROP PROC import_extentinfo
87 GO
88 CREATE PROCEDURE import_extentinfo
89 AS
90 DBCC extentinfo('test_shrink')
91 GO
92 INSERT INTO #extentinfo EXEC import_extentinfo
93 GO
94
95 SELECT [file_id],obj_id,index_id,partition_id,ext_size,
96 'actual extent count'=COUNT(*),'actual page count'=SUM(pg_alloc),
97 'possible extent count'=ceiling(SUM(pg_alloc)*1.0/ext_size),
98 'possible extents/actual extents'=
99 (ceiling(SUM(pg_alloc)*1.00/ext_size)*100.00)/COUNT(*)
100 FROM #extentinfo
101 GROUP BY file_id,obj_id,index_id,partition_id,ext_size
102 HAVING COUNT(*) -ceiling(SUM(pg_alloc)*1.0/ext_size)>0
103 ORDER BY partition_id,obj_id,index_id,file_id
104
105 --DBCC SHRINKFILE清空文件，不收缩到指定的大小
106 USE GPOSDB --要收缩的数据库名
107 DBCC SHRINKFILE(1,EMPTYFILE)
