Hey guys!
É possível ultrapassar o limite de 10 GB de dados em uma base? Como eu já comentei no artigo SQL Server Express e Developer – Até onde podemos usar as versões gratuitas do SQL Server?, a versão Express tem uma série de limitações técnicas, sejam de hardware, como o SQL Server utilizar apenas 1 CPU e 1 GB de memória, quanto limitações de software, como ausência do SQL Agent e limitação do tamanho máximo por database de 10 GB (antes do SQL Server 2008 R2 eram 4 GB).
Em vista de todas as limitações citadas acima (e de forma mais detalhada no artigo acima), vou colocar à prova essa limitação de 10 GB, já que ouvi relatos de pessoas da comunidade dizerem que já viram bases com mais de 10 GB rodando o SQL Server Express.
Script utilizado nos testes:
|
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 |
Teste 1 – Autogrowth configurado em 1 MB
Nesse primeiro teste, vamos validar o tamanho máximo de dados que conseguimos inserir em uma base do SQL Server Express, configurando o crescimento automático em 1 MB:
|
1 2 3 |
ALTER DATABASE [dirceuresende] MODIFY FILE ( NAME = N'dirceuresende', FILEGROWTH = 1024KB ) |
Como era de se esperar, após algum tempo começou a aparecer a mensagem de erro abaixo na aba “Messages”:
Msg 1105, Level 17, State 2, Line 21
Could not allocate space for object ‘dbo.Teste’ 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.
Vamos agora analisar o tamanho da tabela e da base de dados para verificar a quantidade de dados que consegui inserir:
|
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:
Como podemos observar acima, exatamente ao atingir 10 GB (1024MB), o SQL Server limitou o crescimento dos dados, conforme o esperado. Agora vamos tentar burlar essa limitação.
Teste 2 – Autogrowth configurado em 11 GB
Vou testar recriar o database e tentar burlar essa limitação colocando um autogrowth de 11 GB e ver se o SQL Server vai aceitar crescer 1x e ultrapassar o limite de 10GB, nem que seja só com 1 evento de crescimento.
|
1 2 3 |
ALTER DATABASE [dirceuresende] MODIFY FILE ( NAME = N'dirceuresende', FILEGROWTH = 11GB ) |
Assim que comecei a inserir os registros no banco, já começaram a aparecer mensagens de erro, ou seja, o SQL Server entendeu que com 1 crescimento já iria ultrapassar os 10 GB e já começou a dar erro de database sem espaço e nem tentou crescer o tamanho do database:
Msg 1105, Level 17, State 2, Line 21
Could not allocate space for object ‘dbo.Teste’ 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.
Vamos ver a quantidade dados que consegui inserir no banco com essa tentativa:
Ou seja, realmente, não vejo como contornar o limite de 10 GB de um database de usuário no SQL Server Express. Mas e os databases de sistema ? 🙂
Teste 3 – Utilizando o database master
Pelos dois testes realizados anteriormente, não consegui identificar uma forma de contornar o limite de 10 GB do SQL Server Express em um database de usuário. Agora, vou testar em databases de sistema, iniciando pelo database master.
Para isso, vou utilizar o script abaixo:
|
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 |
E agora, vamos ver o volume de dados que inserimos na tabela..
|
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:
Opa! Ultrapassei o limite de 10 GB e coloquei 13 GB no database master (interrompi porque acabou o espaço em disco na VM.. rs.. mas aparentemente, não existe limite de espaço para o database master)
Será que isso acontece com os outros databases de sistema também ? Vamos testar!
Teste 4 – Utilizando os databases msdb e tempdb
Agora que vimos que é possível ultrapassar o limite de 10GB no database master, vou testar se isso é possível com outros databases de sistema também.
Database msdb
Após os testes no database msdb, concluí que ele também está abaixo da limitação de 10GB de dados. Após o último evento de crescimento possível abaixo de 10GB, o SQL Server começou a retornar as mensagens de erro de falta de espaço no database:
Msg 1105, Level 17, State 2, Line 21
Could not allocate space for object ‘dbo.Teste’ 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
Após os testes no database tempdb, concluí que, assim como o database master, o database tempdb NÃO está dentro do limite de 10 GB, ou seja, você consegue inserir mais de 10 GB nesses dois databases, conforme os resultados abaixo comprovam.
Important: Lembre-se que o database tempdb é recriado e todos os dados armazenados são perdidos sempre que o serviço do SQL Server é reiniciado, ou seja, se você pensou em armazenar qualquer informação nesse database, sugiro mudar de ideia.
Script utilizado para validar os dados:
|
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:
Bom, pessoal, nesse artigo eu quis demonstrar que a limitação de 10 GB realmente existe, embora tenha um “jeitinho brasileiro” de contorná-la. Em um ambiente de uma empresa séria, isso é algo impensável, já que além dessa barreira de espaço, existem limitações de recursos que não estão disponíveis (Ex: SQL Server Agent) e também limitações de hardware. Uma instância com 1 CPU e 1 GB de memória RAM não suporta uma quantidade grande de usuários e operações, se tornando bem lenta à medida que o volume de dados vai aumentando.
Além disso, é uma péssima prática de administração de banco de dados utilizar bases de dados de sistema para armazenar dados. O database master, por exemplo, não deveria nem ter objetos de usuário lá. Não é à toa que esse artigo está na categoria “O que não fazer” do meu blog.. rs
Caso você queira conhecer todas as limitações do SQL Server Express e conhecer também a versão Developer, e saber em quais cenários você pode utilizar essa versão COMPLETA e GRATUITA do SQL Server, não deixe de ler o meu artigo SQL Server Express e Developer – Até onde podemos usar as versões gratuitas do SQL Server?.
Espero que tenham gostado desse artigo, um grande abraço e até a próxima!
Portuguese 
English
Tenho uma base de dados par cada cliente (300 bases), mas no meu escritório acesso todas (uma de cada vez), para ter consultas/estatísticas globais, todas bases juntas dá 10GB (medidos pelo Explorer do windows. problemas ainda não apareceram
o limite e por banco, isso não impede de se criar varios bancos, dividindo as tabelas.
e no banco principal criar views buscando as tabelas desse banco, dessa forma para a aplicação, fica transparente como se fosse um banco apenas.
se o banco chegou nesse limite, e o cliente nao quer por gastar com a versão licenciada, é uma solução para o sistema não parar.
Dirceu, boa tarde.
Adicione mais um filegroup e mais um arquivo de Dados e veja o que acontece.