Unable to shrink datafile

  • RSingh-135921

    Ten Centuries

    Points: 1378

    Hi,

    We have a 500 GB database.

    We deleted 75% of old data and then rebuilt most of the indexes.

    As a result of all this we had enough free space in the datafiles.

    To reclaim most of the space I was trying to shrink the datafiles (15 in all).

    Although most of them shrunk successfully, one of them has failed with this error:

    Could not adjust the space allocation for file 'PCRDATA14'

    This file is 26.6 GB in size. 7.8 GB is the used space and it had 18.8 GB free space. I was shrinking it with this command:

    DBCC SHRINKFILE(PCRDATA14,15000)

    GO

    Any idea why the file is not shrinking? and how can I shrink it?

    Regards

  • SQLQuest29

    SSCrazy Eights

    Points: 8203

    RSingh (1/31/2009)


    Hi,

    We have a 500 GB database.

    We deleted 75% of old data and then rebuilt most of the indexes.

    As a result of all this we had enough free space in the datafiles.

    To reclaim most of the space I was trying to shrink the datafiles (15 in all).

    Although most of them shrunk successfully, one of them has failed with this error:

    Could not adjust the space allocation for file 'PCRDATA14'

    This file is 26.6 GB in size. 7.8 GB is the used space and it had 18.8 GB free space. I was shrinking it with this command:

    DBCC SHRINKFILE(PCRDATA14,15000)

    GO

    Any idea why the file is not shrinking? and how can I shrink it?

    Regards

    Hi,

    what version of sql u r using ? And do post the error message u r getting.

    do check the following link:

    http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.server/2004-03/3009.html

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • Jason Crider

    SSCertifiable

    Points: 6019

    See if this will help you at all. I've had good luck with it.

    -- Shrink_DB_File.sql

    /*

    This script is used to shrink a database file in

    increments until it reaches a target free space limit.

    Run this script in the database with the file to be shrunk.

    1. Set @DBFileName to the name of database file to shrink.

    2. Set @TargetFreeMB to the desired file free space in MB after shrink.

    3. Set @ShrinkIncrementMB to the increment to shrink file by in MB

    4. Run the script

    */

    declare @DBFileName sysname

    declare @TargetFreeMB int

    declare @ShrinkIncrementMB int

    -- Set Name of Database file to shrink

    set @DBFileName = 'nameOfDatabaseFileGoesHere'

    -- Set Desired file free space in MB after shrink

    set @TargetFreeMB = 1000

    -- Set Increment to shrink file by in MB

    set @ShrinkIncrementMB = 500

    -- Show Size, Space Used, Unused Space, and Name of all database files

    select

    [FileSizeMB] =

    convert(numeric(10,2),round(a.size/128.,2)),

    [UsedSpaceMB] =

    convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,

    [UnusedSpaceMB] =

    convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,

    [DBFileName] = a.name

    from

    sysfiles a

    declare @sql varchar(8000)

    declare @SizeMB int

    declare @UsedMB int

    -- Get current file size in MB

    select @SizeMB = size/128. from sysfiles where name = @DBFileName

    -- Get current space used in MB

    select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.

    select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

    -- Loop until file at desired size

    while @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB

    begin

    set @sql =

    'dbcc shrinkfile ( '+@DBFileName+', '+

    convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '

    print 'Start ' + @sql

    print 'at '+convert(varchar(30),getdate(),121)

    exec ( @sql )

    print 'Done ' + @sql

    print 'at '+convert(varchar(30),getdate(),121)

    -- Get current file size in MB

    select @SizeMB = size/128. from sysfiles where name = @DBFileName

    -- Get current space used in MB

    select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.

    select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName

    end

    select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

    -- Show Size, Space Used, Unused Space, and Name of all database files

    select

    [FileSizeMB] =

    convert(numeric(10,2),round(a.size/128.,2)),

    [UsedSpaceMB] =

    convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,

    [UnusedSpaceMB] =

    convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,

    [DBFileName] = a.name

    from

    sysfiles a

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    Shrink DB File by Increment to Target Free Space

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

  • RSingh-135921

    Ten Centuries

    Points: 1378

    Thanks friends, I will try the script.

    It needs to be done over the weekend.

    Regards

  • Jason Crider

    SSCertifiable

    Points: 6019

    I couldn't find the link or I would have included it. Thanks Michael.

  • sqldba4u

    Hall of Fame

    Points: 3414

    Tried executing the script but hard luck.

  • anurag.shekhar

    Mr or Mrs. 500

    Points: 578

    You are trying to release a huge amount of space at once. Try releasing it in smaller chunks. You can use the given script above. If it is not working try increasing the size of your datafile by few MBs and then use the script. It should work

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply