SQL Server 2008 database size issue

Good day experts,

I have a SQL Server 2008 R2 Express installation.  According to Microsoft's website, this edition has a database size limit of 10GB.

My applications and the SQL Server machine's windows event log are reporting errors related to exceeding licensed database size limit of only 4096MB.

The exact error in the eventlog recorded against my SQL Server instance name is: "CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database."

When I run SELECT @@VERSION, the following is returned:
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition with Advanced Services on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

I'm assuming this is a common problem.  How do I rectify this?

Many thanks
PantoffelSlippersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dipopoCommented:
Was this formerly a MS SQL 2005 express that you upgraded? If so backup your databases, uninstall the current instance(s) and re-install, restore DB and you should get the full 10GB.
0
ZberteocCommented:
Try to explicitly specify the database size at creation time. It is possible that there is a value that is used grater than the maximum size PER DATABASE, which is 4096 MB. The 10GB is the overall size.

CREATE DATABASE [your_db] ON  PRIMARY 
( NAME = N'your_db', FILENAME = N'F:\SQLData2\your_db.mdf' , SIZE = 3072KB , MAXSIZE = 4194304KB , FILEGROWTH = 524288KB )
 LOG ON 
( NAME = N'your_db_log', FILENAME = N'E:\SQLLogs\your_db_log.ldf' , SIZE = 3072KB , MAXSIZE = 4194304KB , FILEGROWTH = 527360KB )
GO

Open in new window

0
PantoffelSlippersAuthor Commented:
dipopo,

Thanks, this is a clean installation.  No previous SQL Server instance on this machine.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ZberteocCommented:
Have you tried to create the database like with the script I gave you? If yes and didn't work what error did you get?
0
PantoffelSlippersAuthor Commented:
Zberteoc,

I get the same error message on the create database statement if my MDF file goes beyond 4096MB in the create database statement.

CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.

Thanks
0
ZberteocCommented:
Are you trying to restore a database from a SQL 2005 bakup? The 4096MB limit was for 2005 express version. if yes make sure that when you create the database the compatibility option is set for 2008.
0
PantoffelSlippersAuthor Commented:
Hi

No I'm not trying to restore a database. I created a database from scratch.  I checked the compatibility level already - it was set to 2008.

Thanks
0
ZberteocCommented:
Decrease the initial database size and see if it allows you to create an empty database. The initial size of a new database is given by the size of "model" system database initial file size. You can check and see how much is that.
0
PantoffelSlippersAuthor Commented:
it works as long as I stay below 4GB on the datafile
0
dipopoCommented:
You have the wrong version friend. :-)

SQL Server 2008 RTM is 10.0.1600.22

You need version 10.50.1600.1 2008 R2 to achieve 10GB.
1
PantoffelSlippersAuthor Commented:
Mmmmm.... Now I'm confused. I thought there was only one Express version.
0
PantoffelSlippersAuthor Commented:
So... I'm looking for the middle one then....
0
PantoffelSlippersAuthor Commented:
OK...

So I uninstalled all existing SQL Server components (backed up my database first), downloaded a new installation of SQL Server 2008 R2 Express SP2 as per links above and installed.

Below is a comparison of my version numbers on the previous installation compared to my new installation now:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition with Advanced Services on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
vs
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (Intel X86)   Jun 28 2012 08:42:37   Copyright (c) Microsoft Corporation  Express Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

I will now proceed to restore my backed up database and see if I can reach 10GB with it.

Will feedback ASAP.

Thanks
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PantoffelSlippersAuthor Commented:
Looks like it's working!
0
PantoffelSlippersAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 300 points for dipopo's comment #a39553368
Assisted answer: 200 points for Zberteoc's comment #a39553634
Assisted answer: 0 points for PantoffelSlippers's comment #a39554599

for the following reason:

No additional information needed.  Experts pointed out I wasn't using the correct version of SQL Express and also showed me where to get the correct version.  Excellent help - thank you.
0
dipopoCommented:
It would be preferable if the points were allocated for the effort of understanding the problem, identifying the issue and suggesting a solution, this took time and effort.
0
dipopoCommented:
Please disregard my earlier posting, I did not see that points were allocated, this did not show up until the object button had been clicked.
0
PantoffelSlippersAuthor Commented:
No worries.  I hope you are comfortable now.  If not, please let me know.

Thanks again
0
ZberteocCommented:
I am confused. Why not simply granting the points as usual and the request for closing the question instead?

I didn't get any points by the way and I think I should, based on the asker comments.

The way this works is you have to grant the points yourself and not to ask the question to be closed.
0
dipopoCommented:
Hi Zberteoc, he did actually allocate you points, hard to see as a result of the closure request.


Accepted answer: 300 points for dipopo's comment #a39553368
Assisted answer: 200 points for Zberteoc's comment #a39553634
Assisted answer: 0 points for PantoffelSlippers's comment #a39554599
0
PantoffelSlippersAuthor Commented:
Hi all,

I am sorry - not sure what happened here.  I found a solution and allocated points to Zberteoc and dipopo but it seems they were not able to see this and the question is still open.

Did I do something wrong here?

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.