當你的sql server instance 裡頭有很多資料庫，或者資料庫內的file group很多的話，不透過程式來管理，我都不知道一個DBA是怎麼跟人家說，恩....他有在看系統!! 不會有說服力的.....這是我每天執行空間例行檢查所寫的一段T-SQL，處理所有資料庫內file group 的空間檢核，程式的output 是準備要給Nagios 進行處理的，有興趣的人，可以依據貴司的需求改成你要的版本，從2008~2016都可應用，因為我手上的系統就是這些版本
IF OBJECT_ID('tempdb..#G_DB_FG_LIST') IS NOT NULL
DROP TABLE #G_DB_FG_LIST;
create table #G_DB_FG_LIST (current_db varchar(100), file_group varchar(100), allcoate_mb int, used_mb int);
exec sp_MSforeachdb 'use [?];
begin
DECLARE @DB_FG_LIST TABLE (current_db varchar(100), file_group varchar(100), allcoate_mb int, used_mb int)
DECLARE @current_db varchar(100);
DECLARE @file_group varchar(100);
DECLARE @allcoate_mb int;
DECLARE @used_mb int;
INSERT @DB_FG_LIST select db_name() , fg.name , sum(size/128),
sum(CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT)/128)
From sys.database_files A LEFT JOIN sys.filegroups fg
ON A.data_space_id = fg.data_space_id group by fg.name;
DECLARE fg_space_cursor CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT current_db, file_group, allcoate_mb, used_mb FROM @DB_FG_LIST where allcoate_mb >= 1000 and used_mb >= 500 ;
-- Open the cursor
OPEN fg_space_cursor;
-- Retrieve one row at a time from the cursor
FETCH NEXT
FROM fg_space_cursor
INTO @current_db,@file_group,@allcoate_mb,@used_mb ;
-- Process and retrieve new rows until no more are available
WHILE @@FETCH_STATUS = 0
BEGIN
if (@used_mb*100/@allcoate_mb) >= 80
insert into #G_DB_FG_LIST
values(@current_db,@file_group,@allcoate_mb,@used_mb);
FETCH NEXT
FROM fg_space_cursor
INTO @current_db,@file_group,@allcoate_mb,@used_mb ;
END
-- Close the cursor
CLOSE fg_space_cursor;
-- Deallocate the cursor
DEALLOCATE fg_space_cursor;
end'
DECLARE @outputinfo varchar(MAX);
DECLARE fg_space_cursor CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT current_db, isnull(file_group,'DBTXNLOG') file_group , allcoate_mb, used_mb FROM #G_DB_FG_LIST;
DECLARE @current_db varchar(100);
DECLARE @file_group varchar(100);
DECLARE @allcoate_mb int;
DECLARE @used_mb int;
set @outputinfo = N'N';
OPEN fg_space_cursor;
FETCH NEXT
FROM fg_space_cursor
INTO @current_db,@file_group,@allcoate_mb,@used_mb;
WHILE @@FETCH_STATUS = 0
begin
set @outputinfo = @outputinfo + ':' + @current_db + '/'+ @file_group + '/' + cast(@allcoate_mb as varchar) + '/' + cast(@used_mb as varchar);
FETCH NEXT
FROM fg_space_cursor
INTO @current_db,@file_group,@allcoate_mb,@used_mb ;
end
-- Close the cursor
CLOSE fg_space_cursor;
-- Deallocate the cursor
DEALLOCATE fg_space_cursor;
if len(@outputinfo) = 1
print N'Y';
else
print @outputinfo;