locked
Shrink the SQL Databse RRS feed

  • Question

  • I'm using SQL 2008 Ent version. when i check it , it's Database size show that 12048.38 MB . Free available only 1121.24 MB .

    is it possible to shrink the Database ? else how can I Database size ? will be affect to Database behavior ?

    Please advise me.

     

     


    Regards, COMDINI
    • Moved by Stephanie Lv Monday, December 12, 2011 6:49 AM (From:Getting started with SQL Server)
    Sunday, December 11, 2011 7:32 AM

Answers

  • Usually people backup log file and then in order to reduce physical size of the log issue DBCC SHRINKFILE command, but do not do on period of time

    Must reading http://www.karaszi.com/SQLServer/info_dont_shrink.asp


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Iric Wen Monday, December 26, 2011 8:47 AM
    • Marked as answer by Peja Tao Tuesday, December 27, 2011 8:30 AM
    Sunday, December 11, 2011 8:13 AM
    Answerer
  • Here the DB size is 12048 MB. what is the size of the transaction log, what is the size of the mdf file?

    If the transaction log is big, I would recommend you use dbcc shrinkfile command, or shrink log file using SSMS to the desired size and then configure a maintenance plan to take periodic transaction log backups do that the size is always under check. This would help the tlog file not to autogrow. The shrink should be a one time activity.

    If the mdf file size is big, i dont think you need to be worried, as it would be data and I assume it would be critical to hold data, atleast thats the primary function of any database.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com
    • Proposed as answer by Iric Wen Monday, December 26, 2011 8:47 AM
    • Marked as answer by Peja Tao Tuesday, December 27, 2011 8:30 AM
    Monday, December 12, 2011 11:59 AM
  • Since you are using Enterprise Edition, you have the option to compress tables and indexes using the DATA_COMPRESSION = ROW or PAGE.  This can reduce data file space requirements considerably, depending on the nature of the data.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Peja Tao Tuesday, December 27, 2011 8:30 AM
    Monday, December 12, 2011 12:25 PM

All replies

  • I'm using SQL 2008 Ent version. when i check it , it's Database size show that 12048.38 MB . Free available only 1121.24 MB .

    is it possible to shrink the Database ? else how can I Database size ? will be affect to Database behavior ?

    Please advise me.

     

     


    Regards, COMDINI
    • Merged by Stephanie Lv Monday, December 12, 2011 6:50 AM duplicated thread
    Sunday, December 11, 2011 7:32 AM
  • Usually people backup log file and then in order to reduce physical size of the log issue DBCC SHRINKFILE command, but do not do on period of time

    Must reading http://www.karaszi.com/SQLServer/info_dont_shrink.asp


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Iric Wen Monday, December 26, 2011 8:47 AM
    • Marked as answer by Peja Tao Tuesday, December 27, 2011 8:30 AM
    Sunday, December 11, 2011 8:13 AM
    Answerer
  • What is your reason for wanting to shrink the database? Database shrink should be avoided if possible since it can cause heavy index fragmentation etc. You should instead monitor the free space and used space growth over time and manage the database size as required based on the growth requirements, ideally you pre-set the size so that there is sufficient space available for data to grow into, and regularly maintain that free space, that will avoid costly auto-growths which can slow down inserts etc.

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    Monday, December 12, 2011 3:46 AM
  • Here the DB size is 12048 MB. what is the size of the transaction log, what is the size of the mdf file?

    If the transaction log is big, I would recommend you use dbcc shrinkfile command, or shrink log file using SSMS to the desired size and then configure a maintenance plan to take periodic transaction log backups do that the size is always under check. This would help the tlog file not to autogrow. The shrink should be a one time activity.

    If the mdf file size is big, i dont think you need to be worried, as it would be data and I assume it would be critical to hold data, atleast thats the primary function of any database.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com
    • Proposed as answer by Iric Wen Monday, December 26, 2011 8:47 AM
    • Marked as answer by Peja Tao Tuesday, December 27, 2011 8:30 AM
    Monday, December 12, 2011 11:59 AM
  • Since you are using Enterprise Edition, you have the option to compress tables and indexes using the DATA_COMPRESSION = ROW or PAGE.  This can reduce data file space requirements considerably, depending on the nature of the data.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Peja Tao Tuesday, December 27, 2011 8:30 AM
    Monday, December 12, 2011 12:25 PM