Monitor space used for disks, DBs, tables and filegroups
I pieced this script together from solutions I found on this and from Brian Knight's excellent book. It creates 3 tables:
- tblDriveLogs. Collects free drivespace info for each drive for each time the script is run.
- tblIndexLogs. Collects info on spaceused for each index and table. Please note that when only af single index exist, TableNm and IndexNm is the same. This is because of the way the info is organized in sysindexes.
- tblFileLogs. Keeps track of the sizes and spaceused in the filegroups and logs for each database.
My idea with this solution was to have way of tracking space and spaceused over time. I wanted to make a Access reporting tool og maybe a nice webpage with some cool stats of the data but haven't got around that yet.
Hope you can use the script. If you find any room for improovement or any stupidities please let me know. If you think it's great please let me also know 😉 Thanks.
/*
usp_MySpaceUsed
Tracks/Logs spaceusage on your disk, file/filegroups og tables/indexes
This script creates a database REPORTS, the tables and the stored
procedure that you can schedule to run evert day of whatever you
like.
Script by Steen Molberg
Build 100 - Created 10-7-2003
*/
use master
go
create database REPORTS
on
(
name=dfile1,
filename='D:\data\sql_d\reports_d1.mdf'
)
log on
(
name=lfile1,
filename='L:\sqllog\reports_l1.ldf'
)
go
use REPORTS
go
create table tblDriveLogs
(
[Date] smalldatetime default getdate(),
Drive varchar(1),
MBFree int not null
constraint PK_tblDriveLogs primary key ([Date],Drive)
)
go
create table tblFileLogs
(
[Date] smalldatetime default getdate(),
DbNm varchar(200) not null,
FgNm varchar(200) not null,
FileNm varchar(200) not null,
FilePath varchar(200) not null,
SizePages int not null,
UsedPages int not null
constraint PK_tblFileLogs primary key ([Date],DbNm,FgNm,FileNm)
)
go
create table tblIndexLogs
(
[Date] smalldatetime default getdate(),
DbNm varchar(200) not null,
FgNm varchar(200) not null,
TableNm varchar(200) not null,
IndexNm varchar(200) not null,
dpages int not null,
reserved int not null,
used int not null
constraint PK_tblIndexLogs primary key ([Date],DbNm,FgNm,TableNm,IndexNm)
)
go
create proc usp_MySpaceUsed
as
set nocount on
declare @LocalErr int
declare @SQL varchar(500)
declare @DbNm sysname
declare @FileNm sysname
declare @Size int
declare @Groupid int
declare @FilePath varchar(200)
declare @FgNm sysname
declare @IndexNm sysname
declare @TableNm sysname
declare @indid int
declare @dpages int
declare @reserved int
declare @used int
-- Record free space on drives
select @SQL =
'use Master
insert REPORTS.dbo.tblDriveLogs (Drive,MBFree) exec xp_fixeddrives'
exec (@SQL)
create table #Files
(
[filename] sysname,
[size] int,
[filepath] varchar(200),
groupname varchar(50)
)
create table #Indexes
(
IndexNm sysname,
TableNm sysname,
FgNm sysname,
indid int,
dpages int,
reserved int,
used int
)
-- Interate over Db-names
declare c1 cursor for
select [name] from master.dbo.sysdatabases
where has_dbaccess([name])=1
open c1
fetch c1 into @DbNm
while @@fetch_status=0
begin
insert #Files exec (
'use ' + @DbNm + ' '
+ 'select F.[name],F.[size],F.[filename],G.groupname
from ' + @DbNm + '.dbo.sysfiles F left join ' + @DbNm + '.dbo.sysfilegroups G
on F.groupid=G.groupid'
)
-- Record filesizes for all dbs on this server
declare c2 cursor for
select * from #Files
open c2
fetch c2 into @FileNm,@Size,@FilePath,@FgNm
while @@fetch_status=0
begin
select @SQL =
'use '+ RTRIM(@DbNm)
+ ' insert REPORTS.dbo.tblFileLogs ([Date],DbNm,FgNm,FileNm,FilePath,SizePages,UsedPages)'
+ ' values ('
+ 'getdate(),'
+ '''' + RTRIM(@DbNm) + ''','
+ '''' + isnull(@FgNm,'LOG') + ''','
+ '''' + RTRIM(@FileNm) + ''','
+ '''' + RTRIM(@FilePath) + ''','
+ cast(@Size as varchar(10)) + ',CAST(FILEPROPERTY(''' + RTRIM(@FileNm) + ''', ''SpaceUsed'') as int)/8)'
exec (@SQL)
fetch c2 into @FileNm,@Size,@FilePath,@FgNm
end
deallocate c2
delete from #Files
-- Record Table and Indexes sizes
insert #Indexes exec (
'use ' + @DbNm + ' '
+ ' select I.name,T.name,G.groupname,I.indid,I.dpages,I.reserved,used'
+ ' from sysindexes I'
+ ' join sysobjects T on I.id=T.id'
+ ' join sysfilegroups G on I.groupid=G.groupid'
+ ' where I.indid in (0,1,255)'
+ ' union all'
+ ' select I.name,T.name,G.groupname,I.indid,I.dpages,I.reserved,used'
+ ' from sysindexes I'
+ ' join sysobjects T on I.id=T.id'
+ ' join sysfilegroups G on I.groupid=G.groupid'
+ ' where I.indid not in (0,1,255)'
+ ' and I.groupid<>(select X.groupid from sysindexes X where (I.id=X.id) and (X.indid in (0,1)))'
)
update #Indexes
set IndexNm=TableNm
where indid in (0,1)
update #Indexes
set dpages=dpages-S.sumdpages, reserved=reserved-S.sumreserved, used=used-S.sumused
from #Indexes I
join
(select X.TableNm, sum(X.dpages) sumdpages, sum(X.reserved) sumreserved, sum(X.used) sumused
from #Indexes X where X.indid not in (0,1,255) group by X.TableNm) S
on I.TableNm=S.TableNm
where I.indid in (0,1)
insert REPORTS.dbo.tblIndexLogs
select getdate() as [Date],@DbNm as DbNm, FgNm, TableNm, IndexNm, dpages, reserved, used
from #Indexes
delete from #indexes
-- End log Tables and Indexes sizes
fetch c1 into @DbNm
end
deallocate c1
drop table #Files
drop table #Indexes
go
-- END OF SCRIPT
