Reclaim Space in Community Server
Over Christmas, I ran into a bad problem with my Community Server installation on GoDaddy. My database was full so I couldn't make any more posts. I emailed GoDaddy and they kindly increased my database size for no cost.
Make a backup NOW. I take no responsibility for any loss of data.
It's happening again and I'm surprised at how much space this is using so I decided to unleash my SQL ninja skills. To start with, you can see the size of your database files by running:
SELECT * FROM sysfiles;
Sure enough, I was near the max, but how do I know what is causing the problem? That's where sp_spaceused comes in. Run that without any params or with a single table name. I went straight to the cs_exceptions table.
EXEC sp_spaceused 'cs_exceptions'
Any error on the site gets logged to that table. With all the spammers and programs trolling the internet, there are thousands of exceptions in this table. For me, the table was taking up half of my allotted 500MB! I didn't care about the data in this table so I tried to delete every exception that only happened a couple times.
delete from cs_exceptions where frequency < 5
That failed with a message saying "The log file for database 'DB_16520' is full. Back up the transaction log for the database to free up some log space." So I tried to shrink the log file.
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MyDatabase_Log, 2)
The log file did shrink, but I still wasn't able to run the above delete command. For some reason, I could only delete about 15,000 rows at a time (by filtering on exceptionid.) So after a few commands, I had finally deleted all the rows from the table.
Oddly enough it still looks like that exceptions table is taking up a good amount of space, but I have freed up 100MB so I'm happy for now. I'm running another backup at this point since my website seems to be in good order. Once that completes then I'll take a look at reclaiming the rest of the space in that table. I'll either try the shrinkfile command on the data file or drop and recreate the exceptions table. I don't think there are any foreign keys so it shouldn't be too hard.