locked
shrinking question RRS feed

  • Question

  • Hi experts,

    I never ever shrink a database... But in this case, I have deleted two tables that are not longer going to be used, and both tables were quite big.

    I tried first option, but is not shrinking anything, why? Shall I go ahead and do the 'reorganize pages before releaseing unsued space, and force the srhink to a value'? which value? :)

    Monday, July 30, 2018 11:17 AM

Answers

  • It may not be shrinking because the free space might not be at the end of file. So to shrink you might have to choose 2nd option "reorganize pages before releasing spaces". This process can bring massive fragmentation. Make sure the value you give is small and you might have to shrink in small chunks

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, July 30, 2018 11:24 AM
  • Use a T-SQL script instead of UI: DBCC SHRINKFILE (Transact-SQL)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Monday, July 30, 2018 12:03 PM

All replies

  • It may not be shrinking because the free space might not be at the end of file. So to shrink you might have to choose 2nd option "reorganize pages before releasing spaces". This process can bring massive fragmentation. Make sure the value you give is small and you might have to shrink in small chunks

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, July 30, 2018 11:24 AM
  • Hi, I chose the 2nd option and I  got the below error:

    what can I do to claim back the space?

    Monday, July 30, 2018 11:35 AM
  • Use a T-SQL script instead of UI: DBCC SHRINKFILE (Transact-SQL)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Monday, July 30, 2018 12:03 PM
  • Try 

    DBCC SHRINKFILE (1, TRUNCATEONLY);


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, July 30, 2018 12:29 PM
    Answerer
  • Stop using the UI, and use T-SQL commands to at least get rid of that filter. Best is to use DBCC SHRINKFILE with a target size.

    Beware that if there are LOB columns in the database, shrinking can take very long time. And I mean very long time.

    In this particular case, something seems to crash though, which is not good news. But let's wait until you have run a T-SQL command, so we are able to see what you did.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, July 30, 2018 9:09 PM