0

I'm developing an app for which I need some simple information about a given Microsoft SQL Server:

  • The maximum size the server supports¹
  • The total free space still available for a database to grow²

I googled about this and even though there are many pages on the matter with many different answers, I couldn't find anything that suits me because either it was too fancy (e.g. writting complex tables to files) or didn't actually work. The context I'm in is a simple software to manage the database; it will get those info, store in integer variables and then use them to work. So my idea here is simply to do a query, select style, grab one data and move on. An example of the only "function" I managed to find that would fit these criteria:

SELECT size FROM sys.master_files WHERE DB_NAME(database_id) = \'DB_NAME_HERE\'²

(Note: this is just an example, the function doesn't actually return what I want)

¹: For example, the Express editions may have up til 10Gb of allowed storage capacity (it depends on the version). So the function here should return a value close to that.

²: In advance: to know my database's size doesn't matter since it's a server with many databases. My app's logic needs the free space available for any database to grow, so calculating "total space - my db space" won't work.

Momergil
  • 2,213
  • 5
  • 29
  • 59
  • 2
    For express, the max size is 10GB, for a **paid** version it's something silly; in the PB. The documentation covers that. If you're using not express, your storage device will be the limitation, not SQL Server – Thom A May 28 '19 at 17:55
  • 1
    How you define: "total free space still available for a database to grow" in a context of a database with multiple files located on multiple drives with different auto-grow settings? – Piotr Palka May 28 '19 at 18:09
  • @Larnu well not actually, it depends on the Express version as the link in the OP mentions. It would be nice to have a query which gives that value instead of asking the user to fill in a GUI LineEdit (if I'm not mistaken, Oracle does have such a method). About the paid version, I suppose the proper way is to look for disk storage free space indeed. Yet the query, if existant, could automatically do that as well. – Momergil May 28 '19 at 18:25
  • @Piotr I define in the following way: suppose I have SQL 2017 Express, so 10Gb of available space. Suppose my DB already has 1Gb of storage space used and other two DBs in the server occupy 500Mb and 1500Mb each. So of a total available of 10Gb, I already have 1000+500+1500 = 3Gb used which gives me 7Gb of "total free space still available for a database to grow". In paid version, this value would essentially be equal to the free space in disk. – Momergil May 28 '19 at 18:30
  • @Momergil why would you choose Express 2008, which has less than 2 month of support left? Every (truly) supported version of Express is 10GB. – Thom A May 28 '19 at 18:34
  • @Momergil each database can be 10GB in size. Not the total size of all databases. You could have 100 9GB databases on Express and be "fine". – Thom A May 28 '19 at 18:35
  • The T-SQL `create database...` statement takes a `MAXSIZE` argument in most contexts. Do you care about that? [Docs](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?view=sql-server-2017) – Mike Sherrill 'Cat Recall' May 28 '19 at 18:37
  • 1
    To get free space on the disc you can use "EXEC MASTER..xp_fixeddrives" – Piotr Palka May 28 '19 at 18:37
  • @Larnu Unfortunately some of my clients are still using old MS SQL servers :( About the 10Gb clarifiction, oops, my mistake, and very nice to learn about that! Unfortunately I can't just hardcode that value since I'm still stuck with the previous problem of some express servers not having 10Gb of free space. – Momergil May 28 '19 at 18:48
  • @MikeSherrill'CatRecall' I do remember reading about this `MAXSIZE` stuff, but unfortunately the examples I found on the web, when run in my local express server, returned a -1 value and that can't happen. If you insist it must work, please provide me with an example query for this `MAXSIZE` so I may give it another chance. – Momergil May 28 '19 at 18:51
  • 1
    Considering that you seem to be developing, and Express is free, then you'll have to force them to upgrade then @Momergil. Someone using SQL Server Express 2008 has no excuse to not upgrade in my view. They've had 7 years to fix breaking changes introduced with 2012, if they suffered any; if they haven't fixed them by now they deserve to not be supported. – Thom A May 28 '19 at 18:51
  • 1
    '-1' just means the maximum size is unlimited. Start with `select physical_name, name, type, type_desc, size, max_size from sys.database_files;` Since you seem to be writing this for clients, 1) don't assume there are only two files, 2) don't assume your clients use well-known file extensions. Consider that some clients might be using a cloud-based SQL Server (Azure SQL, Microsoft SQL Server on AWS, etc). – Mike Sherrill 'Cat Recall' May 28 '19 at 20:02

0 Answers0