SQLServerCentral / Discuss Content Posted by Boris Baliner / Article Discussions / Article Discussions by Author / Quickly Viewing Available Space / Latest Posts

RE: Quickly Viewing Available Space

dougznospam-mailbox — Thu, 18 Jun 2009 22:39:31 GMT

My favorite way to view available space in SQL is with a simple HTML Application I wrote. You can download it here: [url=http://www.dougzuck.com/hta]http://www.dougzuck.com/hta[/url][img]http://www.dougzuck.com/wp-content/uploads/2009/06/get_sql_db_sizes.png[/img]

RE: Quickly Viewing Available Space

Wayne Fairless — Tue, 06 Jun 2006 11:12:00 GMT

Opps. Forgot to change the DB between cursor lookups. Edited to work on case sensitive collations, too. Try this one instead....

-- Wayne Fairless, DBA

USE MASTER

IF (SELECT COUNT(*) FROM dbo.sysobjects WHERE NAME = N'proc_ShowAvailableSpace') = 1

BEGIN

DROP PROCEDURE dbo.proc_ShowAvailableSpace

END

CREATE PROCEDURE dbo.proc_ShowAvailableSpace

@DBName NVARCHAR(256) = NULL

SET NOCOUNT ON

DECLARE @TSQL NVARCHAR(512),

@RETCODE INT,

@CurrentDB VARCHAR(256)

IF @DBName IS NULL

BEGIN

DECLARE DB_CUR CURSOR

FOR

SELECT NAME FROM master.dbo.sysdatabases

ORDER BY NAME

OPEN DB_CUR

FETCH NEXT FROM DB_CUR INTO @CurrentDB

PRINT @CURRENTDB

WHILE @@FETCH_STATUS = 0

BEGIN

SET @TSQL = 'USE ' + @CurrentDB+ '; SELECT name AS [File Name (' + @CurrentDB + ')], ' +

'size/128.0 -CAST(FILEPROPERTY(name, ' +

CHAR(39) + 'SpaceUsed' + CHAR(39) +

')AS int)/128.0 AS [Available Space (MB)] ' +

'FROM [' + @CurrentDB + '].[dbo].[sysfiles]'

EXEC (@TSQL)

--print @tsql

FETCH NEXT FROM DB_CUR INTO @CurrentDB

END

CLOSE DB_CUR

DEALLOCATE DB_CUR

END

ELSE

BEGIN

PRINT 'Free space for database "' + @DBName + '":'

-- Check to see if the DB exists

IF (SELECT DB_ID(@DBName)) IS NULL

BEGIN

PRINT @DBName + 'does not exist.'

RETURN

END

ELSE

BEGIN

SET @TSQL = 'SELECT name AS [File Name], ' +

'size/128.0 -CAST(FILEPROPERTY(name, ' +

CHAR(39) + 'SpaceUsed' + CHAR(39) +

')AS int)/128.0 AS [Available Space (MB)] ' +

'FROM [' + @DBName + '].[dbo].[sysfiles]'

EXEC (@TSQL)

END

-- EXEC dbo.proc_ShowAvailableSpace 'master'

-- EXEC dbo.proc_ShowAvailableSpace

RE: Quickly Viewing Available Space

J.T. Shyman — Tue, 06 Jun 2006 09:38:00 GMT

Returns all Nulls for the space values when running connected to a SQL 2000 server

RE: Quickly Viewing Available Space

Wayne Fairless — Tue, 06 Jun 2006 09:17:00 GMT

-- Try this. It'll go through all the DBs if no name is specified, otherwise, just -- return it for the DB specified.

-- Wayne Fairless, DBA

USE MASTER

IF (SELECT COUNT(*) FROM dbo.sysobjects WHERE NAME = N'proc_ShowAvailableSpace') = 1

BEGIN

DROP PROCEDURE dbo.proc_ShowAvailableSpace

END

CREATE PROCEDURE dbo.proc_ShowAvailableSpace

@DBName NVARCHAR(256) = NULL

SET NOCOUNT ON

DECLARE @TSQL NVARCHAR(512),

@RETCODE INT,

@CurrentDB VARCHAR(256)

IF @DBName IS NULL

BEGIN

DECLARE DB_CUR CURSOR

FOR

SELECT NAME FROM master.dbo.sysdatabases

ORDER BY NAME

OPEN DB_CUR

FETCH NEXT FROM DB_CUR INTO @CurrentDB

PRINT @CURRENTDB

WHILE @@FETCH_STATUS = 0

BEGIN

SET @TSQL = 'SELECT name AS [File Name (' + @CurrentDB + ')], ' +

'size/128.0 -CAST(FILEPROPERTY(name, ' +

CHAR(39) + 'SpaceUsed' + CHAR(39) +

')AS int)/128.0 AS [Available Space (MB)] ' +

'FROM [' + @CurrentDB + '].[dbo].[SYSFILES]'

EXEC (@TSQL)

FETCH NEXT FROM DB_CUR INTO @CurrentDB

END

CLOSE DB_CUR

DEALLOCATE DB_CUR

END

ELSE

BEGIN

PRINT 'Free space for database "' + @DBName + '":'

-- Check to see if the DB exists

IF (SELECT DB_ID(@DBName)) IS NULL

BEGIN

PRINT @DBName + 'does not exist.'

RETURN

END

ELSE

BEGIN

SET @TSQL = 'SELECT name AS [File Name], ' +

'size/128.0 -CAST(FILEPROPERTY(name, ' +

CHAR(39) + 'SpaceUsed' + CHAR(39) +

')AS int)/128.0 AS [Available Space (MB)] ' +

'FROM [' + @DBName + '].[dbo].[SYSFILES]'

EXEC (@TSQL)

END

-- EXEC dbo.proc_ShowAvailableSpace 'master'

-- EXEC dbo.proc_ShowAvailableSpace

RE: Quickly Viewing Available Space

Stephen Anderson-260031 — Tue, 06 Jun 2006 08:55:00 GMT

The fileproperty function needs to be used in the context of the database. I plan to call this from an MS Access database (I will convert it to .Net later) looping through each database on the server. I will also be using this on multiple servers.

My plan is to use this to determine file growth, space issues, and to help project future disk space needs.

RE: Quickly Viewing Available Space

Don Schaeffer — Tue, 06 Jun 2006 08:44:00 GMT

This is nice, but what if I want to see available space for all databases on my server? I tried doing this by using the sp_msforeachdb proc, but for some reason I'm getting nulls for AvailableSpaceInMB. Anybody know why?

exec sp_msforeachdb 'SELECT ''?'' as DBName, name AS FileName, size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )AS int)/128.0 AS AvailableSpaceInMB FROM [?].dbo.SYSFILES'

I could always result to using a cursor, looping thru the databases but I'd rather not do that. If I can get the above statement to work, the next step would be to insert the results into a temp table so I can then see the free space for all files for all databases as the result of a single query.

RE: Quickly Viewing Available Space

Stephen Anderson-260031 — Tue, 06 Jun 2006 08:30:00 GMT

Try this and compare it to the task pad view in Enterprise Manager.

SELECT AA.name, AA.filename, -- AA.dbSize, BB.name, BB.filename, --BB.dbSize, (AA.dbSize) * CC.low / 1048576 AS DBSize, -- This is the total database size. (AA.dbSize) * CC.low / 1048576 - AA.AvailableSpaceInMB AS DBUsedSpace, AA.AvailableSpaceInMB as DBAvailableSpaceInMB, (BB.dbSize) * CC.low / 1048576 AS LogSize, (BB.dbSize) * CC.low / 1048576 - BB.AvailableSpaceInMB AS LogUsedSpace, BB.AvailableSpaceInMB as LogAvailableSpaceInMB, (AA.dbSize + BB.dbSize) * CC.low / 1048576 AS TotalSize, AA.AvailableSpaceInMB + BB.AvailableSpaceInMB as TotalAvailableSpaceInMB FROM (select name, filename, convert(dec(15),size) as dbSize, size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB from dbo.sysfiles where (status & 64 = 0)) AA CROSS JOIN

(select name, filename, convert(dec(15),size) as dbSize, size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB from dbo.sysfiles where (status & 64 <> 0)) BB CROSS JOIN

(select low from master.dbo.spt_values where number = 1 and type = 'E') CC

RE: Quickly Viewing Available Space

Leendert van Staalduinen — Tue, 06 Jun 2006 07:15:00 GMT

OK, this is valuable information.

But did I miss something after "... just run the following code:" ?

Leendert.

Quickly Viewing Available Space

Bikeman — Tue, 30 May 2006 10:07:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bBaliner/quicklyviewingavailablespace.asp