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:
- Open Sql Server Management Studio
- Run sp_helpdb ‘YourDb’ and save the results
- 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 - Change in the script the final query :
- from:
SELECT *
FROM #TempTable - To :
SELECT *
FROM #TempTable
order by cast(replace(IndexSize,' KB','') as int) desc - Run the query and identify the worst tables
- Open the Tables, Index and click on “Reorganize all”
- Shrink the DB
- Run sp_helpdb ‘YourDb’ and compare the results with the old one
- Install the new SQL server and try to migrate the databases, but this is another story…..
Hope it helps!
Posts
Couple days ago I was at the I-net and perceived there an unusual tool, which to my great surprise aided me quite easy. But it moreover the application showed me some interesting features - how to recover sql database.
ReplyDeleteHello Matteo,
ReplyDeleteYou can also use third party SQL Database recovery tools to handle database corruption errors. It does not matter that CHECKDB DBCC detect the error or not, the software works with their own strategies to handle database corruption & provides recovered database files(mdf & ndf).