Link to home
Start Free TrialLog in
Avatar of TimHudspith
TimHudspith

asked on

Defragment a database - how?

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?
SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TimHudspith
TimHudspith

ASKER

Thanks for the suggestions but in the end I ran Ola Hallengren's SQL Server Maintenance Solution to clean up my indexes, then ran the shrinkdatabase command, which did the trick.

http://ola.hallengren.com/

DBCC SHRINKDATABASE