1

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.

  • Kendra touched on this with tempdb, not sure it'd be an issue for you but wouldn't hurt. – scsimon Dec 18 '18 at 20:13
  • @scimon I suppose you meant Tara, not Kendra! – ypercubeᵀᴹ Dec 18 '18 at 21:01
  • @ypercubeᵀᴹ ah you're right! – scsimon Dec 18 '18 at 21:51
  • You don’t need to concern yourself with shrinking if you export/import the data. This is also a good way to rebuild/pack all tables and indexes. (However if the data fits In the 10gb limit it might quickly outgrow it if you start mutating sich densely packed tables) – eckes Dec 20 '18 at 21:45
5

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;
0

Try executing the shrink in 100 MBs ,

I don't have my code at hand but create a cursor which will go over, the file many times reducing the total size everytime by 100 MBs. That should work and allow you to stop it and don't loose any job.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.