How to find SQL Server free space in database files ?


Execute the following SQL Server scripts to create and test a stored procedure for database file free space check.
USE MASTER;

GO

CREATE PROC SpaceFree

           @Database SYSNAME

AS

  BEGIN

    DECLARE  @sqlCommand NVARCHAR(1024)

    

    SET @sqlCommand = ‘USE ‘ + @Database + ‘ ‘ +

    ‘SELECT DatabaseFile = name, FreeSpaceMB=size/128.0

    -CAST(FILEPROPERTY(name, ”SpaceUsed” )AS int)/128.0 ‘ +

    ‘FROM dbo.SYSFILES’

    

    PRINT @sqlCommand

 /*

 USE AdventureWorks SELECT DatabaseFile = name, FreeSpaceMB=size/128.0

    -CAST(FILEPROPERTY(name, ‘SpaceUsed’ )AS int)/128.0 FROM dbo.SYSFILES

 */

    

    EXEC sp_executesql  @sqlCommand

  END

GO
— procedure execution
EXEC SpaceFree ‘AdventureWorks’

GO

/* Results

DatabaseFile            FreeSpaceMB

AdventureWorks_Data     3.187500

AdventureWorks_Log      1.125000

*/

********************************************************************************************************

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: