資料庫黑洞

資料庫空間檢查語法

-- 版本: 2005+
-- 說明: 資料庫空間檢查語法
-- ================================================
USE [資料庫名稱];
GO
SELECT
  @@SERVERNAME AS server_name,
  DB_NAME() AS database_name,
  CASE
    WHEN data_space_id = 0 THEN 'LOG'
    ELSE FILEGROUP_NAME(data_space_id)
  END AS file_group,
  name AS logical_name,
  physical_name,
  type_desc,
  FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS used_size_Mb,
  size / 128.0 AS allocated_size_mb,
  CASE
    WHEN max_size = -1 THEN max_size
    ELSE max_size / 128.0
  END AS max_size_Mb,
  growth,
  is_percent_growth
FROM sys.database_files
WHERE state_desc = 'ONLINE';

-- ================================================
-- 版本: 2000
-- 說明: 資料庫空間檢查語法
-- ================================================
USE [資料庫名稱];
GO
SELECT RTRIM(name) AS [Segment Name], groupid AS [Group Id], filename AS [File Name],
   CAST(size/128.0 AS DECIMAL(10,2)) AS [allocated_size_mb],
   CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [used_size_Mb],
   CAST([maxsize]/128.0 AS DECIMAL(10,2)) AS [Max in MB],
   CAST([maxsize]/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space in MB],
   CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST([maxsize]/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]
FROM sysfiles
ORDER BY groupid DESC;

 

Check List

1.檢查交易紀錄檔 ( 2005+: type_desc='LOG'，2000: filename副檔名 ldf ) 的資料，是否實際大小 (used_size_Mb) 過於龐大，如果是，請壓縮交易紀錄檔

-- 版本: 2005+
-- 壓縮交易紀錄檔
-- ================================================
DECLARE @logName VARCHAR(128);
DECLARE @databaseName VARCHAR(128);
SET @databaseName = '資料庫名稱';

SET @logName = (SELECT name FROM sys.master_files WHERE database_id = DB_ID(@databaseName) and type = 1);
EXEC('
	USE [' + @databaseName + ']
	ALTER DATABASE [' + @databaseName + '] SET RECOVERY SIMPLE WITH NO_WAIT
	DBCC SHRINKFILE(' + @logName + ', 1)
	ALTER DATABASE [' + @databaseName + '] SET RECOVERY FULL WITH NO_WAIT
');

-- ================================================
-- 版本: 2000
-- 壓縮交易紀錄檔
-- ================================================
-- 1.先找出 log 名稱
USE [資料庫名稱]
GO
SELECT name FROM sys.sysfiles WHERE fileid = 2;

--2.壓縮
DBCC SHRINKFILE(log名稱, 2);
BACKUP LOG 資料庫名稱 WITH TRUNCATE_ONLY;
DBCC SHRINKFILE(log名稱,2);

 

2.檢查主檔 ( 2005+: type_desc='ROWS' ，2000: filename副檔名 mdf ) 的資料，分配空間 (allocated_size_mb) 與實際大小 (used_size_Mb) 相減是否差異過大，如果是，請壓縮資料庫

-- 版本: 2000+
-- 壓縮資料庫
-- ================================================
USE [資料庫名稱]
GO
DBCC SHRINKDATABASE(N'資料庫名稱')
GO

 

 

參考資料

SQL Server数据库状态监控 - 可用空间

How to determine used/free space within SQL database files?

INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE

 

本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權，文章歡迎轉載，請註明出處，謝謝~~~