Post reply

How a shrinkfile works in my database.

  • November 21, 2011 at 11:48 am

    #390913

    Hi people,

    Thanks again for your time.

    I have a misconception about how SHRINKFILE works, i read the following links:

    http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-the-log-and-log-backups-how-to-convince-yourself.aspx

    http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-More-on-the-circular-nature-of-the-log.aspx

    But l'm still get confused about what i'm doing.

    My regular process to shrink a database base is based on the following list:

    1.- I ran this script:

    SELECT name, filename,

    CONVERT(Decimal(15,2),ROUND(a.size/128.000,2)) [Currently Allocated Space (MB)],

    CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],

    CONVERT(Decimal(15,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]

    FROM dbo.sysfiles a (NOLOCK)

    2.- I check the Available Space and as far i know that's what i can gain.

    3.- then after i ran:

    USE [MyDatabae]

    GO

    DBCC SHRINKFILE (N'MyDatabae' , Available Space).

    But please somebody who can clarify what does the SHRINKFILE and when i convenient to use it.

    Thanks.

  • November 21, 2011 at 2:16 pm

    #1411044

    In short, it moves the last page in the data file as early as possible, repeat until no more pages can be moved.

    When to use it - almost never. Only time you should really consider a shrink of a data file is when some large archive or data purge has removed a lot of data and the empty space won't be reused in a reasonable amount of time

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 21, 2011 at 3:11 pm

    #1411078

    Thanks sir 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

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