MS SQL SERVER 資料庫使用空間查詢 @ Joseph A-Sa-BLue

select DB_NAME(database_id) N'資料庫',physical_name N'實體檔案',type_desc N'檔案類型',state_desc N'檔案狀態', size*8.0/1024 N'檔案大小(MB)' FROM sys.master_files

檔案大小(MB)= size*8.0/1024

ref:https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-ver16

http://sharedderrick.blogspot.com/2010/11/sql-server.html

因要取得實際資料量

參考

http://sharedderrick.blogspot.com/2008/01/displays-disk-space-reserved-and-used.html

單一資料庫查詢

利用了 sys.databases 產生一個 CURSOR 逐一查詢 , 寫成StoredProcedure 並加入Schedule 定時的查詢並儲存

GO
/****** Object: StoredProcedure [dbo].[sp_DBUsed] Script Date: 2022/12/7 下午 03:34:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:       <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_DBUsed]
   -- Add the parameters for the stored procedure here
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

DECLARE @tDBSize TABLE
(DBName nvarchar(200) ,
Fileid INT, FileGroup INT, TotalExtents INT,
UsedExtents INT, Name sysname, FileName NVARCHAR(4000))

DECLARE @tLogSize TABLE
(DBName sysname, logsize float, used float, status int)

    DECLARE cur_dbname CURSOR
FOR select name from sys.databases
-- Insert statements for procedure here
   DECLARE @database sysname
   open cur_dbname;
   FETCH NEXT FROM cur_dbname INTO @database
   WHILE @@FETCH_STATUS = 0
BEGIN
       INSERT @tDBSize(Fileid,FileGroup,TotalExtents,UsedExtents,Name,FileName)
       EXEC ('Use '+@database +' DBCC showfilestats')
       update @tDBSize set DBName=@database where DBName is null;
       FETCH NEXT FROM cur_dbname INTO @database;
END;
   CLOSE cur_dbname;
   DEALLOCATE cur_dbname;
INSERT INTO @tLogSize
EXECUTE ('DBCC SQLPERF(LOGSPACE)')

insert DB_UsedSize
SELECT convert(nvarchar(20),getdate(),120) N'Dates',D.DBName N'DBName',D.Name N'FileName',
TotalExtents*64.0/1024 N'DataSpace_MB',
UsedExtents*64.0/1024 N'DataSpaceUsed_MB',
logsize N'LogSpace_MB',
(logsize*used/100) N'LogSpaceUsed_MB'
FROM @tLogSize L INNER JOIN @tDBSize D
ON L.DBName=D.DBName

END

改完後看到

http://sharedderrick.blogspot.com/2008/01/displays-disk-space-reserved-and-used_02.html