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!
Database limit is not applicable for master db.
So, if you deploy all objects to master, 10GB limit will not bother the lawyer anymore.
Technically yes, but that is a Terribad* idea
* stolen from Brent