Category Archives: SQL Server free space in database files ?

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
%d bloggers like this: