There are different ways to find the DB size. The easiest way is:
1. Login to SQL Server Management Studio –> Select the DB—>Right click on Properties
The popup as shown below will show the DB size-
The size shown 3743.38 MB is sum of Data and Log files. Point to understand here is that this is the space allocated to store Data and Log transactions. It doesn’t mean that the real size of DB is this much. Space Available 2838.73 MB is the space still available to fill up. Once this space is used up then SQL Server will allocate new Data space and Log space as er growth rate set.
2. Use sp_helpdb to get the DB size. Along with the DB size it will give Data and Log file path and growth rate information as well. You can verify that DB_Size shown is sum of Data and Log file size shown.
3. Execute sp_spaceused to find the real space used by Data files in DB. The first result gives the DB size which is sum of Data and Log files. The second result shows the total space used by Data files under the ‘reserved’ column. Rest three columns give break up of the ‘reserved’ column space. So in below example-
a) Sum of Data and Log Space = 3529.55 MB
b) Unallocated space = 2839.07 MB
c) Used Space by Data and Log files = 3529.55 – 2839.07 MB = 690.48 MB
d) Data file Space (its same as shown in reserved column) = 706552/1024 MB = 689.99218 MB
e) Difference in (c) and (d) gives Log file space.
When you use this SP you may find that the calculations are not matching. To get the correct results, you should perform Shrink of database files. To shrink the database files, right click the database—>Task—>Shrink—>Files
Shrink both Data and Log files-
4. Use DBCC SHOWFILESTATS. This gives the Data file space in terms of Extents.
Each Extent in SQL Server is made of 8 Pages. Each Page is 8K. So Each Extent is 64K.
5. Similar information can be used from following queries as well –
select * from sys.master_files
select * from sys.database_files
The size column in sys.master_files is supposed to contain the current size of the file in 8K pages. So multiply the value by 8 to get size in KB. Further divide by 1024 will give size in MB.
6. Finally if you want to find the DB size for many DB’s then use the below query to get the details –
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB, (size*8) as SizeKB, SUM(size*8) OVER (partition by database_id) as [Total Size in KB]
FROM sys.master_files
WHERE DB_NAME(database_id) IN (select db_name(database_id) from sys.master_files)
GO