Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 @@VERSION
Microsoft 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?

share|improve this question
    
The database was copied from SQL Express, and that's your problem right there. I'd script out the database, get rid of all versions of SQL Server on the machine, and reinstall 2008 R2 again. –  user3914 Aug 3 '10 at 18:02
    
He notes that this same problem happens with new databases as well. But I agree the best course of action seems to be a reinstall. –  Sean Howat Aug 3 '10 at 18:05
    
Are the DB files on a local NTFS formatted drive? –  Chris S Aug 3 '10 at 18:43
    
@Chris S: Yes, they're automatically stored in [C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA]. The drive is NTFS, with 382GB of free space. –  Jesse Aug 3 '10 at 18:56

3 Answers 3

up vote 6 down vote accepted

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.

share|improve this answer
    
In the end I resorted to a complete reinstall, which worked. Thanks to everyone for the suggestions! –  Jesse Aug 9 '10 at 20:07

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.

share|improve this answer
    
The C# adapter error states: "Could not allocate space for object 'dbo.<TABLE>'.'PK_<TABLE>_1' in database '<DBNAME>' 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." –  Jesse Aug 4 '10 at 13:16
    
Manually growing the database succeeds, but does not affect the error. Adding a file to the filegroup increases the unallocated space to 619.76MB, but does not affect the error. There is plenty of hard disk space, and this is the only database in the filegroup. There's nothing relevant in the error log. –  Jesse Aug 4 '10 at 13:16
2  
can you grow the db past 4GB? sounds like you dont have auto grow turned on, or have a max size set. Follow what the error message is telling you to do. That sums up your issues. –  Nick Kavadias Aug 4 '10 at 13:20
    
Autogrow is enabled. I can grow the database manually, but the database refuses all data beyond 4GB even when the database size is manually set above 4GB. I did try the error message's suggestions. :) (Dynamo's suggestion for a full clean reinstall is starting to look attractive.) –  Jesse Aug 5 '10 at 0:07
    
SQL Server has some shared component files. Looks like one of the files responsible for these limits is in there. What versions of SQL Server do you have installed on your machine? what have you had previously installed & what order did you install them in? –  Nick Kavadias Aug 5 '10 at 6:49

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.

share|improve this answer
    
Hello TomTom. The database is already configured to autogrow as needed. That is the first thing I checked. :) –  Jesse Aug 4 '10 at 12:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.