locked
Databse wont shrink RRS feed

  • Question

  • Hi,

    I have a 12gb SQL 2008 database. I have moved a table which was 4gb in size.
    Now I am trying to shrink the database to claim back the space but it doesnt shrink.

    Server Management tells me there are 4.5gb of free space in the database, but the shrink process is very short, and the size of the file stays the same after it.
    Any Ideas?

    Dror Yitzhakov.
    Sunday, August 16, 2009 12:18 PM

Answers

All replies

  • Hi Yitzhakov,

    Are you concerned by the recovery model FULL with your database ?

    Verify you can shrink your TLog File by this statement :

    SELECT log_reuse_wait_desc 
    FROM sys.databases WHERE name = '<databaseName>'

    ++
    MCDBA | MCITP SQL Server 2005 | LPI Linux 1
    Sunday, August 16, 2009 6:29 PM
  • Also post the output for DBCC opentran for that database.Try taking a transaction log backup and shrink again.
    Please note transaction log backup is not possible in simple recovery mode.
    Thanks, Leks
    • Edited by LekssEditor Sunday, August 16, 2009 9:12 PM Additional Inputs
    Sunday, August 16, 2009 9:11 PM
    Answerer
  • And... could you specify your statement you use to shrink the database?

    -----------------
    Starwind Softwaredeveloper
    Monday, August 17, 2009 1:02 AM

  • And... could you specify your statement you use to shrink the database?

    -----------------
    Starwind Softwaredeveloper
    Try this

    DBCC SHRINKDATABASE (YourDatabaseName, TRUNCATEONLY);
    
    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Monday, August 17, 2009 1:17 AM
  • You might have to wait for Ghost Cleanup to complete before you can free the space:

    http://serverfault.com/questions/23446/ghost-cleanup

    However, if that isn't the case, try using DBCC SHRINKFILE('filename', 10) to shrink the data file only.  By specifying a target size of 10, it will shrink to the smallest size possible based on the data in the datafile, reorganizing the pages internally to do so.  This will result in heavy index fragmentation and should be followed immediately by and full index rebuild for the database so that the indexes are logically defragmented.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, August 18, 2009 1:43 AM