SQL Server script to find disk bottlenecks SQL Server Tips by Burleson Consulting

*************************************************
-- up_bn_storage_bnecks
*************************************************
If the intent is finding out if autogrowth is enabled for any of 
the databases or logs, or if any database or log is nearing its 
maximum file size limit, the up_bn_storage_bnecks procedure can 
be used. It gives a count of such issues along with detail on 
which database or logs have a problem on SQL Server can be used. 


IF OBJECT_ID('dbo.up_bn_storage_bnecks') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.up_bn_storage_bnecks
IF OBJECT_ID('dbo.up_bn_storage_bnecks') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.up_bn_storage_bnecks >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.up_bn_storage_bnecks >>>'
END
go

CREATE PROCEDURE up_bn_storage_bnecks
@maxdb_pct tinyint,
@maxlog_pct tinyint
AS
BEGIN


set nocount on
DECLARE @databasename sysname,
@db_growth tinyint,
@log_growth tinyint,
@db_shrink tinyint,
@db_maxsize tinyint,
@log_maxsize tinyint,
@db_log_same_dsk int,
@dummy_cntr int,
@maxdb varchar(10),
@maxlog varchar(10),
@version varchar(4)
 
create table #db_necks
(database_name sysname NULL,
 db_growth char(3) NULL,
 log_growth char(3) NULL,
 db_shrink char(3) NULL,
 db_maxsize char(3) NULL,
 log_maxsize char(3) NULL)
 
create table #temptab
(c1 tinyint)
/*
*******************************************
* Get server version and set cursor up for
* databases (only non-suspect).
*******************************************
*/
select 
@maxdb = convert(varchar(10), @maxdb_pct)
select 
@maxlog = convert(varchar(10), @maxlog_pct)

select 
@version = substring(@@version,23,4)
if @version = '7.00'
declare databasecursor cursor for
select 
name
from 
master..sysdatabases
where 
databaseproperty(name,N'IsShutdown') <> 1 and
databaseproperty(name,N'IsInRecovery') <> 1 and
databaseproperty(name,N'IsNotRecovered') <> 1 and 
databaseproperty(name,N'IsOffline') <> 1 and
databaseproperty(name,N'IsSuspect') <> 1 and 
has_dbaccess(name) = 1 and
name not in ('pubs','Northwind','model')
else
exec ('declare databasecursor 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
and name not in (''pubs'',''Northwind'',''model'')')
open 
databasecursor
 
fetch next 
from 
databasecursor into @databasename
 
while (@@fetch_status <> -1)
begin
if (@@fetch_status = -2)
begin
fetch next 
from 
databasecursor into @databasename
continue
end 
 
   begin
 
/* add to count of no growth databases */
insert into #temptab EXEC ('use [' + @databasename + '] 
select 
count(*) - ISNULL((select count(*) 
from sysfiles  
where growth = 0 and 
groupid <> 0),0)
from 
sysfiles 
where 
groupid <> 0')
 
select @db_growth = (select c1 from #temptab)
 
delete from #temptab
 
/* add to count of no growth logs */
insert into #temptab EXEC ('use [' + @databasename +'] 
select 
count(*) - ISNULL((select count(*) 
                 from sysfiles
where growth = 0 and 
(status&0x40)=0x40),0) 
from 
sysfiles 
where 
(status&0x40)=0x40')
 
        select @log_growth = (select c1 from #temptab)
 
delete from #temptab
 
/* add to count of no shrink databases */
if @version = '7.00'
begin
SELECT @db_shrink =
     convert(int,DATABASEPROPERTY(@databasename, 'IsAutoShrink'))
select @db_shrink = 
(select case @db_shrink when 1 then 0 when 0 then 1 end)
end
else
begin
       insert into #temptab 
EXEC ('use [' + @databasename +'] 
select convert(int,DATABASEPROPERTYEX(''' +
@databasename + ''', ''IsAutoShrink''))')
 
select @db_shrink = 
                  (select c1 from #temptab)
select @db_shrink = 
(select case @db_shrink when 1 then 0 when 0 then 1 end)
end
 
delete from #temptab
 
/* add to count of database files nearing maxsize limit */
insert into #temptab EXEC ('use [' + @databasename +'] 
select 
count(*) 
from sysfiles 
where growth > 0 and 
maxsize <> -1 and 
        status&0x40<>0x40 and 
(100 * convert(decimal(28,2),size)/maxsize ) > ' + @maxdb)
 
select @db_maxsize = (select c1 from #temptab)
 
delete from #temptab
 
/* add to count of log files nearing maxsize limit */
insert into #temptab EXEC ('use [' + @databasename +'] 
select 
count(*) 
from 
sysfiles 
where 
growth > 0 and 
maxsize <> -1 and 
status&0x40=0x40 and 
(100 * convert(decimal(28,2),size)/maxsize ) > ' + @maxlog)
 
select @log_maxsize = (select isnull(c1,0) from #temptab)
 
delete 
from #temptab
 
END
/* insert bottleneck information */
 
insert 
into #db_necks
(database_name,
db_growth,
log_growth,
db_shrink,
db_maxsize,
log_maxsize)
values
(@databasename,
case @db_growth
when 0 then 'No'
else 'Yes'
end,
 case @log_growth
when 0 then 'No'
else 'Yes'
end,
case @db_shrink
when 0 then 'Yes'
else 'No'
end,
case @db_maxsize
when 0 then 'No'
else 'Yes'
end,
case @log_maxsize
when 0 then 'No'
else 'Yes'
end)
 
fetch next 
from 
databasecursor into @databasename
 
END
/* select query for grid */
select 
database_name,
can_db_grow = db_growth,
can_log_grow = log_growth,
can_db_shrink = db_shrink,
db_near_max_size = db_maxsize,
log_near_max_size = log_maxsize
from 
#db_necks
order by 
1
 
select 
total_db_growth_problems = 
(select count(*) from #db_necks where db_growth = 'No'),
total_log_growth_problems = 
(select count(*) from #db_necks where log_growth = 'No'),
total_db_shrink_problems = 
(select count(*) from #db_necks where db_shrink = 'Yes'),
total_db_maxsize_problems = 
(select count(*) from #db_necks where db_maxsize = 'Yes'),
 total_log_maxsize_problems = 
(select count(*) from #db_necks where log_maxsize = 'Yes')

deallocate databasecursor
drop table #db_necks
drop table #temptab
 
RETURN(0)
END
go
IF OBJECT_ID('dbo.up_bn_storage_bnecks') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.up_bn_storage_bnecks >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.up_bn_storage_bnecks >>>'
go
GRANT EXECUTE ON dbo.up_bn_storage_bnecks TO public
go