-1

I am able to determine the size (total space used, available space, etc.) of the hard drives in a C# Winforms program.

Is it possible or is there a way to determine the size of a SQL Server database(which has tables and it's values.. so it's like a record) where it's residing on a certain hard drive?

Also when I want to update or add a record in the database, is it possible that the size (total used, available space (Database-internal usage & free space.. i think) ) automatically updates in my C# form?

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
Captain Yang
  • 5
  • 6
  • 6
    **1.** Windows Forms is irrelevant to your question, so I've removed that tag. **2.** What kind of SQL database? Oracle? DB/2? SQL Server? MySQL? PostgreSQL? etc. **3.** What sizes are you speaking of in the last part of your question? Database-internal usage & free space, or filesystem usage and free space? -- Thank you for adding these details to your question via an edit. – stakx - no longer contributing Sep 03 '14 at 05:52
  • @stakx - Thank you i have updated :) – Captain Yang Sep 03 '14 at 06:00
  • I don't see any updates in your edit... Are you sure you've answered any of the questions asked by stakx? – Alexei Levenkov Sep 03 '14 at 06:03
  • @AlexeiLevenkov I just did again, is it clear enough now or still confusing? – Captain Yang Sep 03 '14 at 06:05
  • It is still unclear what exact question it should be duplicate of... Consider checking though results of this search http://www.bing.com/search?q=mssql+compute+used+space (assuming "Microsoft SQL Server"). – Alexei Levenkov Sep 03 '14 at 06:13
  • Answers copy code from http://stackoverflow.com/questions/18014392/select-sql-server-database-size - maybe that the one this post should be duplicate of. – Alexei Levenkov Sep 03 '14 at 06:57

2 Answers2

1

You should create a store procedure which will give you the output in table form and you can read the values on your front end using C# -

Just create the 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

You can then read it from your C# code to display on your screen (front end)

Murtaza
  • 3,025
  • 2
  • 24
  • 39
1

Try this one -

Manual:

  • Login Sql Server Right Click on Database from Sql Server Studio
    Click on Properties
    You Can See Size of Database in General Tab in MB's

    OR

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 link: stackoverflow.com/questions/18014392/ and http://blog.sqlauthority.com/2010/02/08/sql-server-find-the-size-of-database-file-find-the-size-of-log-file/

sms247
  • 4,256
  • 5
  • 34
  • 44
  • Your answer is surprising similar to earlier one... If you copy/pasted script from some external source consider adding link so your post look more original. – Alexei Levenkov Sep 03 '14 at 06:16
  • http://blog.sqlauthority.com/2010/02/08/sql-server-find-the-size-of-database-file-find-the-size-of-log-file/ – sms247 Sep 03 '14 at 06:49
  • and http://stackoverflow.com/questions/18014392/select-sql-server-database-size – sms247 Sep 03 '14 at 06:50
  • Why not to ask this one to be duplicate of http://stackoverflow.com/questions/18014392/select-sql-server-database-size and post answer there? Anyway - please edit links to be part of post, not comments (as comments can disappear...) – Alexei Levenkov Sep 03 '14 at 06:58