Can I determine the size of a SQL Server database?
I can determine the size (total space, available space, etc.) of hard drives in a C # Winforms program.
Is this possible or is there a way to determine the size of the SQL Server database (which has tables and values ... so it's like a record) where it resides on a specific hard drive?
Also when I want to update or add a record to the database, is it possible that the size (total used, available space (internal database use and free space ... I think)) is automatically updated in my C # form?
source to share
You have to create a storage procedure that will give you the result in table form, and you can read the values at your front end using C # -
Just create a stored proc in your database with any name like 'getDatabaseSizes'
with below Query -
SELECT
database_name = DB_NAME(database_id)
, log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID('[dbname]')
GROUP BY database_id
Then you can read it from your C # code for display on screen (front end)
source to share
Try this option -
Manual:
-
Sql Server Login Right click on the database from Sql Server Studio
Click Properties
You can see the size of the database under the General tab in MB'sOR
Query:
option 1:
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'
GO
OR TRY This query: -
option-2:
SELECT
database_name = DB_NAME(database_id)
, log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID('[yourDBName]') -- for current db
GROUP BY database_id
ref reference: stackoverflow.com/questions/18014392/ and also http://blog.sqlauthority.com/2010/02/08/sql-server-find-the-size-of-database-file-find-the-size -of-log-file /
source to share