Sometimes we need to know how much disk space used by databases in SQL SERVER. There are multiple ways to know the database size in SQL SERVER.
1. Using Table Sys.master_files
2. Using Stored Proc sp_spaceused
3. Using Manual Option in SSMS
Using Table Sys.master_files
This is one option by which we can know database size. Below query uses two tables databases which contains database ID, Name etc and another table master_files which contains size columns holds size of database. By using Inner join(database ID) we are getting database size. Both tables are present in master database.
- SELECT sys.databases.name,
- CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Total disk space]
- FROM sys.databases
- JOIN sys.master_files
- ON sys.databases.database_id=sys.master_files.database_id
- GROUP BY sys.databases.name
- ORDER BY sys.databases.name
See below Figure 1 after executing above query which gives all the databases with their sizes.
Figure 1: Get Database Size
Using Stored Proc sp_spaceused
This is 2nd option to know database size. Here we will call stored procedure sp_spaceused which is present in master database. This one helps to know size of current database.
After calling above stored procedure it shows below Figure2 which contains column called database_size surrounded by red mark.
Figure 2: Get Database size by SP
Using Manual Option in SSMS
This is another option to know database size. To know size Go to Server Explorer -> Expand it -> Right click on Database -> Choose Properties -> In popup window choose General tab ->See Size property which is marked(red) in Figure3.
Figure 3: Manual option to get Database size
Hope it helps you to get database size in SQL SERVER.
Happy Coding!!