Capturing database growth on a monthly
basis is one of the main activities of the database administrator. This
help in proper capacity planning and proactive measures can be taken
before landing into disk space issues . While capturing the database
growth most of the database administrator tend to use the database size
as the bench mark but I think that’s not the correct way of capturing
the database growth.
As a database administrator we need to look at the database used size instead of the database size because database size is the total of used space + free space and free space doesn’t contribute to database growth.
Below is one such script which will capture the used space of all the databases which are accessible and give you the total used space for that server. This will help you in doing capacity planning and now you know the total database growth for that server.
create table #dbusedsize ( used_mb int)
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE DATABASEPROPERTYEX(name, ‘Status’) = ‘ONLINE’
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = ‘USE [' + @DB +']‘ + CHAR(13) + ‘INSERT INTO #dbusedsize( used_mb )
select FILEPROPERTY([name], ”SpaceUsed”)/128
from sysfiles sf left outer join sysfilegroups sfg on sf.groupid=sfg.groupid
order by 1′ + CHAR(13)
Exec (@SQL)
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
select SUM(used_mb) from #dbusedsize
drop table #dbusedsize
PS : This script will give you the total used space for a particular server and now for each database.
As a database administrator we need to look at the database used size instead of the database size because database size is the total of used space + free space and free space doesn’t contribute to database growth.
Below is one such script which will capture the used space of all the databases which are accessible and give you the total used space for that server. This will help you in doing capacity planning and now you know the total database growth for that server.
create table #dbusedsize ( used_mb int)
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name]
FROM master..sysdatabases
WHERE DATABASEPROPERTYEX(name, ‘Status’) = ‘ONLINE’
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = ‘USE [' + @DB +']‘ + CHAR(13) + ‘INSERT INTO #dbusedsize( used_mb )
select FILEPROPERTY([name], ”SpaceUsed”)/128
from sysfiles sf left outer join sysfilegroups sfg on sf.groupid=sfg.groupid
order by 1′ + CHAR(13)
Exec (@SQL)
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
select SUM(used_mb) from #dbusedsize
drop table #dbusedsize
PS : This script will give you the total used space for a particular server and now for each database.
