Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Quickly Viewing Available Space Expand / Collapse
Author
Message
Posted Tuesday, May 30, 2006 10:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, March 1, 2009 1:56 PM
Points: 125, Visits: 4
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bBaliner/quicklyviewingavailablespace.asp
Post #283641
Posted Tuesday, June 6, 2006 7:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 17, 2013 1:19 PM
Points: 29, Visits: 397

OK, this is valuable information.

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

Leendert.

Post #285209
Posted Tuesday, June 6, 2006 8:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 26, 2011 8:44 AM
Points: 11, Visits: 81

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

 

Post #285265
Posted Tuesday, June 6, 2006 8:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 26, 2007 1:17 PM
Points: 115, Visits: 4

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.

 

Post #285271
Posted Tuesday, June 6, 2006 8:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 26, 2011 8:44 AM
Points: 11, Visits: 81

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.

Post #285277
Posted Tuesday, June 6, 2006 9:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 9:20 AM
Points: 96, Visits: 2

-- 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

GO

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

BEGIN

     DROP PROCEDURE dbo.proc_ShowAvailableSpace

END

GO

CREATE PROCEDURE dbo.proc_ShowAvailableSpace

@DBName NVARCHAR(256) = NULL

AS

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

END

 

-- EXEC dbo.proc_ShowAvailableSpace 'master'

-- EXEC dbo.proc_ShowAvailableSpace

 

Post #285297
Posted Tuesday, June 6, 2006 9:38 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 25, 2011 9:16 AM
Points: 316, Visits: 4
Returns all Nulls for the space values when running connected to a SQL 2000 server


-- J.T.

"I may not always know what I'm talking about, and you may not either."

Post #285310
Posted Tuesday, June 6, 2006 11:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 9:20 AM
Points: 96, Visits: 2

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

GO

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

BEGIN

DROP PROCEDURE dbo.proc_ShowAvailableSpace

END

GO

CREATE PROCEDURE dbo.proc_ShowAvailableSpace

@DBName NVARCHAR(256) = NULL

AS

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

END

-- EXEC dbo.proc_ShowAvailableSpace 'master'

-- EXEC dbo.proc_ShowAvailableSpace

Post #285369
Posted Thursday, June 18, 2009 10:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 22, 2010 12:06 PM
Points: 14, Visits: 110
My favorite way to view available space in SQL is with a simple HTML Application I wrote. You can download it here: http://www.dougzuck.com/hta




Post #738044
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse