笔记9-徐 DBCC SHRINKFILE不起作用的原因

笔记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)

 

posted @ 2013-07-27 15:24 桦仔 阅读(...) 评论(...)  编辑 收藏