How do I check if I have hit the size limit on the Database ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi,

I have this application which inserts huge data to a database on SQL server 2008 R2 express edition. I understand that the upper limit on the size of the database is 10GB. Now, I need to inform the user if he hits the upper limit of the DB size, while trying to insert data.

Is there an exception thrown when the upper limit is breached ? How do I basically detect such a case of DB size exceed ? I use C# 4.5 and Enterprise library for DB interactions.

Any advise on this matter is highly appreciated.

Thanks.

Posted 19-Jun-15 5:59am

JamesHadleyChase

Updated 19-Jun-15 7:24am

v2

Add a Solution

Comments

PIEBALDconsult 19-Jun-15 12:06pm

I expect there would be an Exception, but you'd have to see it to know what it looks like.
In the meantime, look at dbo.sysfiles
https://msdn.microsoft.com/en-us/library/ms178009.aspx
Maybe you can give a warning before disaster strikes.

virusstorm 19-Jun-15 12:11pm

We need to clarify the exact edition you are using. You mention that you are using enterprise, which has a database size limit of 524,272TB. If you are using SQL Server 2008R2 Express, that has a limitation of 10GB.

JamesHadleyChase 19-Jun-15 13:25pm

Hi, I have an express edition. Thanks for pointing that out. I have updated the question too.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

virusstorm · Accepted Answer · 2015-06-19T07:43:00

If you try to insert data into a database and it is full, it will throw an exception. Obviously this isn't ideal, so you want to be monitoring the database size. The best way to do this is to query the system views to find out how large your database is.

This query:

SQL

SELECT *
FROM sys.database_files

Will return the details about the current database you connected to. There is a column called size which has the current allocated size of your database in kilobytes. Remember that SQL Server will allocate x amount of database space so you will have free space within the file, even though it has hit the max size.

I wrote this query to help show how much space is available in a database file. You need to use the function FILEPROPERTY and pass the value "SpaceUsed". With this query, I also convert all of the sizes into gigabytes.

SQL

SELECT
    name
   ,[filename]
   ,CONVERT(DECIMAL(12, 2), ROUND((size / 128.000) / 1024, 2)) AS [File Size in GB]
   ,CONVERT(DECIMAL(12, 2), ROUND((FILEPROPERTY(name, 'SpaceUsed') / 128.000)
                                  / 1024, 2)) AS [Space Used in GB]
   ,CONVERT(DECIMAL(12, 2), ROUND(((size - FILEPROPERTY(name, 'SpaceUsed'))
                                   / 128.000) / 1024, 2)) AS [Free Space in GB]
   ,CONVERT(DECIMAL(4, 1), ROUND(CASE WHEN CONVERT(DECIMAL(12, 2), ROUND((size
                                                              / 128.000)
                                                              / 1024, 2)) < 1
                                      THEN 100.00
                                      ELSE CONVERT(DECIMAL(12, 2), ROUND(((size
                                                              - FILEPROPERTY(name,
                                                              'SpaceUsed'))
                                                              / 128.000)
                                                              / 1024, 2))
                                           / CONVERT(DECIMAL(12, 2), ROUND((size
                                                              / 128.000)
                                                              / 1024, 2))
                                           * 100
                                 END, 1)) AS [Percent Free]
FROM
    sys.sysfiles