| |
 |
|
Display SQL Server storage filegroups
script
SQL Server Tips by Donald Burleson |
This is one of the many SQL
Server Windows scripts to display storage filegroups from the book "High
Performance SQL Server DBA".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
where
databasepropertyex(name,'status') not in
('SUSPECT', 'OFFLINE',
'RESTORING', 'RECOVERING') and
has_dbaccess(name) = 1
See code depot for full script
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
See code depot for full script
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
Figure 5.2 is a representation of the filegroup demographic
information.
This is one of the many SQL Server
Windows scripts to display storage filegroups from the book "High
Performance SQL Server DBA".
|
|
|
and include the URL for the page.
