Size of a SQL Server DB

This weekend I had to find a way to calculate the size (on disk) of a MS SQL Sever database. I got two solutions, both of which worked great. The first solution came from one of my coworkers, Dave An:

SELECT (size * 8) as filesize, filename from sysfiles

This returned the size of both the main data file and the log file. Dave made sure to mention that this assumes a default setting for the database page size.

Another solution was provided by Jared Riley:

<CFSTOREDPROC DATASOURCE=”yourdatasource” PROCEDURE=”sp_helpdb” RETURNCODE=”Yes”>
   <CFPROCRESULT NAME=”RS1”>
</CFSTOREDPROC>

CFOUTPUT QUERY = RS1>
#name# #ReplaceNoCase(db_size, “ MB”, “”, “ALL”)# #owner# #created#
</CFOUTPUT>

This returns the complete size of the db, i.e., the data and log file combined.

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate for Extend by Auth0. He focuses on serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support.

Lafayette, LA https://www.raymondcamden.com

Comments