—————————————————-
— WORK IN PROGRESS
— xp_fixeddrives joined with sys.masterfiles view
— Looking at space, judging db size, growth and
— free space on the server.
— use sp_helpfile for SQL 2000
—————————————————-
set nocount on
declare @fixeddrives table — declare table variable
(
drive varchar(1),
MB_free int
)
insert into @fixeddrives exec xp_fixeddrives — Insert data
declare @masterfiles table — declare table variable
(
database_id varchar(4),
name varchar (75),
drive varchar(1),
physical_name varchar (150),
size int,
max_size int,
growth int
)
insert into @masterfiles — Insert data
select
database_id,
name,
drive = left(physical_name,1),
physical_name,
size,
max_size,
growth
from sys.master_files
— Display records
select
s.name as DbName,
Drive = left(m.physical_name,1),
m.physical_name,
cast(cast(m.size as float)*8/1024 as decimal(10,2)) as MB_size,
CASE
when m.max_size = -1 then -1
else cast(cast(m.max_size as float)*8/1024 as decimal(10,2))
END as MB_maxsize,
cast(cast(m.growth as float)*8/1024 as decimal(10,2)) as MB_growth,
f.MB_free,
CASE
when m.max_size = 0 then 0 — check for division by zero
when m.max_size = -1 then -1 — check for unrestricted growth
else cast(cast(m.size as float)*8/1024 as decimal(10,2))/cast(cast(m.max_size as float)*8/1024 as decimal(10,2))
END as [Percent]
from @masterfiles m
join @fixeddrives f ON m.drive = f.drive
join sys.databases s ON m.database_id = s.database_id
where 0=0
AND s.name = ‘TableName’
–AND right(m.physical_name,3)=’mdf’
–AND right(m.physical_name,3)=’ldf’
/**************************************************************************************************/
— Use as a guide for total drive size. If there are other files in the drive, this query will not
— account for those. Best to check results against the server itself.
— If the database is set up per standards, then the drives should report accurately, and
— should not contain extraneous files.
/**************************************************************************************************/
select
Drive = left(m.physical_name,1),
Drive_Size_Mb = cast(cast(sum(m.size) as float)*8/1024 as decimal(10,2)) + (f.MB_free),
cast(cast(sum(m.size) as float)*8/1024 as decimal(10,2)) AS [Total Mb used],
f.MB_free
from @masterfiles m
join @fixeddrives f ON m.drive = f.drive
where 0=0
–AND right(m.physical_name,3)=’mdf’
–AND right(m.physical_name,3)=’ldf’
group by f.MB_free, left(m.physical_name,1)
— SP and views referenced:
— xp_fixeddrives
— sp_databases
— sp_spaceused
— select * from sys.databases
— select * from sys.master_files
— Use with ALL versions:
set nocount on
go
select
d.name as DbName,
s.name as DataFileName,
cast(size/128 as float) as SizeMB,
cast(maxsize/128 as float) as MaxSizeMB,
CASE
when s.maxsize = 0 then 0 — check for division by zero
when s.maxsize = -1 then -1 — check for unrestricted growth
else cast(cast(size as float)/128 as decimal(10,2))/cast(cast(maxsize as float)/128 as decimal(10,2))
END as [Percent],
CASE
when cast(cast(size as float)/128 as decimal(10,2))/cast(cast(maxsize as float)/128 as decimal(10,2))
< .50 then ‘I am ok’ — in case predicate is removed
when cast(cast(size as float)/128 as decimal(10,2))/cast(cast(maxsize as float)/128 as decimal(10,2))
between .50 and .5599 then ‘Half Full’
when cast(cast(size as float)/128 as decimal(10,2))/cast(cast(maxsize as float)/128 as decimal(10,2))
between .56 and .6999 then ‘Getting there’
when cast(cast(size as float)/128 as decimal(10,2))/cast(cast(maxsize as float)/128 as decimal(10,2))
between .70 and .8599 then ‘Packed in Here’
when cast(cast(size as float)/128 as decimal(10,2))/cast(cast(maxsize as float)/128 as decimal(10,2))
> .85999 then ‘OMG!’
END as [Report],
s.[filename] as DataFileLocation
from master..sysaltfiles s
inner join master..sysdatabases d on s.dbid = d.dbid
