I use the following procedure that lists space used for each database by space free per file group and space used per table. I also have it raise an error if the number of extents remaining on the data file group starts running low.
CREATE procedure checksizes as
-- Create the temp table for further querying
CREATE TABLE #TableTemp(
acquired datetime,
DatabaseName varchar(50),
rec_id int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
db_size decimal(15,2))
declare @dbname varchar(25)
declare @sql varchar(1000)
declare dbcursor cursor for select name from master..sysdatabases where dbid >4
EXEC sp_MSforeachdb @command1="print '?' DBCC updateusage ('?')"
open dbcursor
fetch next from dbcursor into @dbname
while @@fetch_status = 0
begin
-- Get all tables, names, and sizes
set @sql = 'USE '+@DBNAME+' EXEC sp_msforeachtable @command1="insert into #TableTemp(nbr_of_rows, data_space, index_space) exec sp_mstablespace ''' + '?'+''''+'",@command2="update #TableTemp set table_name = '''+'?'+''' where rec_id = (select max(rec_id)
from #TableTemp)"'
exec (@sql)
update #TableTemp set databasename = @dbname, acquired=(select getdate()),total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #TableTemp where databasename is null) where databasename is null
fetch next from dbcursor into @dbname
end
close dbcursor
deallocate dbcursor
insert into tablesizes SELECT * FROM #TableTemp where databasename + table_name + convert(varchar(12), nbr_of_rows) not in (select databasename+ table_name+ convert(varchar(12),nbr_of_rows) from tablesizes)
DROP TABLE #TableTemp
insert into dbsizes select getdate(), d.name, f.name, f.filename, f.size*8/1024, f.growth*8/1024, f.maxsize*8/1024, Extents = case when growth = 0 then 0 else (maxsize - size)/growth end, f.groupid,-1 from master..sysdatabases d, master..sysaltfiles f where f.dbid = d.dbid and f.dbid > 4
declare @groupid int
declare @extents int
declare @filename varchar(200)
declare @dbid int
declare @growth int
declare spaceused cursor for select name, dbname from dbsizes where spaceused = -1
open spaceused
fetch next from spaceused into @filename, @dbname
while @@fetch_status = 0
begin
set @sql = 'USE '+@dbname + ' update sqlmanager..dbsizes set spaceused = (SELECT FILEPROPERTY('''+@filename+''', ''spaceused'')*8/1024) where name = '''+@filename+''' and spaceused =-1'
exec (@sql)
fetch next from spaceused into @filename, @dbname
end
close spaceused
deallocate spaceused
declare groupcursor cursor for select groupid, dbname , sum(extents)
from dbsizes
group by groupid, dbname, acquired
having sum(extents) < 4
and sum(spaceused) > 0 and convert(decimal (9,2),sum(spaceused))/convert(decimal (9,2),sum(size))>.9
and convert(varchar(10),getdate(),101)=convert(varchar(10), acquired, 101)
open groupcursor
fetch next from groupcursor into @groupid, @dbname, @extents
while @@fetch_status = 0
begin
set @growth = (select max(growth) from dbsizes where dbname = @dbname and groupid = @groupid and size <> maxsize)
set @sql = 'File Group '+convert(varchar(2),@groupid)+ ' on database '+@@servername+'/'+@dbname+' has '+convert(varchar(2),@extents)+' extents of '+ convert(varchar(5),@growth) + 'MB and less than 10percent free'
raiserror(@sql,16,1) with log
--print @sql
fetch next from groupcursor into @groupid, @dbname, @extents
end
close groupcursor
deallocate groupcursor
--drop table #dbtemp
GO