Quickly Viewing Available Space
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bBaliner/quicklyviewingavailablespace.asp
-
OK, this is valuable information.
But did I miss something after "... just run the following code:" ?
Leendert.
-
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
-
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.
-
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.
-
-- 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
-
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."
-
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
-
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply