Get Database Size Stats

Please see below query, which will help you to get stats for Capacity planning such as

Database Name - Name of Database
Creation Date - When database was created
Space Allocated - How much space is currently allocated
Growth Still Available - How much space is available in Database to grow
Space Used - How much is currently been used
Data Size - Size of data file
LogSize - Size of Log file
and so on

Query used to get above stats, you can use below SQL Query

/********************************************************************************

       NAME: GetDatabaseSizeStats

       PURPOSE: Displays all known database sizes and space allocations.

********************************************************************************/

DECLARE @sysdb BIT -- 1 for include sysdatabases ,0 for Not include sysdatabases

DECLARE @order int -- set the order of columns

DECLARE @orderby bit -- 0 for asc, 1 for desc

set nocount on

SET @sysdb = 0

SET @order = 1

SET @orderby = 1

declare @id int

        ,@type character(2)

        ,@pages bigint

        ,@dbname sysname

        ,@datasize bigint

        ,@logsize bigint

        ,@reservedpages bigint

        ,@sqlstring varchar(2000)

create table #spacetemp (

        id int identity(1,1)

       ,DBname varchar(500)

       ,DBcreationDate datetime

       ,DBsize decimal(10,2)

       ,Avispace decimal(10,2)

       ,Datasize decimal(10,2)

       ,Logsize decimal(10,2)

       ,DBStatusid bigint

       ,DBStatus varchar(200)

       )

create table #stemp (

        sid int identity(1,1)

       ,mdfpath varchar(200)

       ,ldfpath varchar(200)

       ,ds bigint

       ,dl bigint

       )

insert into #spacetemp (DBname,DBcreationDate,DBStatusid,DBStatus)

       select [name],crdate, status

       ,case

              when status=1 then 'autoclose'

              when status=4 then 'select into/bulkcopy'

              when status=8 then 'trunc'

              when status=16 then 'torn page detection'

              when status=32 then 'loading'

              when status=64 then 'pre recovery'

              when status=128 then 'recovering'

              when status=256 then 'not recovered'

              when status=512 then 'offline'

              when status=1024 then 'read only'

              when status=2048 then 'dbo use only'

              when status=4096 then 'single user'

              when status=32768 then 'emergency mode'

              when status=4194304 then 'autoshrink'

              when status=1073741824 then 'cleanly shutdown'

       end

       from master..sysdatabases

       where status!=512

         and dbid > case    when @sysdb=1 then 0 else 4 end

Declare @sDBname varchar(500)

Declare @sDBcreationDate datetime

Declare @counter int

Declare @Maxid int

set @counter = 1

select @maxid = max(id) from #spacetemp

while (@counter<=@maxid)

       begin

       set @id=0

       set @type =''

       set @pages = 0

       set @datasize = 0

       set @logsize = 0

       set @reservedpages = 0

       set @sqlstring = ''

       select @dbname = DBname from #spacetemp where id =@counter

       IF @type = 'SQ'

              SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue

       set @sqlstring= 'insert into #stemp (ds,dl)

              select sum(convert(bigint,case when status & 64 = 0 then size else 0 end))

               ,sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from ['+@dbname+']..sysfiles'

       exec (@sqlstring)

       select @datasize=ds,@logsize=dl from #stemp where sid=@counter

       update #spacetemp set

               DBsize = ltrim(str((convert (dec (15,2),@datasize) + convert (dec (15,2),@logsize))

               * 8192 / 1048576,15,2))

              ,Avispace = ltrim(str((case when @datasize >= @reservedpages then

               (convert (dec (15,2),@datasize) - convert (dec (15,2),@reservedpages))

               * 8192 / 1048576 else 0 end),15,2))

              ,Datasize = ltrim(str((convert (dec (15,2),@datasize)) * 8192 / 1048576,15,2))

              ,Logsize = ltrim(str((convert (dec (15,2),@logsize)) * 8192 / 1048576,15,2))

        where [DBNAME]=@DBNAME

       set @counter = @counter+1

       end

set @sqlstring='

select

[DBname] as ''Database Name''

,[dbcreationdate] as ''Creation Date''

,convert(varchar(200),[dbsize])+'' MB'' as ''Space Allocated''

,convert(varchar(200),[avispace])+'' MB'' as ''Growth Still Avail''

,convert(varchar(200),[dbsize]-[avispace])+'' MB'' as ''Space Used''

,convert(varchar(200),[datasize])+'' MB'' as ''Data Size''

,convert(varchar(200),[logsize])+'' MB'' as ''Log Size''

,[DBstatus] as ''Database Status''

,DBStatusid

from #spacetemp order by '+case @order

when 1 then 'DBname'

when 2 then 'dbcreationdate'

when 3 then 'dbsize'

--when 4 then 'avispace'

--when 5 then 'dbsize+avispace'

else 'DBname' end+' '+case @orderby when 1 then 'asc' when 0 then 'desc'end +''

exec (@sqlstring)

drop table #spacetemp

drop table #stemp