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
 
 
 

9 comments:

  1. Sp_spaceused also includes the log space. Why the log size is important is because it can be even larger than the database itself.
    By Arthur Zubarev

    ReplyDelete
  2. Hi Arthur,
    database size includes both data file size plus log file. even SP_HELPDB gives the same result as SP_SPACEUSED.

    ReplyDelete
  3. Jack Vamvas
    I usually query the sys.master_files . It is also easy to create a report with this view
    http://www.sqlserver-dba.com/2012/09/sql-server-find-the-database-file-size-and-log-file-size.html

    ReplyDelete
  4. My partner and i actually enjoy this post and the internet site all in all! Your piece of writing is really plainly composed as well as simply understandable. Your current Blog design is awesome as well! Would be awesome to know where I are able obtain it. Please maintain up the very good job. We all require far more such website owners like you on the net and much fewer spammers. Fantastic mate!
    Hadoop online training

    ReplyDelete
  5. Thanks for sharing with us that awesome article you have amazing blog.....
    Hadoop Online Training

    ReplyDelete

  6. Hi, I believe your website could be having browser compatibility issues. When I take a look at your website in Safari, it looks fine however when opening in Internet Explorer, it’s got some overlapping issues. I just wanted to provide you with a quick heads up! Apart from that, wonderful blog!
    SAP APO Online Training

    ReplyDelete
    Replies
    1. Thank you, Raju. I will have a look.

      Delete
  7. Hadoop online training in hyderabad.All the basic and get the full knowledge of hadoop.
    hadoop online training in hyderbad

    ReplyDelete

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...