SQL Server Express Challenge - Is It Possible to Exceed the 10 GB Data Limit in a Database?

Hey guys!
Is it possible to exceed the 10GB data limit on one base? As I already mentioned in the article SQL Server Express and Developer – How far can we use the free versions of SQL Server?, the Express version has a series of technical limitations, whether hardware, such as SQL Server using only 1 CPU and 1 GB of memory, or software limitations, such as the absence of SQL Agent and a limitation on the maximum size per database of 10 GB (before SQL Server 2008 R2 it was 4 GB).

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

Script used in tests:

CREATE DATABASE [dirceuresende]
GO

ALTER DATABASE [dirceuresende] SET RECOVERY SIMPLE
GO

ALTER DATABASE [dirceuresende] SET DELAYED_DURABILITY = FORCED 
GO

USE [dirceuresende]
GO

CREATE TABLE dbo.Teste (
    Coluna CHAR(4000)
)

INSERT INTO dbo.Teste
VALUES ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'),
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa')
GO 10000

Test 1 – Autogrowth configured at 1 MB

In this first test, we will validate the maximum data size that we can insert into a SQL Server Express database, configuring automatic growth at 1 MB:

ALTER DATABASE [dirceuresende] MODIFY FILE ( 
    NAME = N'dirceuresende', FILEGROWTH = 1024KB 
)

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

Let's now analyze the size of the table and database to check the amount of data I was able to insert:

USE [dirceuresende]
GO

sp_spaceused 'dbo.Teste'
GO

SELECT 
    CONVERT(VARCHAR(25), DB.name) AS dbName,
    state_desc,
    (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
    (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
    (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
    (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],
    recovery_model_desc AS [Recovery model]
FROM
    sys.databases DB

Result:

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

Test 2 – Autogrowth configured at 11 GB

I'm going to test recreating the database and try to get around this limitation by placing an autogrowth of 11 GB and see if SQL Server will accept 1x growth and exceed the 10GB limit, even if it's just with 1 growth event.

ALTER DATABASE [dirceuresende] MODIFY FILE ( 
    NAME = N'dirceuresende', FILEGROWTH = 11GB
)

As soon as I started inserting the records into the database, error messages started to appear, that is, SQL Server understood that with 1 growth it would already exceed 10 GB and it started giving an error about the database running out of space and it didn't even try to increase the size of the database:

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

In other words, I really don't see how to get around the 10 GB limit of a user database in SQL Server Express. But what about system databases? 🙂

Test 3 – Using the master database

From the two tests performed previously, I was unable to identify a way to get around SQL Server Express's 10 GB limit on a user database. Now, I'm going to test on system databases, starting with the master database.

To do this, I will use the script below:

USE [master]
GO

ALTER DATABASE [master] MODIFY FILE ( 
    NAME = N'master', FILEGROWTH = 1GB 
)

CREATE TABLE dbo.Teste (
    Coluna CHAR(4000)
)

INSERT INTO dbo.Teste
VALUES ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'),
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), 
('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa'), ('aaa')
GO 10000

And now, let's see the volume of data we inserted into the table..

USE [master]
GO

SELECT @@VERSION, SERVERPROPERTY('Edition')
GO

sp_spaceused 'dbo.Teste'
GO

SELECT 
    CONVERT(VARCHAR(25), DB.name) AS dbName,
    state_desc,
    (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
    (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
    (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
    (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],
    recovery_model_desc AS [Recovery model]
FROM
    sys.databases DB

Result:

Oops! I exceeded the 10 GB limit and put 13 GB in the master database (I stopped because I ran out of disk space on the VM... lol... but apparently, there is no space limit for the master database)

Does this happen with other system databases too? Let's test!

Test 4 – Using the msdb and tempdb databases

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

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

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

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

Script used to validate the data:

USE [tempdb]
GO

SELECT @@VERSION, SERVERPROPERTY('Edition')
GO

sp_spaceused 'dbo.Teste'
GO


SELECT 
    CONVERT(VARCHAR(25), DB.name) AS dbName,
    state_desc,
    (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
    (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
    (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
    (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],
    recovery_model_desc AS [Recovery model]
FROM
    sys.databases DB


SELECT
    instance_name AS 'Database',
    [Data File(s) Size (KB)] / 1024 AS [Data file (MB)],
    [LOG File(s) Size (KB)] / 1024 AS [Log file (MB)],
    [Log File(s) Used Size (KB)] / 1024 AS [Log file space used (MB)]
FROM
(
    SELECT
        *
    FROM
        sys.dm_os_performance_counters
    WHERE
        counter_name IN ( 'Data File(s) Size (KB)', 'Log File(s) Size (KB)', 'Log File(s) Used Size (KB)' )
        AND instance_name = 'tempdb'
) AS A
PIVOT
(
    MAX(cntr_value)
    FOR counter_name IN ( [Data File(s) Size (KB)], [LOG File(s) Size (KB)], [Log File(s) Used Size (KB)] )
) AS B

Result:

Well, guys, in this article I wanted to demonstrate that the 10 GB limitation really exists, although there is a “Brazilian way” of getting around it. In a serious company environment, this is unthinkable, since in addition to this space barrier, there are resource limitations that are not available (Ex: SQL Server Agent) and also hardware limitations. An instance with 1 CPU and 1 GB of RAM does not support a large number of users and operations, becoming very slow as the volume of data increases.

Furthermore, it is bad database administration practice to use system databases to store data. The master database, for example, shouldn't 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 know the Developer version, and know in which scenarios you can use this COMPLETE and FREE version of SQL Server, be sure to read my article SQL Server Express and Developer – How far can we use the free versions of SQL Server?.

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