Defragment a database - how?
Posted on 2014-03-05
The backbone of my database (SQL 2008 Express) is a table that stores photographs in a filestream column. This table accounts for practically all the data in the db, the other tables containing just a few hundred rows of reference data. I created this table and its primary key on a filestream filegroup.
At the outset, I set the database up with a primary filegroup, a secondary one (default for user data) and a filestream filegroup. For a couple of months now I've been repeatedly deleting and repopulating this main table. Along the way I've encountered quite a bit of unreliability with the filestream technology - sometimes when repopulating the table the db would randomly ignore the filestream group and start saving the data to the secondary filegroup. When this happened the db ran out of space and the process stalled. In this event my solution has always been to drop and recreate the table.
Now my secondary file is over 4GB, its log file is 1.4GB, and I'm out of space unable to run anything.
How do I clean this up?