Recovering MSDE dtabase > 2GB

[Adam D. Barratt]

Hi,

Whilst performing a bulk insert on an MSDE 2000 database, the PRIMARY
filegroup filled up, causing the insert to fail. I tried to delete some data
from the database, which continually timed out.

Having stopped and started the MSDE service, it fails to start the database
as it's over 2GB. Is there any way I can remove some data from the database
in order to get it back under 2GB? (Upgrading the hosting machine to SQL
Server or SQL Server 2005 Express may not be an option, although using either
for the recovery would be less of an issue so long as the resulting database
can still be reattached to MSDE 2000).

Many thanks for any suggestions,

Adam

[Andrea Montanari]

hi Adam,

Post by Adam D. Barratt
Hi,
Whilst performing a bulk insert on an MSDE 2000 database, the PRIMARY
filegroup filled up, causing the insert to fail. I tried to delete
some data from the database, which continually timed out.
Having stopped and started the MSDE service, it fails to start the
database as it's over 2GB. Is there any way I can remove some data
from the database in order to get it back under 2GB? (Upgrading the
hosting machine to SQL Server or SQL Server 2005 Express may not be
an option, although using either for the recovery would be less of an
issue so long as the resulting database can still be reattached to
MSDE 2000).

using the 2005 bits can become a problem, as attaching a SQL 2000 database
on that version will upgrade all database metadata to the 2005 version and
you will no longer be able to reattach that database back to the MSDE
instance.. you could do that, but you should create a new empty (as regard
the data) MSDE database and, when you are done with cleaning up the
SQLExpress database, move the data from it back to the MSDE database.. and
SQLExpress and related management tools do not offer "wizards" for that as
the full version of SQL Server Management Studio does.. so it will be a
manual set of
INSERT INTO msde.db.dbo.table SELECT * FROM sqlexpress.db.dbo.table;
it can obviously done, but.. there's a lot of manual work to be performed..

I'd suggest to install the Evaluation Edition of SQL Server 2000, start and
clean up a copy of the offending database, shrinking it under the allowed
limit.. then move it again to the MSDE instance and, when finished,
uninstall the Eval Edition of SQL Server 2000 BUT....but I really do not
know
if this violates the Evaluation Edition EULA..