locked
Shrink Database RRS feed

  • Question

  • Hi All,

    I have an SQL 2000 Database which is 135GB and I want to perform "Shrink Database" but I want to know what is the maximum percentage that I can shrink it to please.


    Thank you

    Thursday, June 23, 2011 10:38 AM

Answers

  • BOL says

     

    This example decreases the size of the files in the UserDB user database to allow 10 percent free space in the files of UserDB.

    DBCC SHRINKDATABASE (UserDB, 10)
    GO
    
    Hmm, have not used DBCC SHRINKDATABASE for long time, I think you may specify a number that is less 100?Does it nmake sense?
    http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(930)-data-file-shrink-does-not-affect-performance.aspx

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by WeiLin Qiao Tuesday, July 5, 2011 1:33 AM
    Thursday, June 23, 2011 10:50 AM
    Answerer
  • use dbcc shrinkdatabase (DBname ,truncateonly)

    TRUNCATEONLY

    Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Marked as answer by WeiLin Qiao Tuesday, July 5, 2011 1:33 AM
    Thursday, June 23, 2011 4:19 PM

All replies

  • BOL says

     

    This example decreases the size of the files in the UserDB user database to allow 10 percent free space in the files of UserDB.

    DBCC SHRINKDATABASE (UserDB, 10)
    GO
    
    Hmm, have not used DBCC SHRINKDATABASE for long time, I think you may specify a number that is less 100?Does it nmake sense?
    http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(930)-data-file-shrink-does-not-affect-performance.aspx

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by WeiLin Qiao Tuesday, July 5, 2011 1:33 AM
    Thursday, June 23, 2011 10:50 AM
    Answerer
  • use dbcc shrinkdatabase (DBname ,truncateonly)

    TRUNCATEONLY

    Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Marked as answer by WeiLin Qiao Tuesday, July 5, 2011 1:33 AM
    Thursday, June 23, 2011 4:19 PM
  • What's the recovery model of the database?
    Harsh Chawla Personal Blog:-http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Thursday, June 23, 2011 4:37 PM