How to Compute Actual Space Used In Datafiles
-
Has anyone discovered the algorithm and the appropriate system tables to use to compute the actual amount of space used/remaining within a database datafile?
I'm trying to hit out to the values displayed in the Taskpad view in Enterprise Manager and want to write a routine around those values.
Thanks
-
AFAIK, EM uses an undocumented DBCC command
DBCC SHOWFILESTATS
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
This isn't really pretty - and I had to carve it out of a cursor that I use as part of my nightly processing - but it seems to be pretty accurate for calculating db space. Also, I can't claim to have come up with this. My inspiration came from a few MS-supplied stored procedures.
I guess that's as close to a disclaimer as I can get <g>. This will more than likely get wordwrapped so I hope you can make sense out of it...
Cheers, Ken
PS Replies/comments are appreciated/welcome
DECLARE @dbname SYSNAME
SELECT @dbname = 'pubs'
EXEC('USE '+@dbname+'
DECLARE @DBSize DEC(15,0),
@LogSize DEC(15,0),
@BytesPerPage DEC(15,0),
@PagesPerMB DEC(15,0),
@FreeSize DEC(15,4),
@Growth INT,
@PrintStr VARCHAR(200)
SELECT @DBSize = SUM(CONVERT(DEC(15),size))
FROM dbo.sysfiles
WHERE (status & 64 = 0)
SELECT @Logsize = SUM(CONVERT(DEC(15),size))
FROM dbo.sysfiles
WHERE (status & 64 <> 0)
SELECT @BytesPerPage = low
FROM master.dbo.spt_values
WHERE number = 1
AND type = "E"
SELECT @PagesPerMB = 1048576 / @BytesPerPage
SELECT @FreeSize =
@DBSize -
(SELECT SUM(CONVERT(DEC(15),reserved))
FROM sysindexes
WHERE indid IN (0, 1, 255)
)
SELECT @Growth = MIN(growth)
FROM sysfiles
WHERE status & 0x40 <> 0x40
SELECT @PrintStr = " Space usage (Data + Log = Total)...: "+
LTRIM(STR((@DBSize) / @PagesPerMB,15,2))+" + "+
LTRIM(STR((@LogSize) / @PagesPerMB,15,2))+" = "+
LTRIM(STR((@DBSize+@LogSize) / @PagesPerMB,15,2))+" Mb"
PRINT @PrintStr
SELECT @PrintStr = " Free Data Space....................: "+
LTRIM(STR((@dbsize -
(SELECT SUM(CONVERT(DEC(15),reserved))
FROM sysindexes
WHERE indid IN (0, 1, 255)
)) / @PagesPerMB,15,2)+ " Mb")
+ " (" +
LTRIM(STR(((@FreeSize / @PagesPerMB)/(@DBSize / @PagesPerMB))*100,15,2))
+ "%)"+
CASE
WHEN (@FreeSize/@DBSize) < .05 AND @Growth = 0 THEN " ***** WARNING *****"
ELSE " "
END
PRINT @PrintStr
USE master
')
-
this is the best i was able to come up with in my own searches for that answer..... this does a pretty good job of mirroring the information showed in the taskpad (b/c it uses the dbcc showfilestats cmd like frank mentioned). it does _not_ use pieces of the sp_spacedused sproc, which may be more accurate, but doesn't paint a consistent picture with what shows in the taskpad.
uses some great code scraps i've found here & there -- i don't remember the sources
(so if anybody sees their own code in this, thanks!!)also, any smileys found in the stored procedures are unintentional
CREATE procedure dbo.DBStats( @dbname sysname = null, @insert bit =0, @UpdateStats bit = 1 )
as
declare @sqlstr varchar(8000), @usestr varchar(100)
select @usestr = isnull('USE '+@dbname,''), @dbname = isnull(@dbname, DB_Name())
select @sqlstr = '
' + @usestr + '
CREATE TABLE #FileDetails (
FileId int , FileGroupId int , TotalExtents int , UsedExtents int ,
[Name] sysname , [FileName] nvarchar( 200 ) ,
TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 ) ,
UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 )
)
CREATE TABLE #LogDetails (
DatabaseName nvarchar( 128 ) , FileSize float ,
[%Usage] float , Status int
)
CREATE TABLE #DBStats_Internal (
dbname sysname,
filetype varchar(10),
percent_of_potential_space_used float,
percent_of_allocated_space_used float,
percent_of_potential_space_allocated float,
potential float,
allocated float,
used float
)
IF ('+ltrim(str(@UpdateStats))+'=1)
BEGIN
DBCC UPDATEUSAGE(0)
--EXEC sp_updatestats
END
INSERT INTO #FileDetails (FileId , FileGroupId , TotalExtents , UsedExtents , [Name] , [Filename])
EXECUTE( ''dbcc showfilestats with tableresults'' )
INSERT INTO #LogDetails (DatabaseName , FileSize , [%Usage] , Status)
EXECUTE( ''dbcc sqlperf( logspace ) with tableresults'' )
INSERT INTO #DBStats_Internal
SELECT
dbname,
filetype,
percent_of_potential_space_used,
percent_of_allocated_space_used,
percent_of_potential_space_allocated,
potential,
allocated,
used
FROM
(
SELECT FILEGROUP_STATS.*,
used,
percent_of_allocated_space_used,
100 * (((used/potential))) [percent_of_potential_space_used]
FROM
(
SELECT
dbname, filetype
, sum(filesize) [allocated]
, sum(theoreticalmax) [potential]
, 100 * (((sum(filesize)/cast(sum(theoreticalmax) as float)))) [percent_of_potential_space_allocated]
FROM
(
SELECT DB_Name() dbname
, case when status & 0x40 = 0x40 then ''log'' else ''data'' end [filetype]
, cast((((size))/128.0) as decimal(15,2)) [FileSize]
, 1.0 * isnull(nullif(isnull(cast(nullif(0,growth) as float),/128.0),0), (cast(isnull(nullif(maxsize,-1),9999999*128) as float) / 128.0) ) [TheoreticalMax]
FROM SYSFILES
 
FILE_STATSGROUP BY dbname, filetype
 
FILEGROUP_STATS INNER JOIN (
-------------------------------
SELECT '''+ @dbname +''' dbname, FileType, FileSize [allocated], CurrentSize [used], Usage [percent_of_allocated_space_used] from
(
SELECT ''data'' AS FileType,
round(SUM( TotalSize ),2) AS FileSize ,
round(SUM( UsedSize ),2) AS CurrentSize ,
round(( ( SUM( UsedExtents ) * 1. ) / SUM( TotalExtents ) ) * 100,2) AS [Usage]
FROM #FileDetails
UNION ALL
SELECT ''log'',
round(FileSize,2) AS FileSize ,
round([%Usage]/100.0 * FileSize, 2) AS CurrentSize,
round([%Usage], 2) AS [Usage]
FROM #LogDetails
WHERE DatabaseName = DB_NAME()
 
FILE_USAGE--------------------------------
 
FILEGROUP_USAGE ONFILEGROUP_STATS.dbname = FILEGROUP_USAGE.dbname and
FILEGROUP_STATS.filetype=FILEGROUP_USAGE.filetype
) DB_STATS
if ('+ltrim(str(@insert))+'=0)
begin
select * from #DBStats_Internal
end else
begin
insert into #dbstats
select * from #DBStats_Internal
end
DROP TABLE #FileDetails
DROP TABLE #LogDetails
DROP TABLE #DBStats_Internal
'
--print @sqlstr
EXEC(@sqlstr)
GO
-
Perhaps tracing sp_spaceused or sp_helpdb exactadb would get you there.
smv929 -
Just another trick for you...if you turn on profiler and filter on your Windows account you can then capture anything that is done on your behalf by EM. For instance, displaying the taskpad view of a database in EM is accomplished by running some 'code' against the master and other database tables. By profiling yourself you can see what EM does under the covers.
-
We use the following syntax to log this information in a database:
insert into logdb.dbo.dbfilesize (db, fileID, filesize)
exec sp_MSforeachdb @command1 =
'select db_name(dbid), sf.fileid, sf.size
from sysdatabases sd, ?..sysfiles sf
where db_name(dbid) = ''?'''
Not really sure where this came from -- otherwise I would credit the author
-
to track DB growth I use the following. (tdb is an archive table for reporting)
Create table #tDB
(dbName varchar (255),
dataSize float,
log_size float,
space_used float,
calc_date datetime)
go
--db size
sp_MSforeachdb "INSERT INTO #tDB SELECT '?',(SUM(size)*8.0)/1024.0 ,0,0,convert(char,getdate(),101) FROM ?..sysfiles WHERE status & 0x40 <> 0x40"
go
--log size
sp_MSforeachdb "UPDATE #tDB SET log_size = (select (SUM(size)*8.0)/1024.0 FROM ?..sysfiles WHERE status & 0x40 = 0x40) where dbName = '?'"
go
--data space used
sp_MSforeachdb "UPDATE #tDB SET space_used= (select(SUM(reserved)*8.0)/1024.0 FROM ?..sysindexes WHERE indid IN(0,1,255)) where dbName='?'"
go
insert into tdb (dbName, dataSize, log_size, space_used,calc_date, pct_change)
select tmp.dbname,tmp.datasize,tmp.log_size,tmp.space_used, tmp.calc_date
,(1-( convert(float , t.datasize)
/ convert(float, tmp.datasize) ))*100 as pct_change
from tdb t
inner join #tdb tmp
on t.dbname = tmp.dbname
where t.calc_date = (select max(calc_date) from tdb)
DROP TABLE #TDB
select * from tdb
where calc_date = (select max(calc_date) from tdb)
order by pct_change desc
-
I have wrote some thing like this
/*
script 1
*/
SET QUOTED_IDENTIFIER ON
set nocount on
DECLARE
@dbsize int,
@dbmaxsize
int,
@DatabaseSize nvarchar(100),
@DatabaseName
CURSOR,
@dbname nvarchar(100),
@SQLString nvarchar(500),
@MyEmailMessage
varchar(500)
CREATE TABLE
#mysysfiles (
[int]
NOT NULL ,
[maxsize] [int]
NOT NULL ,
[name] [nchar] (128)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
SET
@DatabaseName= CURSOR FOR select name from master..sysdatabases
OPEN
@DatabaseName
FETCH NEXT FROM
@DatabaseName INTO @dbname
WHILE
@@FETCH_STATUS = 0
begin
exec
('INSERT INTO #mysysfiles select top 1 size,maxsize,name from '+@dbname+'..sysfiles ORDER BY Name ')
select
@dbsize = size from #mysysfiles
select
@dbmaxsize = maxsize from #mysysfiles
if
(@dbsize/@dbmaxsize > 75)
begin
set
@MyEmailMessage = 'The database file'+@dbname +'is below 25%'
EXEC
xp_sendmail @recipients = 'myemail',
@message = @MyEmailMessage,
@copy_recipients = 'myemail',
@subject = 'Database File needs enlargement'
end
delete from
#mysysfiles
exec
('INSERT INTO #mysysfiles select top 1 size,maxsize,name from '+@dbname+'..sysfiles ORDER BY Name desc')
select
@dbsize = size from #mysysfiles
select
@dbmaxsize = maxsize from #mysysfiles
if
(@dbsize/@dbmaxsize > 75)
begin
set
@MyEmailMessage = 'The database file'+@dbname +'is below 25%'
EXEC
xp_sendmail @recipients = 'myemail',
@message = @MyEmailMessage,
@copy_recipients = 'myemail',
@subject = 'Database File needs enlargement'
end
delete from
#mysysfiles
FETCH NEXT FROM @DatabaseName INTO @dbname
END
drop table
#mysysfiles
CLOSE
@DatabaseName
DEALLOCATE
@DatabaseName
-- but script above only good if db has only two files
--I also tried script below that suppose work even
--database has more then two files
but I get back
this message
Server: Msg 156, Level 15, State 1, Line 57
Incorrect syntax near the keyword 'END'.
--you are welcome to try to fix it and use it and tell --
me if you secseed /*
script 2
*/
SET QUOTED_IDENTIFIER ON
set nocount on
DECLARE @dbsize int,
@dbmaxsize int,
@dbfilename [nchar] (128),
@DatabaseName CURSOR,
@dbname nvarchar(100),
@SQLString nvarchar(500),
@MyEmailMessage varchar(500)
CREATE TABLE #mysysfiles (
[int] NOT NULL ,
[maxsize] [int] NOT NULL ,
[name] [nchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
SET @DatabaseName= CURSOR FOR select name from master..sysdatabases
OPEN @DatabaseName
FETCH NEXT FROM @DatabaseName INTO @dbname
WHILE @@FETCH_STATUS = 0
begin
exec ('INSERT INTO #mysysfiles select top 1 size,maxsize,name from '+@dbname+'..sysfiles ORDER BY Name ')
declare DatabaseSize CURSOR FOR select size,maxsize,name from #mysysfiles
open DatabaseSize
FETCH NEXT FROM DatabaseSize INTO @dbsize,@dbmaxsize,@dbfilename
while (@@fetch_status = 0 )
select @dbsize = size from #mysysfiles
select @dbmaxsize = maxsize from #mysysfiles
print @dbmaxsize
if (@dbsize/@dbmaxsize > 75)
begin
set @MyEmailMessage = 'The database file'+@dbname +'is below 25%'
EXEC xp_sendmail @recipients = 'my email',
@message = @MyEmailMessage,
@copy_recipients = 'my email',
@subject = 'Database File needs enlargement'
end
delete from #mysysfiles
FETCH NEXT FROM DatabaseSize INTO @dbsize,@dbmaxsize,@dbfilename
end
close DatabaseSize
DEALLOCATE DatabaseSize
FETCH NEXT FROM @DatabaseName INTO @dbname
END
CLOSE @DatabaseName
DEALLOCATE @DatabaseName
drop table #mysysfiles
--go
-
Have to add my two cents...
DECLARE @DBName nvarchar(60)
DECLARE @SQLString nvarchar (2000)
DECLARE c_db CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE status&512 = 0
CREATE TABLE #TempForFileStats([Server Name] nvarchar(40),
[Database Name] nvarchar(60),
[File Name] nvarchar(128),
[Usage Type] varchar (6),
[Size (MB)] real,
[Space Used (MB)] real,
[Space Used (%)] real,
[MaxSize (MB)] real,
[Next Allocation (MB)] real,
[Growth Type] varchar (12),
[File Id] smallint,
[Group Id] smallint,
[Physical File] nvarchar (260),
[Date Checked] datetime)
CREATE TABLE #TempForDataFile ([File Id] smallint,
[Group Id] smallint,
[Total Extents] int,
[Used Extents] int,
[File Name] nvarchar(128),
[Physical File] nvarchar(260))
CREATE TABLE #TempForLogFile ([File Id] int,
[Size (Bytes)] int,
[Start Offset] bigint,
[FSeqNo] int,
[Status] int,
[Parity] smallint,
[CreateTime] varchar(40))
OPEN c_db
FETCH NEXT FROM c_db INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'SELECT @@SERVERNAME as ''ServerName'', ' +
'''' + @DBName + '''' + ' as ''Database'', ' +
' f.name, ' +
' CASE ' +
' WHEN (64 & f.status) = 64 THEN ''Log'' ' +
' ELSE ''Data'' ' +
' END as ''Usage Type'', ' +
' f.size*8/1024.00 as ''Size (MB)'', ' +
' NULL as ''Space Used (MB)'', ' +
' NULL as ''Space Used (%)'', ' +
' CASE f.maxsize ' +
' WHEN -1 THEN NULL ' +
' WHEN 0 THEN f.size*8/1024.00 ' +
' ELSE f.maxsize*8/1024.00 ' +
' END as ''Max Size (MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8/1024.00) ' +
' WHEN f.growth =0 THEN NULL ' +
' ELSE f.growth*8/1024.00 ' +
' END as ''Next Allocation (MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN ''Percentage'' ' +
' ELSE ''Pages'' ' +
' END as ''Usage Type'', ' +
' f.fileid, ' +
' f.groupid, ' +
' filename, ' +
' getdate() ' +
' FROM [' + @DBName + '].dbo.sysfiles f'
INSERT #TempForFileStats
EXECUTE(@SQLString)
------------------------------------------------------------------------
SET @SQLString = 'USE [' + @DBName + '] DBCC SHOWFILESTATS'
INSERT #TempForDataFile
EXECUTE(@SQLString)
--
UPDATE #TempForFileStats
SET [Space Used (MB)] = s.[Used Extents]*64/1024.00,
[Space Used (%)] = (s.[Used Extents]*64/10.24) / f.[Size (MB)]
FROM #TempForFileStats f,
#TempForDataFile s
WHERE f.[File Id] = s.[File Id]
AND f.[Group Id] = s.[Group Id]
AND f.[Database Name] = @DBName
--
TRUNCATE TABLE #TempForDataFile
-------------------------------------------------------------------------
SET @SQLString = 'USE [' + @DBName + '] DBCC LOGINFO'
INSERT #TempForLogFile
EXECUTE(@SQLString)
--
UPDATE #TempForFileStats
SET [Space Used (MB)] = (SELECT (MIN(l.[Start Offset]) +
SUM(CASE
WHEN l.Status <> 0 THEN l.[Size (Bytes)]
ELSE 0
END))/1048576.00
FROM #TempForLogFile l
WHERE l.[File Id] = f.[File Id]),
[Space Used (%)] = (SELECT (MIN(l.[Start Offset]) +
SUM(CASE
WHEN l.Status <> 0 THEN l.[Size (Bytes)]
ELSE 0
END))/10485.76
FROM #TempForLogFile l
WHERE l.[File Id] = f.[File Id])/ f.[Size (MB)]
FROM #TempForFileStats f
WHERE f.[Database Name] = @DBName
AND f.[Usage Type] = 'Log'
--
TRUNCATE TABLE #TempForLogFile
-------------------------------------------------------------------------
FETCH NEXT FROM c_db INTO @DBName
END
DEALLOCATE c_db
SELECT * FROM #TempForFileStats
--where [space used (%)] > 85
------------
DROP TABLE #TempForFileStats
DROP TABLE #TempForDataFile
DROP TABLE #TempForLogFile
"Keep Your Stick On the Ice" ..Red Green -
Well, if we are only talking on how to track db growth, here are some techniques by Vyas Kondreddi.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
/*
I MADE MY WORK FOR DATABASE THAT HAS ANY NUMBER OF DATA OR LOG FILES
*/
SET QUOTED_IDENTIFIER ON
set nocount on
DECLARE @dbsize decimal,
@dbmaxsize decimal,
@DatabaseSize nvarchar(100),
@DatabaseName CURSOR,
@dbname nvarchar(100),
@SQLString nvarchar(500),
@MyEmailMessage varchar(500),
@dbfilename varchar(100)
CREATE TABLE #mysysfiles (
[int] NOT NULL ,
[maxsize] [int] NOT NULL ,
[name] [nchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
SET @DatabaseName= CURSOR FOR select name from master..sysdatabases
OPEN @DatabaseName
FETCH NEXT FROM @DatabaseName INTO @dbname
WHILE @@FETCH_STATUS = 0
begin
exec ('INSERT INTO #mysysfiles select size,maxsize,name from '+@dbname+'..sysfiles ORDER BY Name ')
FETCH NEXT FROM @DatabaseName INTO @dbname
END
CLOSE @DatabaseName
DEALLOCATE @DatabaseName
--select count((convert(decimal,size)/convert(decimal,maxsize))*100) from #mysysfiles where maxsize <>-1
--and ((convert(decimal,size)/convert(decimal,maxsize))*100)>75
declare @counter int --counter for loop
table_loop:
select @counter = count((convert(decimal,size)/convert(decimal,maxsize))*100) from #mysysfiles where maxsize <>-1
and ((convert(decimal,size)/convert(decimal,maxsize))*100)>75
--print @counter
if @counter=0
begin
drop table #mysysfiles
print ' there were no more records that match your critiria'
return
end
else
begin
select @dbfilename = [name] from #mysysfiles where maxsize <>-1
and ((convert(decimal,size)/convert(decimal,maxsize))*100)>75
print @dbfilename
/*
test email part tommorow
*/
set @MyEmailMessage = 'The database file'+@dbfilename +'is below 25%'
EXEC xp_sendmail @recipients = 'MYEMAIL',
@message = @MyEmailMessage,
@copy_recipients = 'MAEMAIL',
@subject = 'Database File needs enlargement'
----------------------------------------------
delete from #mysysfiles where [name]=@dbfilename
goto table_loop
end
select * from #mysysfiles
--select size , maxsize from dbo.sysfiles where
-
So you have a script that will retrieve this information from linked servers
and store it in local database tables?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply