Saturday, May 11, 2013

How to get database sizes

There are several ways to get database sizes in a server. Following three system tables has the information to get database sizes.

sys.sysfiles

sys.database_files

sys.dm_db_file_space_usage

You also can use following system stored procedure.

exec sp_spaceused

However the easiest way is to use SP_HELPDB system stored procedure. Below script used that SP to get the database sizes in a server.

create table #spdbdesc

(
dbname sysname,
dbsize nvarchar(13) null,
owner sysname null,
dbid smallint primary key,
created nvarchar(11),
dbdesc nvarchar(600) null,
cmptlevel tinyint
)


INSERT INTO #spdbdesc
exec SP_HELPDB

SELECT *
FROM #spdbdesc
 
 
 

Transitioning of SQLOS to SQLPAL

​ The SQLOS​ Prior to SQL Server 2005, SQL Server runs as a user mode process like any other Windows application and it depends on...