Sql-server – Shrinking a SQL Server 2008 Standard database to move it into SQL Server 2014 Express

shrinksql serversql server 2014sql-server-2008sql-server-express

I need to shrink a popular CRM database with SSMS to move it onto a new server. I am going from SQL Server 2008 Standard to SQL Server 2014 Express. Database is in Simple Recovery Model. It needs to be under the 10 gig limit.

There's 39% space to erase but shrink does not work. I've done this many times including this database after removing 100000's of rows in different tables but it does not want to shrink this time.

No errors just runs and does nothing.

Best Answer

Normally the thing holding my data files back from being shrunk is that a CHECKPOINT is needed. I have had to do several iterations of:

CHECKPOINT;
GO
DBCC SHRINKFILE(...)
GO

Before the file would finally shrink.

If the data files still doesn't shrink, try the troublshooting steps here to confirm that you really have the free space that you think you have. Here's the query from the docs link:

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
Related Question