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.

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);
go

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);
GO

DBCC SHRINKFILE (N'MyDB', truncateonly);
GO

-- 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!

Kevin3NF

Follow @Dallas_DBAs