How do I reindex my SQL Server Express database to free up space when it's already full?

Question

Being the naive guy that I am, I let my DB get full (10GB is the max for SQL Server Express), and have now decided to drop columns to reclaim space. I'm dropping 10 columns from a ~50M row table to save space, but even after dropping them, I keep getting "Error 1011: Could not allocate a new page for database ...".

I cannot shrink the database, I cannot reindex the database, I cannot rebuild indexes, I can't add a filegroup, or anything like that. I have 97% fragmentation (even after defragging my hard drive), and I feel like I'm running out of options. Even attempting to run an ALTER INDEX .. REBUILD WITH (SORT_IN_TEMPDB = ON) gives the same error message.

What are my options now? Should I just migrate to a different DB (MYSQL, PostgreSQL)?

Well, you could create a new database, build the skinnier table and supporting objects, and copy the data using insert/select, import/export wizard, BCP, SSIS, etc. Migrate to MySQL/PostgreSQL? When your Honda gets a flat tire, do you trade it in for a Toyota? — Aaron Bertrand, Oct 14 '15 at 02:34
Ah I see. Thanks. How do I do this without overflowing my 1GB memory limit? — ijustlovemath, Oct 14 '15 at 02:37
If any of the columns were variable length, you could try running DBCC CLEANTABLE to reclaim space. — Erik Darling, Oct 14 '15 at 02:47

score 3 · Accepted Answer · answered Oct 14 '15 at 02:46

Create a new database.

Create the skinnier version of your table.

Create supporting objects (procedures, views, users, other tables, what have you).

Migrate the data (or some subset of the rows) from the original database to the skinnier version of the table in the new database, using methods like:

insert newdb.dbo.table(...) select cols from olddb.dbo.table;
SSIS
BCP
import/export wizard

Stay on top of your new database as it grows. Update your connection strings to point at the new database (or drop the old one and rename the new one to the old name).

Technical problems with specific software or platforms tend to have simpler solutions than throwing hands in the air and switching do different software / platform...

Another big draw of the other DB's is no limit on size. That's not to say I don't see the draw of paying for the full license. SSMS is a godsend. — ijustlovemath, Oct 14 '15 at 02:48

How do I reindex my SQL Server Express database to free up space when it's already full?

1 Answers1