none
SQL 2008 - Shrink Database Not working - how to resolve?

    Question

  • Hello,

    I have a test SQL08 database that I am testing out shrinking.

    The database is set to full recovery model and auto-shrink=false.  The data and log file are set to auto-grow.  I have 2 test tables and that is all.  There are no indexes etc defined on these tables.

    I insert a few hundred thousand records into the 2 tables and as expected the database grows in size from a few MB along with the log file to many GB.  I delete many thousands of records and then run:

    DBCC SHRINKDATABASE (MyDatabase, 10)

    After the operation is complete (which doesnt seem to take that long) I observe that the MDF file is the same size on the hard disk as it was before the shrink operation e.g. around 10.3GB.

    Therefore, I would like to know, are there any other commands I need to execute before or after the SHRINKDATABASE to release the free space back to the Operating system ASAP ?  The shrink, doesnt shrink anything by the looks of it.  I have also tried truncating ALL data from the tables and then running the shrink and get the same result!

    I wanted to set up this process in an automated fashion on some of my customers servers to keep a lid on disk space usage within a reusable script - the goal would be to use a different database name as the input.

    Any suggestions are appreciated.

    Thanks,

    Ross
    Database and Application Developer
    Sunday, August 09, 2009 10:08 PM

Answers

All replies

  • I suggest you use DBCC SHRINKFILE instead of SHRINKDATABASE. Another reason you might not see a reduction is because deletion of data in SQL Server is a deferred operation, done after the fact, in the background.

    I do not recommend you schedule shrink operations, unless you really really need to, and are prepared to pay the penalties. Check out:

    http://www.karaszi.com/SQLServer/info_dont_shrink.asp
    http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Sunday, August 09, 2009 10:53 PM
  • Because your database recovery mode is Full Recovery, you need to backup the log before you can shrink it with dbcc shrinkdatabase.
    Monday, August 10, 2009 1:00 AM
  • Hi

    This is Mark, Microsoft SQL Support Engineer. I'm glad to assist you with the problem.

    in order to help you to resolve the issue, I would like to explain the following
    1 before shrink the database, please backup the log file first.

    2 to verify if the shrink database is successful, we need to run the following command to check the free space of the database file.

    exec

    sp_spaceused

    3 to give back the disk space back to OS, we could just manually set the size of the mdf after shrink the database.

    4 to narrow down the issue, please run DBCC SHRINKFILE to reduce the size of the mdf file.

    if you have any questions on the above, please let me know. Thanks

    Regards
    Mark Han
     

    Tuesday, August 11, 2009 8:49 AM
  • I also found that setting the recovery mode to Simple, then carrying out ShrinkDatabase, then returning back to Full recovery achieved the same thing.

    I presume that the removal of the BACKUP LOG with TRUNCATE_ONLY in 2008 is to prevent the quick and dirty approach being carried out to backup the log file?

    Thanks.
    Database and Application Developer
    Tuesday, August 11, 2009 12:04 PM
  • I also found that setting the recovery mode to Simple, then carrying out ShrinkDatabase, then returning back to Full recovery achieved the same thing.

    I presume that the removal of the BACKUP LOG with TRUNCATE_ONLY in 2008 is to prevent the quick and dirty approach being carried out to backup the log file?

    Thanks.
    Database and Application Developer

    Correct. Setting the db to simple (then checkpoint, shrink and back to full) will probably make it clearer to you that you are doing an operation that actuallt affect your restore options.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, August 11, 2009 4:41 PM
  • Hi MrPeds

    in order to make the issu clear for us, I would like to verify the following
    1) please run the following script and post it result.
       exec sp_spaceused

       sp_helpdb yy

       SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
       size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *
       FROM sys.database_files;

    2) how about the size of the log file.

    3) please use DBCC SHRINKFILE to reduce the size of the mdf file to see the result.

    4) please try to shrink the log file of the database by dbcc shrinkdatabase command to see if that will be successful

    Besides, to set the recovery model to simple will prevent the log file of the database from growing.

    if you have any questions on the above, please let me know.

    Thanks
       

     

     

    Wednesday, August 12, 2009 5:50 AM
  • I think i've got enough to be getting on with now - the database is reducing in size so that is the main thing.

    thanks for you input.

    MrPeds
    Database and Application Developer
    Wednesday, August 12, 2009 7:54 AM
  • Hi MrPeds

    Thank you for the update.

    if you have any questions related the issue, please post them here.

    Have a nice day

    Regards
    Mark Han
    Thursday, August 13, 2009 2:17 AM