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
留言列表
