SQL Server script for storage filegroups SQL Server Tips by Burleson Consulting

**************************************************************
-- up_bn_storage_filegroups
**************************************************************
After checking the global storage picture of the SQL Server, 
one can then drill down to obtain more detail on filegroups, 
files and databases. The up_bn_storage_filegroups procedure 
will give some good information on the filegroup front:

IF OBJECT_ID('up_bn_storage_filegroups') IS NOT NULL
BEGIN
DROP PROCEDURE up_bn_storage_filegroups
IF OBJECT_ID('up_bn_storage_filegroups') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE up_bn_storage_filegroups >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE up_bn_storage_filegroups >>>'
END
go
CREATE PROCEDURE up_bn_storage_filegroups
AS
declare @db_name sysname,
@testamt float

set nocount on
set ARITHABORT off
set ARITHIGNORE on
set ANSI_WARNINGS off

create table #filegroup_info
(dbname sysname NULL,
groupid int NULL,
groupname sysname NULL,
total_space float NULL,
max_file_growth_size float NULL,
file_count tinyint NULL)

create table #space_info_objects
(dbname sysname NULL,
groupid int NULL,
reserved_pages_tables float NULL,
reserved_pages_indexes float NULL)

create table #logspace
(database_name sysname,
log_space decimal(15,2),
pct_used decimal(15,2) NULL,
status int)

declare db_cursor cursor for
select 
name
from 
master..sysdatabases
See code depot for full script
where 
databasepropertyex(name,'status') not in 
('SUSPECT', 'OFFLINE',
'RESTORING', 'RECOVERING') and 
has_dbaccess(name) = 1
open db_cursor

fetch 
db_cursor 
into 
@db_name

while @@fetch_status = 0
begin
insert 
into #filegroup_info
(dbname,
groupid,
         groupname,
total_space,
max_file_growth_size,
file_count)
exec 
('use [' + @db_name + '] 
select 
db_name = db_name(),
a.groupid,
  a.groupname,
sum(b.size),
max(b.growth),
count(b.fileid) 
from 
sysfilegroups a, 
sysfiles b 
where 
a.groupid =* b.groupid 
   group by 
a.groupid,a.groupname')

-- Get object space totsls
insert 
into #space_info_objects
(dbname,
groupid,
reserved_pages_tables,
reserved_pages_indexes)
exec 
('use [' + @db_name + '] 
select 
db_name = db_name(),
groupid,
table_pages = 
isnull((select 
sum(reserved) 
          from 
sysindexes a 
where 
a.indid in (0,1,255) and 
c.groupid = a.groupid),0),
index_pages = 
isnull((select 
sum(reserved) 
from 
sysindexes b 
where 
b.indid >1 and b.indid < 255 and 
c.groupid = b.groupid),0) 
from 
sysindexes c 
group by 
groupid')

fetch 
db_cursor 
into 
@db_name
end

insert into 
#logspace (database_name,log_space,pct_used,status) 
select 
db_name = a.instance_name,
log_size_mb = convert(decimal(15,2),a.cntr_value) / 1024,
log_pct_used = 100 * convert(decimal(15,2),b.cntr_value) / a.cntr_value,
status = 0
from
    master..sysperfinfo a,
master..sysperfinfo b
where 
a.object_name = 'SQLServer:Databases' and
b.object_name = 'SQLServer:Databases' and
a.counter_name = 'Log File(s) Size (KB)' and
b.counter_name = 'Log File(s) Used Size (KB)' and
a.instance_name <> '_Total' and
b.instance_name <> '_Total' and
a.instance_name = b.instance_name


select
a.dbname,
filegroupid = isnull(a.groupid,0),
file_group = case groupname
when NULL then 'LOG'
else groupname
end,
can_grow = case max_file_growth_size
when 0 then 'NO'
else 'YES'
end,
file_count,
size_in_mb = convert(decimal(17,2),((total_space * 8) / 1024)),
table_reserved_mb = case b.reserved_pages_tables
when NULL then 0
else convert(decimal(17,2),((b.reserved_pages_tables * 8) / 1024))
end -
case b.reserved_pages_indexes
         when NULL then 0
else convert(decimal(17,2),((b.reserved_pages_indexes * 8) / 1024))
end,
index_reserved_mb = case b.reserved_pages_indexes
when NULL then 0
else convert(decimal(17,2),((b.reserved_pages_indexes * 8) / 1024))
end,
/* don't subtract indexes for total free because they are 
included in total table space */
free_space_mb = case groupname
when NULL then convert(decimal(17,2),((100 - c.pct_used) /100) * 
convert(decimal(17,2),((total_space * 8) / 1024)))
else
convert(decimal(17,2),((total_space * 8) / 1024)) -
case b.reserved_pages_tables
when NULL then 0
else convert(decimal(17,2),((b.reserved_pages_tables * 8) / 1024))
end
end,
free_space_pct = case groupname
when NULL then 100 - c.pct_used
else
convert(decimal(5,2),100 * 
(convert(decimal(17,2),((total_space * 8) / 1024)) -
case b.reserved_pages_tables
when NULL then 0
else convert(decimal(17,2),((b.reserved_pages_tables * 8) / 1024))
end) / (convert(decimal(17,2),((total_space * 8) / 1024))))
end
from 
#filegroup_info a,
#space_info_objects b,
#logspace c
where 
a.dbname *= b.dbname and
a.groupid *= b.groupid and
a.dbname = c.database_name
order by 
1,2

 deallocate db_cursor
drop table #filegroup_info
drop table #space_info_objects
go
IF OBJECT_ID('up_bn_storage_filegroups') IS NOT NULL
PRINT '<<< CREATED PROCEDURE up_bn_storage_filegroups >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE up_bn_storage_filegroups >>>'
go

< up_bn_storage_filegroups (object space totals segment)
-- get object space totals
insert into #space_info_objects
(dbname,
groupid,
reserved_pages_tables,
reserved_pages_indexes)
exec ('use [' + @db_name + '] 
SELECT
DB_NAME = DB_NAME(),
c.data_space_id,
table_pages = ISNULL((
SELECT 
SUM(total_pages)
FROM
sys.allocation_units a,
sys.partitions b,
sys.tables d 
WHERE 
a.container_id = b.partition_id and
b.object_id = d.object_id and
b.index_id in (0,1,255) and
c.data_space_id = a.data_space_id), 0), 
index_pages = ISNULL((
SELECT
SUM(total_pages)
FROM 
sys.allocation_units a,
sys.partitions b,
        sys.indexes d 
WHERE
a.container_id = b.partition_id and
b.object_id = d.object_id and
(b.index_id > 1 and b.index_id < 255) and
d.name is not null and
c.data_space_id = a.data_space_id), 0)
FROM 
sys.allocation_units c 
GROUP BY 
c.data_space_id')