Purging a SQL Server Express Database

This is not another IRL post, but rather is more of “Help, my SQL Express database is full!” post.

Dog that barely fits in her bed
Just barely fits

I was contacted by a lawyer that was using a 3rd party application to store emails, keep track of time, etc.

The backend of the application is SQL Server Express edition, which has a hard limit of 10GB for the data file.

The biggest table by rowcount and size in the DB is (of course) the email table, which stores the email body in a ‘text‘ datatype. I don’t know what version of SQL Express he is using, but I was working with a restored copy on SQL 2016 Developer. His vendor was no help.

Steps we tried after a lot of digging and testing:

0. Initial datafile size: 9969MB

1. Purge oldest 20%

Delete [email]
Where sysid in 
  (Select top 20 percent sysid from [email] order by date);

File size 9600MB. Not much improvement

2. Purge by a specific date

Delete [email]
Where [date] < '2012-01-01'

File size 9500MB. Slightly better.

3. Of the remaining emails, delete by largest memo field, older than 1/1/2017:

Select top 1000 sysid, len(Cast(memo as nvarchar(max))) as size
Into #BigMemos
from [email]
Where [Date] < '2017-01-01'
Order by len(Cast(memo as nvarchar(max))) desc

Delete [email]
Where sysid in (Select sysid from #BigMemos)

Drop table #BigMemos

Still not much difference, but at this point I know the indexes and half-full pages must be in full chaos mode

4. Rebuild, then Reorganize all of the indexes on this table

Nope...can't show the statements...too much application specific naming :)

Down to 9200MB

5. Time to Shrink!

DBCC SHRINKFILE (N'MyDB', notruncate);

DBCC SHRINKFILE (N'MyDB', truncateonly);

-- Yes, both were required, and in this order. Your mileage may vary

This got us down to 8696 MB, which will last my new lawyer friend a solid 3-5 years.

And yes, I billed him lawyer rates for this 😉

If you need part-time DBA help, or just want to have a Senior DBA around for emergencies, Contact us!

Thanks for reading!


Leave a Comment

Sign up for our Newsletter

%d bloggers like this: