none
SQL Serever DB Size Issue RRS feed

  • Question

  • Hi All,

      We have SQL Server 2008R2 in our setup. In it a Database  is of  size 150GB. We have dropped tables on the big database and cleared around 130GB (Logical).  So our point is how can we reduce the DB Physical size apart from shrinking it.

     

        Can we take a backup and restoring it would reduce the size? Please suggest the options we have to physically reduce the DB File size.

    Regards,

    Varun

    Wednesday, December 30, 2015 5:57 AM

Answers

  • Hi All,

      We have SQL Server 2008R2 in our setup. In it a Database  is of  size 150GB. We have dropped tables on the big database and cleared around 130GB (Logical).  So our point is how can we reduce the DB Physical size apart from shrinking it.

        Can we take a backup and restoring it would reduce the size? Please suggest the options we have to physically reduce the DB File size.

    Backup and restore would not rduce size at all. AFAIK shrinking is only activity which would release space. Yes shrinking data file is not recommended but in extreme situations you have to.

    Shrink in small chunks


    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 Wiki Articles

    MVP

    Wednesday, December 30, 2015 7:08 AM
    Moderator

All replies

  • refer following

    is there any job running on database ?

    http://aboutsqlserver.com/2014/12/02/size-does-matter-10-ways-to-reduce-the-database-size-and-improve-performance-in-sql-server/


    Please click Mark As Answer if my post helped.

    Wednesday, December 30, 2015 6:13 AM
  • Not backup/restore do not 'solve' the problem

    Take a look into compression feature if you use EE 

     There is always the option of creating another table on another filegroup, copying data there and dropping original table and renaming the new table afterwards. This approach would work in either edition; however, in the most part of the cases it needs be done offline. Online implementation is, of course, possible but it is usually complicated if table has volatile data


    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

    • Proposed as answer by Ice Fan Wednesday, December 30, 2015 1:01 PM
    Wednesday, December 30, 2015 6:22 AM
    Answerer
  • Hi All,

      We have SQL Server 2008R2 in our setup. In it a Database  is of  size 150GB. We have dropped tables on the big database and cleared around 130GB (Logical).  So our point is how can we reduce the DB Physical size apart from shrinking it.

        Can we take a backup and restoring it would reduce the size? Please suggest the options we have to physically reduce the DB File size.

    Backup and restore would not rduce size at all. AFAIK shrinking is only activity which would release space. Yes shrinking data file is not recommended but in extreme situations you have to.

    Shrink in small chunks


    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 Wiki Articles

    MVP

    Wednesday, December 30, 2015 7:08 AM
    Moderator