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

Rate

β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜…

1 (1)

Share

Share

Rate

β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜… β˜…

1 (1)