Hot to recover unused space from SQL server free edition (MSDE,Express)

If you have a SQL server free edition and it isn’t working with the message:

CREATE/ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 2048 MB per database.

Unfortunately the ”dbcc cleantable “ does not help,  it cleans the table unused space BUT the shrink operation does  not decrease the database size

You have another option:  reorganize the indexes and shrink the db: it works giving you some hours of new life, just the time to install SQL2008 express R2 with 8GB limit

Follow this steps:

1. Open Sql Server Management Studio
2. Run sp_helpdb ‘YourDb’ and save the results
3. Analyze the tables with the bigger size, use this script:
   http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx
4. Change in the script the final query :
• from:
  SELECT *
  FROM #TempTable
• To :
  SELECT *
  FROM #TempTable
  order by cast(replace(IndexSize,' KB','') as int) desc
5. Run the query and identify the worst tables
6. Open the Tables, Index and click on “Reorganize all”
7. Shrink the DB
8. Run sp_helpdb ‘YourDb’ and compare the results with the old one
9. Install the new SQL server and try to migrate the databases, but this is another story…..
   

Hope it helps!