Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

MSDE – Database Size Limit

Most of you are aware that the Microsoft SQL Server 2000 Desktop Engine (MSDE) limits database size to 2 gigabytes.  However, how do you know when it reaches that limit.  Well, the database becomes inaccessible.  Yesterday one of my clients called and said, “My SQL Server is not running, can you take a look?”.  Of course I could.  I logged into the server and the SQL Server was running.  Hmmmm, I thought.  The server did not have any client tools installed, so I had to logon to another machine and connect.

Before I did that I asked the client what was the specific error?  He sent me the following screen shot:

image

While reading the error I realized one thing, the SQL Server was running as a named instance.  Typically in our environment, this usually indicates that someone in the department installed an application that’s using a Desktop version of SQL Server.  I connected to the instance via Management Studio and ran, SELECT @@version, which returned the following:

Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05   Copyright (c) 1988-2003 Microsoft Corporation  Desktop Engine on Windows NT 5.2 (Build 3790: Service Pack 2). 

I quickly noticed that it was an MSDE install.  A quick look in the error log validated my suspicions. 

2009-10-06 16:54:01.98 spid3     CREATE/ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 2048 MB per database.
2009-10-06 16:54:02.00 spid3     Could not allocate space for object 'StatisticalMemory' in database 'WhatsUp' because the 'DEFAULT' filegroup is full.

The database had reached  the 2 gigabyte limit.  As a result, I stopped the SQL Server, copied the .mdf and .ldf files to another SQL Server (SQL Server 2005 Standard Edition) and attached it to that server.

Just a little blast from the past for all of you have that have ever experienced this.

Talk to you soon

Patrick LeBlanc, Founder TSQLScripts.com and SQL Lunch

SQL Down South

Comments

Posted by Tim Mitchell on 8 October 2009

Thanks for the tip.  I knew there was a hard stop for database size, but had never experimented to determine what happened when it reached that threshold.

Posted by Patrick LeBlanc on 8 October 2009

Neither had I.  Talk to you Monday at the SQL Lunch.

Posted by Steve Jones on 8 October 2009

That's good to know. I wonder what you do if you don't have another edition handy? I'd hope that you could somehow still access it to remove some data.

Posted by Patrick LeBlanc on 8 October 2009

I am trying to find a solution for that now.  

Posted by rcm on 13 October 2009

If your app can take it try Sql Express (the 2005 edition of MSDE).They raised the limit to 4GB.

Posted by Mike Hinds on 13 October 2009

SQL Express 2005 brings many benefits but one serious downside - the loss of SQL Agent, and (for me anyway) scheduled backups. I use batch files scheduled through Control Panel Scheduled Tasks, which is not as much fun.

Posted by Patrick LeBlanc on 13 October 2009

Thanks rcm and mike for the posts.  The data was growing at such a fast rate that SQL Express was not an option.  

Leave a Comment

Please register or log in to leave a comment.