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!
