Few days back my E+1 and TL ask me about
database free space and it was bit difficult to get all database details at a
time.
I was able to get single database details
at a time, after some goggling I found 1 script on citagus and it was very helpful. The best part is this will work
for all version like 2000, 2005.2008 and R2 etc
..
use master
go
if exists (select [id] from tempdb..sysobjects where
[id] = OBJECT_ID ('tempdb..#TempForFileStats '))
DROP TABLE #TempForFileStats
if exists (select [id] from tempdb..sysobjects where
[id] = OBJECT_ID ('tempdb..#TempForDataFile'))
DROP TABLE #TempForDataFile
if exists (select [id] from tempdb..sysobjects where
[id] = OBJECT_ID ('tempdb..#TempForLogFile'))
DROP TABLE #TempForLogFile
DECLARE @DBName nvarchar(110)
DECLARE @SQLString
nvarchar (2000)
DECLARE c_db CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE status&512 = 0
SET NOCOUNT ON
CREATE TABLE #TempForFileStats(
[Server Name]
nvarchar(40) ,
[Database Name] nvarchar(110),
[File Name] nvarchar(128),
[Usage Type] varchar (6),
[Size (MB)] real
null default 0,
[Space Used (MB)] real null default 0,
[MaxSize (MB)] real null default 0,
[Next Allocation (MB)] real null default 0,
[Growth Type] varchar (12),
[File Id] smallint
null default 0,
[Group Id] smallint
null default 0,
[Physical File] nvarchar (260) ,
[Date Checked] datetime)
CREATE TABLE #TempForDataFile (
[File Id] smallint,
[Group Id] smallint,
[Total Extents] int null default 0,
[Used Extents] int null default 0,
[File Name] nvarchar(128),
[Physical File] nvarchar(260))
CREATE TABLE #TempForLogFile (
[File Id] int,
[Size (Bytes)] real null default 0,
[Start Offset] real null default 0,
[FSeqNo] int
null default 0,
[Status] int
null default 0,
[Parity] int
null default 0,
[CreateTime] varchar(55))
OPEN c_db
FETCH NEXT FROM c_db INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString
= '
SELECT
@@SERVERNAME as ''ServerName'', ' +'''' + @DBName + '''' + 'as ''Database'',
'+'f.name,
'+'CASE ' +' WHEN (64 & f.status) = 64 THEN ''Log'' ' +' ELSE ''Data'' ' +' END as ''Usage Type'',
'+'f.size*8/1024.00
as ''Size (MB)'',
'+'
NULL as ''Space Used (MB)'',
'+'CASE
cast(f.maxsize as real) '+' WHEN -1 THEN -1 '+'
WHEN 0 THEN f.size*8/1024.00 '+
' ELSE
cast(f.maxsize as real)*8/1024.00 '+'END
as ''Max Size (MB)'',
'+'
CASE '+'WHEN (1048576&f.status) =
1048576 THEN (growth/100.00)*(f.size*8/1024.00) '+'WHEN
f.growth =0 THEN 0
'+'ELSE
f.growth*8/1024.00 '+'END as ''Next Allocation (MB)'',
'+'CASE '+'WHEN
(1048576&f.status) = 1048576 THEN ''Percentage'' '+'
ELSE ''Pages'' ' +
' END as ''Usage
Type'',
'+'f.fileid,
'+'
f.groupid,
'+'filename,
'+'getdate()'+ 'FROM [' + @DBName + '].dbo.sysfiles f'
INSERT
#TempForFileStats
EXECUTE(@SQLString)
print @SQLString
SET @SQLString
= 'USE [' + @DBName + '] DBCC SHOWFILESTATS
WITH NO_INFOMSGS '
INSERT
#TempForDataFile
EXECUTE(@SQLString)
UPDATE
#TempForFileStats SET [Space Used (MB)] = s.[Used Extents]*64/1024.00
FROM
#TempForFileStats f,#TempForDataFile s
WHERE f.[File Id]
= s.[File
Id]
AND f.[Group Id]
= s.[Group
Id]
AND f.[Database Name] =
@DBName
TRUNCATE TABLE #TempForDataFile
SET @SQLString
= 'USE [' + @DBName + '] DBCC LOGINFO WITH NO_INFOMSGS '
INSERT
#TempForLogFile
EXECUTE(@SQLString)
UPDATE
#TempForFileStats SET [Space Used (MB)] = (SELECT (MIN(l.[Start Offset]) +SUM(CASE
WHEN l.Status <> 0 THEN l.[Size
(Bytes)] ELSE 0 END))/1048576.00
FROM
#TempForLogFile l
WHERE l.[File Id] = f.[File Id])
FROM
#TempForFileStats f
WHERE f.[Database Name] =
@DBName
AND f.[Usage Type]
= 'Log'
TRUNCATE TABLE #TempForLogFile
FETCH NEXT FROM c_db INTO @DBName
END
DEALLOCATE c_db
SELECT [Server
Name],[Database Name],[File
Name],[Usage Type],[Size
(MB)],
[Space Used (MB)],[Size (MB)]-[Space
Used (MB)] as [Free Space (MB)],[Physical File]
FROM
#TempForFileStats
DROP TABLE #TempForFileStats
DROP TABLE #TempForDataFile
DROP TABLE #TempForLogFile
go
