第四章 SQLserver磁盘空间管理

本文主要是介绍第四章 SQLserver磁盘空间管理，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、查看磁盘使用情况

--磁盘使用情况
WITH T1 AS (
SELECT DISTINCT
REPLACE(vs.volume_mount_point,':\','') AS Drive_Name ,
CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Total_Space_GB ,
CAST(vs.available_bytes / 1024.0 / 1024 / 1024  AS NUMERIC(18,2)) AS Free_Space_GB
FROM    sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
)
SELECT
Drive_Name,
Total_Space_GB,
Total_Space_GB-Free_Space_GB AS Used_Space_GB,
Free_Space_GB,
CAST(Free_Space_GB*100/Total_Space_GB AS NUMERIC(18,2)) AS Free_Space_Percent
FROM T1

二、数据库空间使用情况

--数据库空间使用情况
USE TEMP_DB
 
GO
 
SELECT a.name as 逻辑文件名, size/128/1024 [totalspace文件大小(G)], 
 
    FILEPROPERTY(a.name, 'SpaceUsed')/128/1024 [usedspace已用空间(G)],
 
    size/128/1024 - FILEPROPERTY(a.name, 'SpaceUsed')/128/1024 [未用空间(G)],
 
    FILEPROPERTY(a.name, 'SpaceUsed')*100.0/size [使用率(%)]
 
FROM sys.database_files a cross join (select recovery_model_desc, log_reuse_wait,log_reuse_wait_desc,is_auto_shrink_on  from sys.databases where name=DB_NAME())b

三、各表空间占用情况

--各表空间占用情况
CREATE TABLE #T (NAME nvarchar(100),ROWS char(20),reserved varchar(18) ,Data varchar(18) ,index_size varchar(18) ,Unused varchar(18) ) 
GO 
INSERT #T EXEC SP_MSFOREACHTABLE 'EXEC sp_spaceused "?"' 
SELECT * FROM #T ORDER BY CONVERT(INT,REPLACE(DATA,'kb','')) DESC

四、收缩日志文件大小

DBCC SHRINKFILE (N'TEMP_DB_log' , 1, TRUNCATEONLY)--收缩日志文件大小到1M

五、查看数据库总空间

--1.查看数据库总空间，已用空间，未用空间，使用率
USE BDL
 
GO
 
SELECT a.name as 逻辑文件名, size/128 [totalspace文件大小(兆)], 
 
    FILEPROPERTY(a.name, 'SpaceUsed')/128 [usedspace已用空间(兆)],
 
    size/128 - FILEPROPERTY(a.name, 'SpaceUsed')/128 [未用空间(兆)],
 
    FILEPROPERTY(a.name, 'SpaceUsed')*100.0/size [使用率(%)]
 
FROM sys.database_files a cross join (select recovery_model_desc, log_reuse_wait,log_reuse_wait_desc,is_auto_shrink_on  from sys.databases where name=DB_NAME())b
 
WHERE type=0

六、回收磁盘空间

--2.进行回收
declare @usedspace int ,@totalspace int
 
select @usedspace= 200530,@totalspace =407006
 
while @totalspace> @usedspace
 
begin
 
	set @totalspace= @totalspace-5 *1024
 
	DBCC SHRINKFILE( BDL,@totalspace )
 
end

这篇关于第四章 SQLserver磁盘空间管理的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！