SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Quickly Viewing Available Space


Quickly Viewing Available Space

Author
Message
Bikeman
Bikeman
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 4
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bBaliner/quicklyviewingavailablespace.asp
Leendert van Staalduinen
Leendert van Staalduinen
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 397

OK, this is valuable information.

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

Leendert.


Stephen Anderson-260031
Stephen Anderson-260031
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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


Don Schaeffer
Don Schaeffer
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 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.


Stephen Anderson-260031
Stephen Anderson-260031
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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.


Wayne Fairless
Wayne Fairless
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 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


J.T. Shyman
J.T. Shyman
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 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."

Wayne Fairless
Wayne Fairless
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 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


dougznospam-mailbox
dougznospam-mailbox
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search