asked on

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

dipopo

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.

Zberteoc

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

PantoffelSlippers

ASKER

dipopo,

Thanks, this is a clean installation. No previous SQL Server instance on this machine.

Zberteoc

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?

PantoffelSlippers

ASKER

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

Zberteoc

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.

PantoffelSlippers

ASKER

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

Zberteoc

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.

PantoffelSlippers

ASKER

it works as long as I stay below 4GB on the datafile

SOLUTION

dipopo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PantoffelSlippers

ASKER

Mmmmm.... Now I'm confused. I thought there was only one Express version.

SOLUTION

Zberteoc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PantoffelSlippers

ASKER

So... I'm looking for the middle one then....

ASKER CERTIFIED SOLUTION

PantoffelSlippers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PantoffelSlippers

ASKER

Looks like it's working!

PantoffelSlippers

ASKER

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.

dipopo

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.

dipopo

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.

PantoffelSlippers

ASKER

No worries. I hope you are comfortable now. If not, please let me know.

Thanks again

Zberteoc

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.

dipopo

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

PantoffelSlippers

ASKER

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