Database Space FilegroupWise

--SP_DB_SPACE_FILEGROUP_WISE @database = 'master',@THRESHOLD =30, @Filename= 'D:\test.txt'
/*THIS STORED PROCEDURE WILL BE USED TO MONITOR DATABASE FREE SPACE FOR EACH FILEGROUP.
PARAMETERS
@DATABASE = NAME OF THE DATABASE , NULL FOR ALL DATABASES
@THRESHOLD = THRESHOLD VALUE WITH DEFAULT OF 10
@FILENAME = NAME OF THE OUTPUT FILE
*/CREATE PROCEDURE SP_DB_SPACE_FILEGROUP_WISE(@database varchar(100)=null, @threshold integer=null,@FileName varchar(1000) )
as 
set nocount on 
if (select object_id('tempdb..#FinalResults') ) is not null
drop table #FinalResults
create table #FinalResults(
ServerName sysname Default(@@servername),
FileType varchar(4) NOT NULL, 
[Name] sysname NOT NULL, 
Filegroup1 varchar(100) not null,
Total numeric(9,2) NOT NULL, 
Used numeric(9,2) NOT NULL, 
[Free] numeric(9,2) NOT NULL, 
PctFreeSpc numeric(9,2) , 
dbname sysname NULL ,
RunDate Datetime Default(Getdate()) ) 


create table #DataFiles( 
Fileid int NOT NULL, 
[FileGroup] varchar(100) NOT NULL, 
TotalExtents int NOT NULL, 
UsedExtents int NOT NULL, 
[Name] sysname NOT NULL, 
[FileName] varchar(300) NOT NULL

) 
create table #LogFiles(
dbname sysname NOT NULL, 
LogSize numeric(15,7) NOT NULL, 
LogUsed numeric(9,5) NOT NULL, 
Status int NOT NULL

) 
BEGIN 
declare @StrSql varchar(500) 
declare @dbname varchar(128) 

/* Get data file(s) size */ 
if @database is not null
begin
declare DataFileCur cursor local fast_forward 
for 
select name 
from master..sysdatabases where name = @database
end
else
begin 
declare DataFileCur cursor local fast_forward 
for 
select name 
from master..sysdatabases 

end
open DataFileCur 
fetch next from DataFileCur into @dbname 
while @@fetch_status=0 
begin 
set @StrSql = 'use ' + @dbname + ' DBCC showfilestats' 
insert #DataFiles 
exec(@StrSql) 
set @strsql = 'use ' + @dbname + ' update #DataFiles set #datafiles.Filegroup = sysfilegroups.groupname from #datafiles , sysfilegroups where #datafiles.Filegroup = cast(sysfilegroups.groupid as varchar(100))'
exec(@strSql)

insert #FinalResults(FileType,[Name],Filegroup1,Total,Used,[Free],PctFreeSpc,dbname) 
select 'Data', 
left(right([FileName],charindex('\',reverse([FileName]))-1),  charindex('.',right([FileName],  charindex('\',reverse([FileName]))-1))-1), 
Filegroup,
CAST(((TotalExtents*64)/1024.00) as numeric(9,2)), 
CAST(((UsedExtents*64)/1024.00) as numeric(9,2)), 
(CAST(((TotalExtents*64)/1024.00) as numeric(9,2)) -CAST(((UsedExtents*64)/1024.00) as numeric(9,2))) , 
convert(decimal(15,2),100.0 * round( totalextents*64.0/1024.0 - usedextents*64.0/1024.0 ,0) /(totalextents*64.0/1024.0)) ,
@dbname 
from #DataFiles
delete #DataFiles 
fetch next from DataFileCur into @dbname 
end 
close DataFileCur 
deallocate DataFileCur 

/* Get log file(s) size */ 
insert #LogFiles exec('dbcc sqlperf(logspace)') 


declare @LogSql Varchar(8000)
declare @dbname1 varchar(128) ,@dbid1 int

if @database is not null
begin
declare LogFileCur cursor local fast_forward 
for 
select name,dbid
from master..sysdatabases where name = @database
end
else
begin
declare LogFileCur cursor local fast_forward 
for 
select name,dbid
from master..sysdatabases
end
open LogFileCur 
fetch next from LogFileCur into @dbname1,@dbid1
while @@fetch_status=0 
begin 
SET @LogSql ='USE '+ @dbname1 + CHAR(13) + ' insert #FinalResults(FileType,[Name],Filegroup1,Total,Used,[Free],PctFreeSpc,dbname) select ''Log'',s.[name],''Transaction Log'',s.Size/128. as LogSize , 
FILEPROPERTY(s.name,''spaceused'')/8.00 /16.00 As LogUsedSpace,(s.Size/128. - FILEPROPERTY(s.name,''spaceused'')/8.00 /16.00) , 
convert(decimal(15,2),100.0 * (s.Size/128. - FILEPROPERTY(s.name,''spaceused'')/8.00 /16.00) / (s.Size/128. ) ),'''+ @dbname1 +''' 
from #LogFiles l , master.dbo.sysaltfiles f ,' + @dbname1+'.dbo.sysfiles s 
where f.dbid='+ convert(Varchar(2), @dbid1) + 'and (s.status & 0x40) <> 0 and s.fileid=f.fileid and l.dbname ='''+ @dbname1+''''
Exec(@LogSql)
fetch next from LogFileCur into @dbname1 ,@dbid1
end

if exists
(select ServerName,dbname,Filegroup1 --,sum(total) as Total ,sum(used) As Used,sum(free) as free , (sum(total)-sum(used))/sum(total)*100 as FreePct
from #FinalResults 
group by ServerName,dbname,Filegroup1
having (sum(total)-sum(used))/sum(total)*100 <= isnull(@threshold,10)
--order by ServerName,dbname,Filegroup1 
)

declare @msg varchar(400)

select 'Free Space Below '+ cast(isnull(@threshold,10) as varchar(10) ) + ' percent for DB ' + ltrim(rtrim(isnull(dbname,'') )) + '-Filegroup:' + ltrim(rtrim(Filegroup1)) + ',PctFree:' + cast (  convert( decimal(5,2),  (sum(total)-sum(used)) / sum(total)*100 ) as varchar(100)  ) as msg --,sum(total) as Total ,sum(used) As Used,sum(free) as free , (sum(total)-sum(used))/sum(total)*100 as FreePct
into #MsgTbl
from #FinalResults 
group by ServerName,dbname,Filegroup1
having (sum(total)-sum(used))/sum(total)*100 <= isnull(@threshold,10)

declare cur_FGResult cursor 
for 
select * from #MsgTbl

DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Error: Scripting.FileSystemObject'

--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,
@FileName, 8, 1
IF @OLEResult <>0 PRINT 'Error: OpenTextFile'

open cur_FGResult
fetch next from cur_FGResult into @msg
while @@fetch_status = 0
begin

--Write
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @msg
IF @OLEResult <> 0 PRINT 'Error : WriteLine'
fetch next from cur_FGResult into @msg
end

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS


drop table #MsgTbl
drop table #DataFiles
drop table #LogFiles
drop table #FinalResults
return
end