SQL Server Performance

Database Space Allocation Filegroup Wise

Discussion in 'Contribute Your SQL Server Scripts' started by chetanjain04, Jun 24, 2006.

  1. chetanjain04 Member

    /*Version 1.0<br />Created by Chetan Jain<br />*/<pre id="code"><font face="courier" size="2" id="code"><br />CREATE PROCEDURE SP_DB_SPACE_FILEGROUP_WISE(@database varchar(100)=null, @threshold integer=null)<br />as <br />set nocount on <br />if (select object_id('tempdb..#FinalResults') ) is not null<br />drop table #FinalResults<br />create table #FinalResults(<br />ServerName sysname Default(@@servername),<br />FileType varchar(4) NOT NULL, <br />[Name] sysname NOT NULL, <br />Filegroup1 varchar(100) not null,<br />Total numeric(9,2) NOT NULL, <br />Used numeric(9,2) NOT NULL, <br />[Free] numeric(9,2) NOT NULL, <br />PctFreeSpc numeric(9,2) , <br />dbname sysname NULL ,<br />RunDate Datetime Default(Getdate()) ) <br /><br /><br />create table #DataFiles( <br />Fileid int NOT NULL, <br />[FileGroup] varchar(100) NOT NULL, <br />TotalExtents int NOT NULL, <br />UsedExtents int NOT NULL, <br />[Name] sysname NOT NULL, <br />[FileName] varchar(300) NOT NULL<br /><br />) <br />create table #LogFiles(<br />dbname sysname NOT NULL, <br />LogSize numeric(15,7) NOT NULL, <br />LogUsed numeric(9,5) NOT NULL, <br />Status int NOT NULL<br /><br />) <br />BEGIN <br />declare @StrSql varchar(500) <br />declare @dbname varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> <br /><br />/* Get data file(s) size */ <br />if @database is not null<br />begin<br />declare DataFileCur cursor local fast_forward <br />for <br />select name <br />from master..sysdatabases where name = @database<br />end<br />else<br />begin <br />declare DataFileCur cursor local fast_forward <br />for <br />select name <br />from master..sysdatabases <br /><br />end<br />open DataFileCur <br />fetch next from DataFileCur into @dbname <br />while @@fetch_status=0 <br />begin <br />set @StrSql = 'use ' + @dbname + ' DBCC showfilestats' <br />insert #DataFiles <br />exec(@StrSql) <br />set @strsql = 'use ' + @dbname + ' update #DataFiles set #datafiles.Filegroup = sysfilegroups.groupname from #datafiles , sysfilegroups where #datafiles.Filegroup = cast(sysfilegroups.groupid as varchar(100))'<br />exec(@strSql)<br /><br />insert #FinalResults(FileType,[Name],Filegroup1,Total,Used,[Free],PctFreeSpc,dbname) <br />select 'Data', <br />left(right([FileName],charindex('',reverse([FileName]))-1), charindex('.',right([FileName], charindex('',reverse([FileName]))-1))-1), <br />Filegroup,<br />CAST(((TotalExtents*64)/1024.00) as numeric(9,2)), <br />CAST(((UsedExtents*64)/1024.00) as numeric(9,2)), <br />(CAST(((TotalExtents*64)/1024.00) as numeric(9,2)) -CAST(((UsedExtents*64)/1024.00) as numeric(9,2))) , <br />convert(decimal(15,2),100.0 * round( totalextents*64.0/1024.0 - usedextents*64.0/1024.0 ,0) /(totalextents*64.0/1024.0)) ,<br />@dbname <br />from #DataFiles<br />delete #DataFiles <br />fetch next from DataFileCur into @dbname <br />end <br />close DataFileCur <br />deallocate DataFileCur <br /><br />/* Get log file(s) size */ <br />insert #LogFiles exec('dbcc sqlperf(logspace)') <br /><br /><br />declare @LogSql Varchar(8000)<br />declare @dbname1 varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> ,@dbid1 int<br /><br />if @database is not null<br />begin<br />declare LogFileCur cursor local fast_forward <br />for <br />select name,dbid<br />from master..sysdatabases where name = @database<br />end<br />else<br />begin<br />declare LogFileCur cursor local fast_forward <br />for <br />select name,dbid<br />from master..sysdatabases<br />end<br />open LogFileCur <br />fetch next from LogFileCur into @dbname1,@dbid1<br />while @@fetch_status=0 <br />begin <br />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 , <br />FILEPROPERTY(s.name,''spaceused'')/8.00 /16.00 As LogUsedSpace,(s.Size/128. - FILEPROPERTY(s.name,''spaceused'')/8.00 /16.00) , <br />convert(decimal(15,2),100.0 * (s.Size/128. - FILEPROPERTY(s.name,''spaceused'')/8.00 /16.00) / (s.Size/128. ) ),'''+ @dbname1 +''' <br />from #LogFiles l , master.dbo.sysaltfiles f ,' + @dbname1+'.dbo.sysfiles s <br />where f.dbid='+ convert(Varchar(2), @dbid1) + 'and (s.status & 0x40) &lt;&gt; 0 and s.fileid=f.fileid and l.dbname ='''+ @dbname1+''''<br />Exec(@LogSql)<br />fetch next from LogFileCur into @dbname1 ,@dbid1<br />end<br /><br />if exists<br />(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<br />from #FinalResults <br />group by ServerName,dbname,Filegroup1<br />having (sum(total)-sum(used))/sum(total)*100 &lt;= isnull(@threshold,10)<br />--order by ServerName,dbname,Filegroup1 <br />)<br /><br />select --ltrim(rtrim(ServerName)),<br />'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 ) <br />as varchar(100) )--,sum(total) as Total ,sum(used) As Used,sum(free) as free , (sum(total)-sum(used))/sum(total)*100 as FreePct<br />from #FinalResults <br />group by ServerName,dbname,Filegroup1<br />having (sum(total)-sum(used))/sum(total)*100 &lt;= isnull(@threshold,10)<br />order by ServerName,dbname,Filegroup1<br /><br />drop table #DataFiles <br />drop table #LogFiles <br />drop table #FinalResults<br />return <br />end <br /></font id="code"></pre id="code">
  2. satya Moderator

    Indeed a good script to get such information, thanks.

Share This Page