Views: 13.019 views
Speak guys!
Is it possible to exceed the limit of 10GB of data on one basis? As I already 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
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 we can insert into a SQL Server Express base by setting automatic growth to 1 MB:
|
1 2 3 |
ALTER DATABASE [dirceuresende] MODIFY FILE ( NAME = N'dirceuresende', FILEGROWTH = 1024KB ) |
As expected, after a while the following error message started to appear 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 for existing files in the filegroup.
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 for 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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 by reaching 10 GB (1024MB), SQL Server has limited data growth as expected. Now let's try to circumvent this limitation.
Test 2 - Autogrowth configured at 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.
|
1 2 3 |
ALTER DATABASE [dirceuresende] MODIFY FILE ( NAME = N'dirceuresende', FILEGROWTH = 11GB ) |
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 for existing files in the filegroup.
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 for 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? 🙂
Test 3 - Using the 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
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 look at the amount of data we entered in the table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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 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!
Test 4 - 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 for existing files in the filegroup.
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 for 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
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” to get around it. In an environment of a serious company, 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.
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!
I have a database for each client (300 bases), but in my office access all (one at a time), to have global queries / statistics, all bases together gives 10GB (measured by windows explorer. Problems have not yet appeared
The limit is per bank, this does not prevent the creation of several banks, dividing the tables.
and in the main bank create views by looking up the tables in this bank, so for the application, it is transparent as if it were just a bank.
If the bank has reached this limit, and the customer does not want to spend on the licensed version, it is a solution for the system not to stop.
Dirceu, good afternoon.
Add one more filegroup and one more Datafile and see what happens.