1.Excellent Script by Tri Effendi SS::(for Data file and Log file sizes information separately)

USE [testdb]
GO
SELECT
[TYPE] = A.TYPE_DESC
,[FILE_Name] = A.name
,[FILEGROUP_NAME] = fg.name
,[File_Location] = A.PHYSICAL_NAME
,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 – ((SIZE/128.0) – CAST(FILEPROPERTY(A.NAME, ‘SPACEUSED’) AS INT)/128.0))
,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 – CAST(FILEPROPERTY(A.NAME, ‘SPACEUSED’) AS INT)/128.0)
,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 – CAST(FILEPROPERTY(A.NAME, ‘SPACEUSED’) AS INT)/128.0)/(A.SIZE/128.0))*100)
,[AutoGrow] = ‘By ‘ + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ‘ MB -‘
WHEN 1 THEN CAST(growth AS VARCHAR(10)) + ‘% -‘ ELSE ” END
+ CASE max_size WHEN 0 THEN ‘DISABLED’ WHEN -1 THEN ‘ Unrestricted’
ELSE ‘ Restricted to ‘ + CAST(max_size/(128*1024) AS VARCHAR(10)) + ‘ GB’ END
+ CASE is_percent_growth WHEN 1 THEN ‘ [autogrowth by percent, BAD setting!]’ ELSE ” END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME;

Ref: http://dba.stackexchange.com/questions/7917/how-to-determine-used-free-space-within-sql-database-files

 

2. Right click on database ==> Reports ==> Standards Reports ==> Disk Usage

3. Below script gives data file initial size as total file size.

Use testdb
Go
select DBName,name,[filename],
size as ‘Size(MB)’,
usedspace as ‘UsedSpace(MB)’,
(size – usedspace) as ‘AvailableFreeSpace(MB)’
from
(
SELECT
db_name(s.database_id) as DBName,
s.name AS [Name],
s.physical_name AS [FileName],
(s.size * CONVERT(float,8))/1024 AS [Size],
(CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, ‘SpaceUsed’) AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
) DBFileSizeInfo

Ref:  http://sqldbpool.com/

Different ways to determine free space for SQL Server databases and database files

1.To find total size for the database and current size of the data files
Use mydb
Go
sp_helpdb mydb

2.free space there is in the database, but this shows us total free space including the transaction log free space
Use mydb
Go
sp_spaceused

3.

Select *, fileproperty(name, ‘SpaceUsed’) as Used
From dbo.sysfiles

Advertisements