MSDE mdf file over 2GB!

  • I have a customer whose mdf file has exceed 2GB on MSDE and now can't connect to the database. When I did some testing with MSDE I tried to exceed the 2gb limit but got the PRIMARY FILEGROUP full error and the size of the mdf was about 1.8GB. I can't understand how the mdf has exceeded the 2GB limit.

    Any one got any ideas?

  • Has it always been an MSDE database and what version of MSDE is it?

  • Yes it has always been MSDE, they have MSDE 2000 SP3

  • How they got above 2GB is beyond me, but you might try using QA to connect to the server and shrink the database to see if that will work. If it does then you can get in there again. If not then you could restore the database to another SQL 2000 edition (personal or standard), get in and shirnk it then restore it back. But if the database is that full with data they may have no choice but to goto SQL Standard edition at least.

  • You can get an MSDE database above the 2GB limit if you restore an existing - non MSDE -database using the with move option. I had one around 8GB which was OK for querying - but any attempt to extend the database results in the "GB limit error.

  • Yes, MSDE DBs are limited to 2GB. You can, however, move some of the data to a "Historical" DB in the same Server because the 2GB limit pertains to each database on the MSDE Server.

  • This can be a real hassle. I have had a couple of customers on MSDE have this happen. Once it hits the limit, you can't do any query operations at all.

    What usually happens is that the database is set to auto-grow, and the server will push it over the limit by allocating the next block of space. For my customers, I found that there was usually alot of unused space in the database (another problem all together). I had to create a temporary database, use a third party tool and pump the data into the temp database (this eliminated any empty space because everything was then contiguous) then backup the temp and restore it into the regular DB.

    If the database you're having problems with really has 2 gigs of data in it, then you'll have to to something else, such as detach it, attach it to a full SQL Server version, and purge some data, or install full SQL on the offending server.

    Bob

    SuccessWare Software


    Bob
    SuccessWare Software

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply