locked
how to clean up unwanted backup table in sql server RRS feed

  • Question

  • Hi Everyone ,

    Let me please explain  if  i going to drop backup table  in my prod  database  post drop table there is any change to reduce the database size ?

    i have more the 1000 tables avail  in my db ..


    baskarlakshmi

    Wednesday, July 24, 2019 5:39 AM

Answers

  • Sorry, you want to drop a table? You can drop and then DBCC SHRINKDATABASE, but please do not do it as it causes to heavy fragmentation and hurts performance   

    I would suggest it  to leave it, as after deletion   you will see the backup database size is reduced


    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, July 24, 2019 6:49 AM
  • Hi baskarlakshmi,

    >>  if  i going to drop backup table  in my prod  database  post drop table there is any change to reduce the database size ?

    Yes, the database size will reducing.  To view the current amount of free (unallocated) space in the database, run sp_spaceused.

    By the way, if you want to use DBCC SHRINKDATABASE. You need consider the following information when you plan to shrink a database:

    • A shrink operation is most effective after an operation. This operation creates unused space, such as a truncate table or a drop table operation.
    • Most databases require some free space to be available for regular day-to-day operations. You might shrink a database repeatedly and notice that the database size grows again. This growth indicates that the shrunken space is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
    • A shrink operation doesn't preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This result is another reason not to repeatedly shrink the database.
    • Unless you have a specific requirement, don't set the AUTO_SHRINK database option to ON.

    Please refer to DBCC SHRINKDATABASE (Transact-SQL).

    Best regards,
    Cathy Ji

    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, July 25, 2019 9:12 AM

All replies

  • Sorry, you want to drop a table? You can drop and then DBCC SHRINKDATABASE, but please do not do it as it causes to heavy fragmentation and hurts performance   

    I would suggest it  to leave it, as after deletion   you will see the backup database size is reduced


    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, July 24, 2019 6:49 AM
  • Hi,

    Please go through below forum discussion, hope! it will help you you're looking answer.

    https://social.msdn.microsoft.com/Forums/SECURITY/en-US/a0e8dfdc-2a92-4352-b5ca-7f52f7511299/tables-are-deleted-but-database-size-does-not-change-in-sql-server-2008r2?forum=sqlsetupandupgrade


    Regards, Kiron

    Wednesday, July 24, 2019 7:02 AM
  • Hi baskarlakshmi,

    >>  if  i going to drop backup table  in my prod  database  post drop table there is any change to reduce the database size ?

    Yes, the database size will reducing.  To view the current amount of free (unallocated) space in the database, run sp_spaceused.

    By the way, if you want to use DBCC SHRINKDATABASE. You need consider the following information when you plan to shrink a database:

    • A shrink operation is most effective after an operation. This operation creates unused space, such as a truncate table or a drop table operation.
    • Most databases require some free space to be available for regular day-to-day operations. You might shrink a database repeatedly and notice that the database size grows again. This growth indicates that the shrunken space is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
    • A shrink operation doesn't preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This result is another reason not to repeatedly shrink the database.
    • Unless you have a specific requirement, don't set the AUTO_SHRINK database option to ON.

    Please refer to DBCC SHRINKDATABASE (Transact-SQL).

    Best regards,
    Cathy Ji

    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, July 25, 2019 9:12 AM
  • @Cathy,

    Thanks for your information


    baskarlakshmi

    Thursday, July 25, 2019 9:24 AM
  • @Cathy,

    Thanks for your information


    baskarlakshmi

    Hi baskarlakshmi ,

    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,
    Cathy Ji


    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, July 25, 2019 10:16 AM