Monday, March 22, 2010

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:

6 comments:

Anonymous said...

Venkatesan,
Firstly, good article... Contratulations!
There is a bug in the line "SET @rowcnt = @@ROWCOUNT". The field DATABASE_ID from SYS.DATABASES has ascend number and if any database is deleted from instance won´t be a sequential number (1,2,3,4,5,8,9).
In this case we can replace line "SET @rowcnt = @@ROWCOUNT" to "SELECT @rowcnt=MAX (A.DATABASE_ID) FROM SYS.DATABASES A".
The max number loops will be correct.

Kind Regards,
Rafael
rafa4321@terra.com.br

vijayasekar said...

Thanks a lot. Script was useful, i replaced set @@rowcount option with cursor as i need output only for particular database.

Anonymous said...

wonderful! Exactly what I was looking for. Thank you!

Anonymous said...

This is great, many thanks for posting this. It's exactly what I've been looking for! :-D

Anonymous said...

Great example for me to get started with. But I eliminated the cursor and this script executes with half the processing time.

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)

Insert into #DB_FILE_INFO
EXEC sp_MSforeachdb ' use [?];
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;'


SELECT *
FROM #db_file_info

DROP TABLE #db_file_info

Nagaraj Venkatesan said...

sp_msforeachdb does make it faster. But its undcoumented and hence take extra care while using the same