Monitoring Disk Space Usage, Database Files, and Backups - Professional SQL Server 2008 Administration with Windows PowerShell - page 464

WHERE (type = 0)

-- Since all the data files are considered in an entirety, if any of
the data files is set to autogrow,
-- then the data files are considered to be able to autogrow.
IF ((SELECT count(name) FROM sys.database_files WHERE (type = 0)
and (growth > 0)) ) > 0
SELECT @dataAutogrow = 1

ELSE

SELECT @dataAutogrow = 0

-- Get the total space used by log files.
SELECT @logSize = sum(size)*8 FROM sys.database_files WHERE type = 1

-- Since all the log files are considered in an entirety, if the max size of any of the
log files is set to Unlimited,
-- then the max size of log files is Unlimited. Otherwise, add up the max size of
all the log files.
IF (SELECT count(name) FROM sys.database_files WHERE (type

= 1) and (max_size =

-1) ) > 0
SELECT @maxLogSize = 'Unlimited'

ELSE

SELECT @maxLogSize = convert(varchar(15), sum(convert(bigint, max_size))* 8)
FROM sys.database_files
WHERE (type = 1)

-- Since all the log files are considered in an entirety, if any of the log files is
set to autogrow,
-- then the log files are considered to be able to autogrow.
IF ((SELECT count(name) FROM sys.database_files WHERE (type = 1) and
(growth > 0)) ) > 0
SELECT @logAutogrow = 1

ELSE

SELECT @logAutogrow = 0

CREATE TABLE #logspace
([Database Name] varchar(100),
[Log Size] decimal(15,2),
[Log Space Used (%)] decimal(3,1),
Status bit
)

-- Get the current size of the transaction log and the percentage of log space used
for the database
INSERT INTO #logspace EXEC('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS ')
SELECT @logused = [Log Space Used (%)] FROM #logspace WHERE [Database Name] = db_name()
DROP TABLE #logspace

-- Consolidate the information of the database
SELECT @dataSize as dataSize, (@dataSize- @reservedSize) as unallocatedData,
@maxDataSize as maxDataSize, @dataAutogrow as dataAutogrow,
@logSize as logSize, convert(bigint, @logSize * (100-@logused)/100) as
unallocatedLog, @maxLogSize as maxLogSize, @logAutogrow as logAutogrow