Finding Space Used,Space left on Data and Log files

The data files and log files in SQL Server grow as specified by the growth property of the files.The growth property is specified either in % of existing size or in terms of MB. After a file grows, the newly allocated space is used for the transactions that happen in the database. One of the responsiblities of the DBA can be to keep track of how much of space is used and how much is free
with in the data and the log files. Let us run thro the options we have in SQL Server 2000 and SQL Server 2005.

Option 1 : Perfmon counter

A popular option can be to use perfmon counters, but the drawback is perfmon counters provide only the data file size, and not Data file used size, Data file space left. However, they do provide the space left and space used details for log files.


SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%:Databases%'
AND counter_name LIKE '%File%'


For SQL 2000 use sysperfinfo

Option 2 : DBCC Showfilestats()

DBCC Showfilestats takes the file id as parameter and provides used space and free space in terms of extents.

Example:

DBCC showfilestats ( 1 )




But, DBCC Showfilestats doesnt provide the info for log files and does it only for data files. So, to help our cause it should be used in combination with perfmon counters.Another word of caution about DBCC Showfilestats(). It is 'undocumneted'.
So, I still prefer a alternative way for checking these details.

Option 3: Fileproperty/sysfiles
The third option which perhaps meets all our requirements is the one using sysfiles system table and fileproperty function. Sysfiles provides the size of data and log files in the database. Fileproperty takes filename( from sysfiles table) and the string 'spaceused' as parameter and provides the actually used by the file.

Example:

SELECT filename,
       name,
       size,
       Fileproperty(name,'SpaceUsed') AS spaceused
FROM   sysfiles


The script provided below can be used against any database SQL Server 2000/2005/2008 and extracts the space used and spaceleft information for all the databases in the server.


DECLARE  @rowcnt INT
DECLARE  @iterator INT
DECLARE  @dbname VARCHAR(200)
DECLARE  @exec_sql VARCHAR(500)
SET @rowcnt = 0
SET @iterator = 1
CREATE TABLE #db_file_info (
  [Database_Name]    SYSNAME    NOT NULL,
  [File_ID]          SMALLINT    NOT NULL,
  [File_Type]        VARCHAR(10)    NOT NULL,
  [File_Name]        SYSNAME    NOT NULL,
  [File_Path]        VARCHAR(500)    NOT NULL,
  [File_Size_in_MB]  INT    NOT NULL,
  [Space_Used_in_MB] INT    NOT NULL,
  [Space_Left_in_MB] INT    NOT NULL)
CREATE TABLE #db (
  dbid INT,
  name VARCHAR(200))
INSERT INTO #db
SELECT dbid,
       name
FROM   MASTER.dbo.sysdatabases
SET @rowcnt = @@ROWCOUNT
WHILE @iterator <= @rowcnt
  BEGIN
    SELECT @dbname = name
    FROM   #db
    WHERE  dbid = @iterator
    
    SET @exec_sql = ' USE ' + @dbname + '; Insert into #DB_FILE_INFO
Select db_name(),fileid,case when groupid = 0 then ''log file'' else ''data file'' end,
name,filename,

[file_size] = 
convert(int,round((sysfiles.size*1.000)/128.000,0)),
[space_used] =
convert(int,round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,0)),
[space_left] =
convert(int,round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,0))
from
dbo.sysfiles;
'
    
    EXEC( @exec_sql)
    
    SET @iterator = @iterator + 1
  END
SELECT *
FROM   #db_file_info
DROP TABLE #db
DROP TABLE #db_file_info


Resultset is provided below: