Search This Blog

SQL Server Find the Size of Database File – Find the Size of Log File



-- create a temporary table to insert the above mentioned output against each databases.

create table #db_files(
db_files varchar(300),
file_loc varchar(300),
filesizeMB decimal(9,2),
spaceUsedMB decimal(9,2),
FreespaceMB decimal(9,2))

declare @strSQL nvarchar(2000)
DECLARE @dbName varchar(MAX)
DECLARE @getDBname CURSOR

SET @getDBname = CURSOR FOR
select name from sys.databases

OPEN @getDBname
FETCH NEXT
FROM @getDBname INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @dbName

select @strSQL = 
    '
        use ' + @dbname + '
        INSERT INTO #db_files
        select
      name
    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
    from dbo.sysfiles a
    '
    exec sp_executesql @strSQL

FETCH NEXT
FROM @getDBname INTO @dbName

END
CLOSE @getDBname
DEALLOCATE @getDBname
GO

select * from #db_files
-- drop the temporary table 
drop table #db_files
- See more at: http://learnmysql.blogspot.com.au/2012/06/simple-script-to-find-free-space-in-sql.html#sthash.rEpZ41a1.dpuf

Output:

 


-----------------------------Data file size---------------------------- 
if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%') 
drop table #dbsize 
create table #dbsize 
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0)) 
go 
insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB) 
exec sp_msforeachdb 
'use [?]; 
  select DB_NAME() AS DbName, 
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,  
    CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),  
sum(size)/128.0 AS File_Size_MB, 
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB, 
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB  
from sys.database_files  where type=0 group by type'  
go   
-------------------log size-------------------------------------- 
  if exists (select * from tempdb.sys.all_objects where name like '#logsize%') 
drop table #logsize 
create table #logsize 
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0)) 
go 
  
insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB) 
exec sp_msforeachdb 
'use [?]; 
  select DB_NAME() AS DbName, 
sum(size)/128.0 AS Log_File_Size_MB, 
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB, 
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB  
from sys.database_files  where type=1 group by type' 
  
go 
--------------------------------database free size 
  if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%') 
drop table #dbfreesize 
create table #dbfreesize 
(name sysname, 
database_size varchar(50), 
Freespace varchar(50)default (0.00)) 
  
insert into #dbfreesize(name,database_size,Freespace) 
exec sp_msforeachdb 
'use [?];SELECT database_name = db_name() 
    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'') 
    ,''unallocated space'' = ltrim(str(( 
                CASE  
                    WHEN dbsize >= reservedpages 
                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576 
                    ELSE 0 
                    END 
                ), 15, 2) + '' MB'') 
FROM ( 
    SELECT dbsize = sum(convert(BIGINT, CASE  
                    WHEN type = 0 
                        THEN size 
                    ELSE 0 
                    END)) 
        ,logsize = sum(convert(BIGINT, CASE  
                    WHEN type <> 0 
                        THEN size 
                    ELSE 0 
                    END)) 
    FROM sys.database_files 
) AS files 
,( 
    SELECT reservedpages = sum(a.total_pages) 
        ,usedpages = sum(a.used_pages) 
        ,pages = sum(CASE  
                WHEN it.internal_type IN ( 
                        202 
                        ,204 
                        ,211 
                        ,212 
                        ,213 
                        ,214 
                        ,215 
                        ,216 
                        ) 
                    THEN 0 
                WHEN a.type <> 1 
                    THEN a.used_pages 
                WHEN p.index_id < 2 
                    THEN a.data_pages 
                ELSE 0 
                END) 
    FROM sys.partitions p 
    INNER JOIN sys.allocation_units a 
        ON p.partition_id = a.container_id 
    LEFT JOIN sys.internal_tables it 
        ON p.object_id = it.object_id 
) AS partitions' 
-----------------------------------  
  
if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%') 
drop table #alldbstate  
create table #alldbstate  
(dbname sysname, 
DBstatus varchar(55), 
R_model Varchar(30)) 
   
--select * from sys.master_files 
  
insert into #alldbstate (dbname,DBstatus,R_model) 
select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases 
--select * from #dbsize 
  
insert into #dbsize(Dbname,dbstatus,Recovery_Model) 
select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online' 
  
insert into #logsize(Dbname) 
select dbname from #alldbstate where DBstatus <> 'online' 
  
insert into #dbfreesize(name) 
select dbname from #alldbstate where DBstatus <> 'online' 
  
select  
  
d.Dbname,d.dbstatus,d.Recovery_Model, 
(file_size_mb + log_file_size_mb) as DBsize, 
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB, 
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace 
from #dbsize d join #logsize l  
on d.Dbname=l.Dbname join #dbfreesize fs  
on d.Dbname=fs.name 
order by Dbname 






SQL Server Find Free Space in Database Files


-----------------------------Data file free size---------------------------- 
USE AdventureWorks
GO

SELECT a.FILEID
,[FILE_SIZE_MB] = convert(DECIMAL(12, 2), round(a.size / 128.000, 2))
,[SPACE_USED_MB] = convert(DECIMAL(12, 2), round(fileproperty(a.NAME, ‘SpaceUsed’) / 128.000, 2))
,[FREE_SPACE_MB] = convert(DECIMAL(12, 2), round((a.size – fileproperty(a.NAME, ‘SpaceUsed’)) / 128.000, 2))
,NAME = a.NAME
,[FILENAME ]
FROM dbo.sysfiles a