All Database Space Used and Free

>>Script Language and Platform: SQL Server
Helps DBA to find out quickly which database takes a lot of space and which file could be shrunk. Very useful when output is sorted by Drive Letter when “Out of space” occurs.

Author: Leonid Sheinkman

	/*
Author: Leonid Sheinkman
Created: 2009-01-12
Updated: 2013-02-15

This script use undocumented DBCC showfilestats command */ 

USE master GO

SET NOCOUNT ON
DECLARE @Kb float
DECLARE @PageSize float
DECLARE @SQL varchar(max)

SELECT @Kb = 1024.0
SELECT @PageSize=v.low/@Kb FROM master..spt_values v WHERE v.number=1 AND v.type='E'

IF OBJECT_ID('tempdb.dbo.#FileSize') IS NOT NULL  DROP TABLE #FileSize CREATE TABLE #FileSize (  DatabaseName sysname,  [FileName] varchar(max),  FileSize int,  FileGroupName varchar(max),  LogicalName varchar(max)
)

IF OBJECT_ID('tempdb.dbo.#FileStats') IS NOT NULL  DROP TABLE #FileStats CREATE TABLE #FileStats (  FileID int,  FileGroup int,  TotalExtents int,  UsedExtents int,  LogicalName varchar(max),  FileName varchar(max)
)

IF OBJECT_ID('tempdb.dbo.#LogSpace') IS NOT NULL  DROP TABLE #LogSpace CREATE TABLE #LogSpace (  DatabaseName sysname,  LogSize float,  SpaceUsedPercent float,  Status bit
)

INSERT #LogSpace EXEC ('DBCC sqlperf(logspace)')

DECLARE @DatabaseName sysname

DECLARE cur_Databases CURSOR FAST_FORWARD FOR  SELECT DatabaseName = [name] FROM dbo.sysdatabases WHERE [name] <> 'RVR_FSA' ORDER BY DatabaseName OPEN cur_Databases FETCH NEXT FROM cur_Databases INTO @DatabaseName WHILE @@FETCH_STATUS = 0
  BEGIN
 print @DatabaseName
 SET @SQL = '
USE [' + @DatabaseName + '];
DBCC showfilestats;
INSERT #FileSize (DatabaseName, [FileName], FileSize, FileGroupName, LogicalName) SELECT ''' +@DatabaseName + ''', filename, size, ISNULL(FILEGROUP_NAME(groupid),''LOG''), [name]  FROM dbo.sysfiles sf; '
PRINT @SQL
 INSERT #FileStats EXECUTE (@SQL)
 FETCH NEXT FROM cur_Databases INTO @DatabaseName
  END

CLOSE cur_Databases
DEALLOCATE cur_Databases


SELECT
 DatabaseName = fsi.DatabaseName,
 FileGroupName = fsi.FileGroupName,
 LogicalName = RTRIM(fsi.LogicalName),
 [FileName] = RTRIM(fsi.FileName),
 DriveLetter = LEFT(RTRIM(fsi.FileName),2),  FileSize = CAST(fsi.FileSize*@PageSize/@Kb as decimal(15,2)),  UsedSpace = CAST(ISNULL((fs.UsedExtents*@PageSize*8.0/@Kb), fsi.FileSize*@PageSize/@Kb * ls.SpaceUsedPercent/100.0) as decimal(15,2)),  FreeSpace = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0)*@PageSize/@Kb), (100.0-ls.SpaceUsedPercent)/100.0 * fsi.FileSize*@PageSize/@Kb) as decimal(15,2)),  [FreeSpace %] = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0) / fsi.FileSize * 100.0), 100-ls.SpaceUsedPercent) as decimal(15,2))  FROM #FileSize fsi  LEFT JOIN #FileStats fs  ON fs.FileName = fsi.FileName  LEFT JOIN #LogSpace ls  ON ls.DatabaseName = fsi.DatabaseName  ORDER BY 5, 8 DESC

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided “as -is”, without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose… Disclaimer Continued

Back to Database Journal Home