How To Find Size Of Database In SQL Server

When you create a new Database, at that time you can mention how much sizeb this database should use initially. That much size your database will use till all of that size don’t get used. In the following figure, new database will use 4MB of size by default, whether it will have that much data in it or not. But once the database gets data of that much size it starts growing its size according to the size of data.

You can find out how much size your database is using by any of the following methods.

Instructions:

1. Using  SP_SPACEUSED Stored Procedure
   • It will give you two result tables, and the upper table will give you the information regarding the size of database in Megabytes. Column database_size gives you the total size of the database.
     
     

     USE HowToIdeas
EXEC sp_spaceused

     

2. Using SP_HELPDB Stored Procedure
   • This stored procedure will give you some information about every database in your SQL Server. db_size column gives information regarding size of the database.
     
     

     EXEC sp_helpdb

     

3. Using SYSFILES Table
   • This table contains information about the files of the database. By default we have two files, primary .mdf file and the other one is log file. This table contains information about both the files and size as well denoted under  size column. The size denoted here is in the 8kb page size, which means the total size of every file is size given by the column multiplied by 8. So, if you just want to find out total size of the database, you can use the following query to get result in Megabytes.
     
     

     USE HowToIdeas
SELECT SUM(size) * 8 / 1024 FROM SYSFILES

     

4. Using SYS.DATABASE_FILES Table
   • This table is quite similar to SYSFILES and stores result of every database file. You can calculate the resultant size the same way as above table.
     
     

     USE HowToIdeas
SELECT SUM(size) * 8 / 1024 FROM SYS.DATABASE_FILES