Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

 
 

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')


 

 

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter