Share via

SQL Server "Space-Space Available"

  • Article
  • 10/31/2012

Question

Wednesday, October 31, 2012 8:15 AM

Hi All,
I have installed SQL server 2008 express edition in Windows Server 2008 Enterprise 32 bit SP2. we created one Database named "TNPHC". last week i upgraded the DB from SQL 2008 Express to SQL 2008 R2 express edition. i aware SQL express R2 DB max size is 10 GB and our "TNPHC" Database's MDF=2.53 GB, LDF=74 MB. But, i can see the SQL database properties General tab. it shows Size=2670.31 MB, Space Available only=958.41 MB. I confused, because used 2.53 GB. Remaining must be 7.47 GB instead of 958.41 MB. Please suggest me on this. Please find the attached screenshot.

Dhakshinamoorthy Balasubramanian

All replies (4)

Wednesday, October 31, 2012 10:11 AM ✅Answered | 1 vote

This is expected. What you see as "space available" is space used compared to the *current size* if the database file, not the maximum size for the file.

Tibor Karaszi, SQL Server MVP | web | blog

Thursday, November 1, 2012 6:28 AM ✅Answered

Hi Dhakshinamoorthy,

I want to inform that “database size” property is the size of the current database, it includes both data and log files. “Space Available” is space in the database that has not been reserved for database objects. With our situation, if database objects take up all the unallocated space, data and log files will grow by “autogrowth” value (we can see these property under database, properties, Files tab) until database size reaches 10 GB. For more detail information, please refer to the following link:
 
sp_spaceused (Transact-SQL):
http://msdn.microsoft.com/en-us/library/ms188776.aspx

Database Properties (Files Page):
http://msdn.microsoft.com/en-us/library/ms180254.aspx

Allen Li

TechNet Community Support

Wednesday, October 31, 2012 8:42 AM

Why 7.47GB?If you do a backup database , you will probably get the size of the bak file =2670.31-958.41

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Wednesday, October 31, 2012 8:49 AM

can you please give the output of this query

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) ='IP3_External'

http://blog.sqlauthority.com/2010/02/08/sql-server-find-the-size-of-database-file-find-the-size-of-log-file/

Ramesh Babu Vavilla MCTS,MSBI