view free available database space

For performance reasons it is better to prevent the use of autogrowth (default) in SQL Server, because your database file is spread across the disk(s). If you create a new database a .mdf (main database file) and .ldf (logfile) is created. By using striping (spread your database over more than 2 files) performance improvement can be accomplished, especially, if these files are spread over separate physical disks. These additional .mdf files are called .ndf (secondary datebase file). Enough about space allocation. How do you know how much space you have used?

With the file property:

SELECT  D.name,
        CAST(CAST((D.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) FILESIZE_MB,
        CAST(Fileproperty(name,'SpaceUsed')/128 AS DECIMAL(15,2)) SPACEUSED_MB,
        CAST((D.SIZE * 8 / 1024.0) - (fileproperty(Name,'SpaceUsed')  / 128.0) AS DECIMAL(15,2)) SPACEFREE_MB

Or the space used overall:

Use [databasename]

SET NOCOUNT ON

declare @SpaceUsed int
declare @SpaceFree int
SELECT  D.name,
        CAST(CAST((D.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) FILESIZE_MB,
        CAST(Fileproperty(name,'SpaceUsed')/128 AS DECIMAL(15,2)) SPACEUSED_MB,
        CAST((D.SIZE * 8 / 1024.0) - (fileproperty(Name,'SpaceUsed')  / 128.0) AS DECIMAL(15,2)) SPACEFREE_MB
INTO #tblSize
FROM    sysfiles D

select @SpaceUsed = SUM(SPACEUSED_MB), @SpaceFree = SUM(SPACEFREE_MB) FROM #tblSize
print 'spaceused ' + convert(varchar,@SpaceUsed) + ' spacefree ' + convert(varchar,@spacefree)

drop table #tblSize

Warning: the script above causes aggregated results, so be careful with uneven disk space and partitions!