T-SQL Query to Get Database Size
January 21st, 2009 by Andrew Chen
The other day I was asked to provide a list of database server and all the databases on them with the disk space each database used.
But when you use it to get database size you have to execute it within the database in question. It doesn’t take a database name as parameter. So it can be automated to run for each database using T-SQL. What I want is query that is able to list all database on the server together with the size they take up. So I wrote the following query to do that. It worked fine for me.
use master
declare @PageSize varchar(10)
select @PageSize=v.low/1024.0
from master..spt_values v
where v.number=1 and v.type='E'
select name as DatabaseName, convert(float,null) as Size
into #tem
From sysdatabases where dbid>4
declare @SQL varchar (8000)
set @SQL=''
while exists (select * from #tem where size is null)
begin
select @SQL='update #tem set size=(select round(sum(size)*'+@PageSize+'/1024,0) From '+quotename(databasename)+'.dbo.sysfiles) where databasename='''+databasename+''''
from #tem
where size is null
exec (@SQL)
end
select * from #tem order by DatabaseName
drop table #tem
The first select statement is to get how many kilobytes a data page has. SQL Server allocates disk space in the unit of data page. Currently each SQL server data page contains 8k bytes. The number of data pages allocated to each database file is recorded in the sysfiles system table. With this information on hand the script creates a temporary table #tem and update the temporary table with size information which is gathered by querying the sysfiles table.
Related Posts:
(No Ratings Yet)
Loading ...
Great help! thanks!!
Your browser does not support iframes.
nice
nice one.
Why not just use sys.master_files and join sys.database files to help with filtering? Eliminates the need for your cursor, dynamic sql, and the temp table. Same dataset, one query:
—————————
select d.name,round(sum(mf.size) * 8 /1024,0) from sys.master_files mf
inner join sys.databases d
on d.database_id = mf.database_id
where d.database_id > 4
group by d.name
order by d.name
—————————
Obviously you can add the page size evaluation easily, i just stuck in 8 for brevity.
fly safe
-lodester
Thanks It works….. Great
Absolutely true
This design is steller! You obviously know how to keep a reader amused. Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Excellent job. I really loved what you had to say, and more than that, how you presented it. Too cool!
What’s wrong with EXEC sp_databases?