SQL Server Express - CHALLENGE: Is it possible to exceed the 10 GB limit of data on a basis?

Views: 4.636
Reading Time: 11 minutes

Hey guys!

In the last 2018 technical post, I would like to ask you a challenging question: Is it possible to exceed the 10 GB data limit on a basis? As I commented in the article SQL Server Express and Developer - How far can we use free versions of SQL Server?Express has a number of technical limitations, such as hardware, such as SQL Server using only 1 CPU and 1 GB of memory, software limitations, such as the absence of SQL Agent, and limiting the maximum database size of 10 GB ( before SQL Server 2008 R2 were 4 GB).

In view of all the limitations mentioned above (and in more detail in the article above), I will test this limitation of 10 GB, as I have heard reports from people in the community saying that they have seen databases with more than 10 GB running SQL Server Express.

Script used in testing:

1 Test - Autogrowth Configured in 1 MB

In this first test, we will validate the maximum data size we can insert into a SQL Server Express base by setting automatic growth to 1 MB:

As expected, after some time the error message below appears in the “Messages” tab:

** An error was encountered during execution of batch. Continuing
Msg 1105, Level 17, State 2, Line 21
Could not allocate space for object 'dbo.Test' in database 'dirceuresende' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on existing files in the filegroup.

Let's now look at the size of the table and database to see how much data I was able to enter:

Result:

As we can see above, exactly by reaching 10 GB (1024MB), SQL Server has limited data growth as expected. Now let's try to circumvent this limitation.

2 Test - Autogrowth Configured on 11 GB

I will try to rebuild the database and try to circumvent this limitation by putting an autogrowth of 11 GB and see if SQL Server will accept 1x grow and exceed the 10GB limit, if only with 1 growth event.

As soon as I started to insert the records in the database, error messages started to appear, that is, SQL Server understood that with 1 growth it would already surpass 10 GB and it started giving database error without space and didn't even try to grow it. database size:

** An error was encountered during execution of batch. Continuing
Msg 1105, Level 17, State 2, Line 21
Could not allocate space for object 'dbo.Test' in database 'dirceuresende' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on existing files in the filegroup.

Let's see how much data I was able to insert into the database with this attempt:

That is, I really don't see how to circumvent the 10 GB limit of a user database in SQL Server Express. But what about system databases? 🙂

3 Test - Using Database Master

From the two tests I previously performed, I couldn't identify a way around the SQL Server Express 10 GB limit on a user database. Now, I will test on system databases, starting with the database master.

For this, I will use the script below:

And now, let's look at the amount of data we entered in the table.

Result:

Oops! I exceeded the 10 GB limit and put 13 GB in the database master (I interrupted because the disk space on the VM .. rs .. but apparently there is no space limit for the database master)

Does this happen to the other system databases as well? Lets test!

4 Test - Using the msdb and tempdb Databases

Now that we have seen that it is possible to exceed the 10GB limit on the database master, I will test if this is possible with other system databases as well.

Database msdb
After testing the database msdb, I concluded that it is also below the 10GB data limitation. After the last possible growth event below 10GB, SQL Server began returning database space out error messages:

** An error was encountered during execution of batch. Continuing
Msg 1105, Level 17, State 2, Line 21
Could not allocate space for object 'dbo.Test' in database 'dirceuresende' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on existing files in the filegroup.

Database tempdb
After testing the database tempdb, I concluded that, like the database master, database tempdb is NOT within the 10 GB limit, meaning you can enter more than 10 GB in these two databases, as the results below prove.

Important: Remember that the tempdb database is rebuilt and all stored data is lost whenever the SQL Server service is restarted, ie if you thought of storing any information in that database, I suggest changing your mind.

Script used to validate data:

Result:

Well, guys, in this article I wanted to demonstrate that the 10 GB limitation really does exist, although there is a “Brazilian way” to circumvent it. In a serious business environment, this is unthinkable, as beyond this space barrier there are limitations of features that are not available (eg SQL Server Agent) as well as hardware limitations. An instance with 1 CPU and 1 GB of RAM does not support a large amount of users and operations, becoming very slow as the data volume increases.

In addition, it is a bad practice for database administration to use system databases to store data. The database master, for example, should not even have user objects there. No wonder this article is in the category “What not to do” from my blog .. lol

If you want to know all the limitations of SQL Server Express and also the Developer version, and know in which scenarios you can use this FULL and FREE version of SQL Server, be sure to read my article. SQL Server Express and Developer - How far can we use free versions of SQL Server?.

I hope you enjoyed this article, a big hug and see you next time!