locked
Shrinking Database question RRS feed

  • Question

  • Hi All,

    Very new to SQL databases.

    So I am looking at the tasks-shrink-files.

    Says currently allocated space and the available free space.  The available free space, is that within the currently allocated space (as in the database is getting full) or if the shrink task is run will free up that amount.

    Hope this makes sense.

    TIA

    Tuesday, June 2, 2020 5:54 PM

Answers

  • Hi All,

    Very new to SQL databases.

    So I am looking at the tasks-shrink-files.

    Says currently allocated space and the available free space.  The available free space, is that within the currently allocated space (as in the database is getting full) or if the shrink task is run will free up that amount.

    Hope this makes sense.

    TIA

    Good day TIA

    The golden rule is that you should NOT shrink the files!

    There is a reason that the files got to this size and they will be growth again to this size (and more) probably ion the future for the same reasons. Growing of the files has a huge impact on performance.

    The official documentation which include answers to your questions is here:

    https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15

    But as said, this should not be used probably and you should read some more on why not. Brand Ozer have a nice post on the topic. Check this:https://www.brentozar.com/archive/2017/12/whats-bad-shrinking-databases-dbcc-shrinkdatabase/


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, June 2, 2020 8:03 PM
  • Hi friend,

    Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can be deallocated and returned to the file system.

    Shrink the Transaction Log file , all free VLFs will be deallocated from the end of the Transaction Log file and returned back to the operating system, in order to reduce the physical size of the SQL Transaction Log file. If you want to shrink the log file because it is growing continuously to a large size , it is best to find the cause of the log growth to solve the problem at its root, for example, log backup not be execute regularly, or there is a transaction that has not been committed for a long time.

    Usually it is not recommended to repeatedly shrink the database,the shrink operation will increases index fragmentation, and most databases require some free space to be available for regular day-to-day operations. Please refer to Shrink a Database--Recommendations.

    Best Regards,
    Cris

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 3, 2020 3:39 AM

All replies

  • Hi All,

    Very new to SQL databases.

    So I am looking at the tasks-shrink-files.

    Says currently allocated space and the available free space.  The available free space, is that within the currently allocated space (as in the database is getting full) or if the shrink task is run will free up that amount.

    Hope this makes sense.

    TIA

    Good day TIA

    The golden rule is that you should NOT shrink the files!

    There is a reason that the files got to this size and they will be growth again to this size (and more) probably ion the future for the same reasons. Growing of the files has a huge impact on performance.

    The official documentation which include answers to your questions is here:

    https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15

    But as said, this should not be used probably and you should read some more on why not. Brand Ozer have a nice post on the topic. Check this:https://www.brentozar.com/archive/2017/12/whats-bad-shrinking-databases-dbcc-shrinkdatabase/


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, June 2, 2020 8:03 PM
  • Hi friend,

    Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can be deallocated and returned to the file system.

    Shrink the Transaction Log file , all free VLFs will be deallocated from the end of the Transaction Log file and returned back to the operating system, in order to reduce the physical size of the SQL Transaction Log file. If you want to shrink the log file because it is growing continuously to a large size , it is best to find the cause of the log growth to solve the problem at its root, for example, log backup not be execute regularly, or there is a transaction that has not been committed for a long time.

    Usually it is not recommended to repeatedly shrink the database,the shrink operation will increases index fragmentation, and most databases require some free space to be available for regular day-to-day operations. Please refer to Shrink a Database--Recommendations.

    Best Regards,
    Cris

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 3, 2020 3:39 AM
  • Read this article 

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


    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, June 3, 2020 4:20 AM
    Answerer
  • Shrinking is not advisable .

    but critical time you need take care make sure that there should no user access while shrinking mdf files.

    make sure that there is no open transaction..

    use database 
    dbcc opentran



    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Wednesday, June 3, 2020 5:07 AM
  • Good day TIA

    Ronen, TIA is not a name, it's abbreviation of Thanks in Advanced :-)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, June 3, 2020 6:26 AM
  • Good day TIA

    Ronen, TIA is not a name, it's abbreviation of Thanks in Advanced :-)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

     Thanks Olaf 😀

    My poor English is shown...
    Well at least I learned something new

    Sorry for the confusion with the name. As you can guess English is not my native language and I was not familiar with the meaning of TIA. I hate using acronyms... For each 3 letters that you select, you can probably find 10 different meaning 🤣


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    • Edited by pituachMVP Thursday, June 11, 2020 5:57 PM
    Wednesday, June 3, 2020 10:39 AM
  • Hi friend,

    Is there any update on this case? Has your question been answered

    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 4, 2020 1:59 AM