none
Shrink Database

    Question

  • Hi,

    I have deleted large log table which contain more than 30 Gb. 30 percent of overall database size.

    Now i need to free these space from the database. how to shrink the database free size safely without losing any data.  If i go with shrink data file with free space it didn't work. 

    Can you give some suggestion. I am using SQL server 2008 R2

    Thanks


    Jaffer

    Wednesday, February 7, 2018 7:49 AM

Answers

  • Hello,

    a shrink don't change / removes data, it only reorganize data pages and removes optional empty datapages at the end of the database file.

    What exactly do you mean with "don't work"? Which exact command are you using?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, February 7, 2018 8:02 AM
    Moderator
  • Shrink is safe, but it can take a very very very very long time. And it has many other disadvantages. So, unless you are desperate to free up some GB on the server, just leave it as is. If you still want to shrink, try to do it a few GBs at a time. And, be patient. And, make sure you read below first:

    http://karaszi.com/why-you-want-to-be-restrictive-with-shrink-of-database-files


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Jafferpg Wednesday, February 14, 2018 1:48 PM
    Wednesday, February 7, 2018 9:27 AM
    Moderator
  • The only time shrinking of data file makes some sense is case like yours. if you have deleted large chunk of data and need the space ASAP you have to shrink and there is option in SSMS while shrinking which says " Release unused space check box"  This Shrink the datafile by taking free space from end of file but do not do page movement. With tsql dbcc shrinkfile you have truncateonly option which takes free space from file without doing page movement.

    TRUNCATEONLY
    Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_size is ignored if specified with TRUNCATEONLY.
    The TRUNCATEONLY option does not move information in the log, but does remove inactive VLFs from the end of the log file. This option is not supported for FILESTREAM filegroup containers.


    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


    Wednesday, February 7, 2018 9:48 AM
    Moderator
  • As mentioned above, shrink can't help you more to gain free space to your database.

    However, if you still wish to apply them, you can try Lepide's SQL server manager which is available free and helps to effectively track, manage and plan disk space utilisation across your SQL servers.


    • Marked as answer by Jafferpg Wednesday, February 14, 2018 1:48 PM
    • Edited by Shanky_621MVP, Moderator Wednesday, February 14, 2018 3:27 PM removed foreign link
    Thursday, February 8, 2018 8:10 AM

All replies

  • Hello,

    a shrink don't change / removes data, it only reorganize data pages and removes optional empty datapages at the end of the database file.

    What exactly do you mean with "don't work"? Which exact command are you using?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, February 7, 2018 8:02 AM
    Moderator
  • SHRINK creates fragmentation, I would rebuild indexes .

    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

    Wednesday, February 7, 2018 8:17 AM
    Answerer
  • Shrink is safe, but it can take a very very very very long time. And it has many other disadvantages. So, unless you are desperate to free up some GB on the server, just leave it as is. If you still want to shrink, try to do it a few GBs at a time. And, be patient. And, make sure you read below first:

    http://karaszi.com/why-you-want-to-be-restrictive-with-shrink-of-database-files


    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by Jafferpg Wednesday, February 14, 2018 1:48 PM
    Wednesday, February 7, 2018 9:27 AM
    Moderator
  • The only time shrinking of data file makes some sense is case like yours. if you have deleted large chunk of data and need the space ASAP you have to shrink and there is option in SSMS while shrinking which says " Release unused space check box"  This Shrink the datafile by taking free space from end of file but do not do page movement. With tsql dbcc shrinkfile you have truncateonly option which takes free space from file without doing page movement.

    TRUNCATEONLY
    Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. target_size is ignored if specified with TRUNCATEONLY.
    The TRUNCATEONLY option does not move information in the log, but does remove inactive VLFs from the end of the log file. This option is not supported for FILESTREAM filegroup containers.


    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


    Wednesday, February 7, 2018 9:48 AM
    Moderator
  • As mentioned above, shrink can't help you more to gain free space to your database.

    However, if you still wish to apply them, you can try Lepide's SQL server manager which is available free and helps to effectively track, manage and plan disk space utilisation across your SQL servers.


    • Marked as answer by Jafferpg Wednesday, February 14, 2018 1:48 PM
    • Edited by Shanky_621MVP, Moderator Wednesday, February 14, 2018 3:27 PM removed foreign link
    Thursday, February 8, 2018 8:10 AM
  • Thanks for your reply Mrs.

    Yes, As Mr.olaf and Mr.Tibork suggested, I decided to leave as it is. because it only affect the space of the server. 

    Once again thanks for your suggestions


    Jaffer

    Wednesday, February 14, 2018 1:51 PM