SQL High Availability and Disaster Recovery Using AlwaysOn

Database Mirroring in SQL Server 2008 R2 - Part 1

Microsoft SQL Server 2012 - Log Shipping Explained

Find Database Space Details

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

Reference taken from citagus