SQL Server 2008 R2 Enterprise database has unexpected 4GB database size limit
I have SQL Server 2008 R2 Enterprise installed on a local Windows 7 x64 workstation. When I create a database on the server, it unexpectedly has a 4GB size limit (Database properties in SQL Server Management Studio say size = 3934.38 MB, space available = 47.13 MB).
Unfortunately the database needs more than 4GB, and Enterprise is not supposed to have a practical maximum size. I confirmed the database is on the Enterprise server:
SELECT @@VERSIONMicrosoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
The database file is not set to restrict growth in SQL Server Management Studio, and there is plenty of hard drive space. The database was copied from SQL Express (which has a 4GB limit), but the same occurs with a fresh database creation.
I've spent a couple of hours trying to figure this out and Google-searching, to no avail. Any ideas?
Was there a SQL Express installation on there at one time? The thing confusing me is that SQL Server 2008 R2 Express has a 10GB limit so I have no idea where the 4GB is coming from. Maybe the upgrade process from 2005 Express to 2008 R2 isn't working properly and doesn't change the database size limit.
My best advice would be a complete reinstall of the SQL software. The only way I can imagine that a 4GB limit would be in place is if SQL Server 2005 Express was installed first.
Can we see the error? are you getting an error when the db tries to grow beyond 4GB? anything in the errorlog? do you get an error when you try to grow/add a datafile manually?
The 4GB limit with express is in the binary, not in the data file! It would not make sense otherwise.
it unexpectedly has a 4GB size limit (Database properties in SQL Server Management Studio say size = 3934.38 MB, space available = 47.13 MB).
Häh?
Can it be the database file is created with 4gb, NO autogrow? Then this is not a limit, is simply is the size of the file.
Grow the database or enable autogrow and it will grow.