Shrinking a SQL Server 2008 Standard database to move it into SQL Server 2014 Express

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP












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.










share|improve this question























  • Kendra touched on this with tempdb, not sure it'd be an issue for you but wouldn't hurt.
    – scsimon
    Dec 18 at 20:13










  • @scimon I suppose you meant Tara, not Kendra!
    – yper-crazyhat-cubeᵀᴹ
    Dec 18 at 21:01










  • @ypercubeᵀᴹ ah you're right!
    – scsimon
    Dec 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 at 21:45















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.










share|improve this question























  • Kendra touched on this with tempdb, not sure it'd be an issue for you but wouldn't hurt.
    – scsimon
    Dec 18 at 20:13










  • @scimon I suppose you meant Tara, not Kendra!
    – yper-crazyhat-cubeᵀᴹ
    Dec 18 at 21:01










  • @ypercubeᵀᴹ ah you're right!
    – scsimon
    Dec 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 at 21:45













1












1








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.










share|improve this question















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.







sql-server sql-server-2008 sql-server-2014 shrink sql-server-express






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 18 at 18:48









MDCCL

6,68731744




6,68731744










asked Dec 18 at 18:04









Daniel Saintonge

61




61











  • Kendra touched on this with tempdb, not sure it'd be an issue for you but wouldn't hurt.
    – scsimon
    Dec 18 at 20:13










  • @scimon I suppose you meant Tara, not Kendra!
    – yper-crazyhat-cubeᵀᴹ
    Dec 18 at 21:01










  • @ypercubeᵀᴹ ah you're right!
    – scsimon
    Dec 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 at 21:45
















  • Kendra touched on this with tempdb, not sure it'd be an issue for you but wouldn't hurt.
    – scsimon
    Dec 18 at 20:13










  • @scimon I suppose you meant Tara, not Kendra!
    – yper-crazyhat-cubeᵀᴹ
    Dec 18 at 21:01










  • @ypercubeᵀᴹ ah you're right!
    – scsimon
    Dec 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 at 21:45















Kendra touched on this with tempdb, not sure it'd be an issue for you but wouldn't hurt.
– scsimon
Dec 18 at 20:13




Kendra touched on this with tempdb, not sure it'd be an issue for you but wouldn't hurt.
– scsimon
Dec 18 at 20:13












@scimon I suppose you meant Tara, not Kendra!
– yper-crazyhat-cubeᵀᴹ
Dec 18 at 21:01




@scimon I suppose you meant Tara, not Kendra!
– yper-crazyhat-cubeᵀᴹ
Dec 18 at 21:01












@ypercubeᵀᴹ ah you're right!
– scsimon
Dec 18 at 21:51




@ypercubeᵀᴹ ah you're right!
– scsimon
Dec 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 at 21:45




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 at 21:45










2 Answers
2






active

oldest

votes


















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;





share|improve this answer






























    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.






    share|improve this answer




















      Your Answer








      StackExchange.ready(function()
      var channelOptions =
      tags: "".split(" "),
      id: "182"
      ;
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function()
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled)
      StackExchange.using("snippets", function()
      createEditor();
      );

      else
      createEditor();

      );

      function createEditor()
      StackExchange.prepareEditor(
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: false,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: null,
      bindNavPrevention: true,
      postfix: "",
      imageUploader:
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      ,
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      );



      );













      draft saved

      draft discarded


















      StackExchange.ready(
      function ()
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f225302%2fshrinking-a-sql-server-2008-standard-database-to-move-it-into-sql-server-2014-ex%23new-answer', 'question_page');

      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      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;





      share|improve this answer



























        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;





        share|improve this answer

























          5












          5








          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;





          share|improve this 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;






          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Dec 18 at 18:21

























          answered Dec 18 at 18:14









          jadarnel27

          3,5551330




          3,5551330























              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.






              share|improve this answer

























                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.






                share|improve this answer























                  0












                  0








                  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.






                  share|improve this answer












                  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.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Dec 18 at 22:45









                  dbamex

                  716




                  716



























                      draft saved

                      draft discarded
















































                      Thanks for contributing an answer to Database Administrators Stack Exchange!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid


                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.

                      To learn more, see our tips on writing great answers.





                      Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                      Please pay close attention to the following guidance:


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid


                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.

                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function ()
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f225302%2fshrinking-a-sql-server-2008-standard-database-to-move-it-into-sql-server-2014-ex%23new-answer', 'question_page');

                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown






                      Popular posts from this blog

                      How to check contact read email or not when send email to Individual?

                      Displaying single band from multi-band raster using QGIS

                      How many registers does an x86_64 CPU actually have?