-- 一時テーブルを作成
CREATE TABLE #tmp (
    DatabaseName NVARCHAR(128),
    LogSize_MB DECIMAL(18, 2),
    LogSpaceUsed_PERCENTAGE DECIMAL(18, 2),
    Status INT
);


CREATE TABLE #DiskUsageLog (
    DatabaseName NVARCHAR(128),
    LogSize_MB DECIMAL(18, 2),
);


CREATE TABLE #DiskUsageData (
     name NVARCHAR(128),
     rows CHAR(11),
     reserved VARCHAR(18),
     data VARCHAR(18),
     index_size VARCHAR(18),
     unused VARCHAR(18)
);


-- データの削除
TRUNCATE TABLE #tmp
TRUNCATE TABLE #DiskUsageLog
TRUNCATE TABLE #DiskUsageData


-- DBCC SQLPERF(LOGSPACE)の結果をtmpに挿入
INSERT INTO #tmp
EXEC ('DBCC SQLPERF(LOGSPACE)');


-- Logのデータを挿入
INSERT INTO #DiskUsageLog
SELECT DatabaseName, LogSize_MB FROM #tmp


-- Dataのデータを挿入し変換
INSERT INTO #DiskUsageData
     EXEC sp_MSforeachtable 'sp_spaceused ''?''';


-- データファイルのサイズ確認
SELECT name AS Logical_Name,
       type_desc AS Type,
   size * 8 / 1024 AS Size 
FROM sys.database_files;


-- 実際のログのサイズ確認
SELECT * FROM #DiskUsageLog
WHERE DatabaseName = DB_NAME();


-- 実際のデータのサイズ確認
SELECT 
    DB_NAME() AS DatabaseName,
    SUM(CAST(REPLACE(reserved, ' KB', '') AS INT)) / 1024 AS TotalReservedMB,
    SUM(CAST(REPLACE(data, ' KB', '') AS INT)) / 1024 AS TotalDataMB,
    SUM(CAST(REPLACE(index_size, ' KB', '') AS INT)) / 1024 AS TotalIndexSizeMB
FROM #DiskUsageData;