5

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?

Improve this question
4
  • 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, 2010 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.
    – Shane
    Aug 3, 2010 at 18:05
  • Are the DB files on a local NTFS formatted drive?
    – Chris S
    Aug 3, 2010 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, 2010 at 18:56

3 Answers 3

Reset to default
6

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.

Improve this answer
1
  • In the end I resorted to a complete reinstall, which worked. Thanks to everyone for the suggestions!
    – Jesse
    Aug 9, 2010 at 20:07
2

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.

Improve this answer
5
  • 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, 2010 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, 2010 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, 2010 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, 2010 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, 2010 at 6:49
2

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.

Improve this answer
1
  • Hello TomTom. The database is already configured to autogrow as needed. That is the first thing I checked. :)
    – Jesse
    Aug 4, 2010 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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