Thursday, October 8, 2009

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:


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 and SQL Lunch

SQL Down South

No comments:

Post a Comment