Shrink File on database to release the free space back to a drive

  • I work in an IAAS and we have a situation where we are trying to by time before we have to have our client upgrade to another tier of IAAS. I have very little experience shrinking either a database or a file for the obvious reasons(i.e the database will just autogrow again which is costly, fragmentation) and have only done so in test environments. I've tried two different shrink file commands and neither one is releasing the free space back to the drive. Essentially I'm trying to release the space back to the drive so that other database files can continue to AutoGrow if needed. Below is my statements that I have tried:

     

    USE [Database]

    GO

    DBCC SHRINKFILE (N'Database_File_Name' , 328650) --target_size is the actual size in mega bytes you want

    GO

     

    USE [Database]

    GO

    DBCC SHRINKFILE (N'Database_File_Name' , 328650,Truncateonly)

    GO

     

    USE [Database]

    GO

    DBCC SHRINKFILE (N'Database_File_Name' , 0,Truncateonly)

    GO

     

     

  • Have you verified you actually have free space in the database?

    Have you factored in the space required to rebuild the biggest index?  You’ll need at least double the space.

    When it’s running that sort of waits / issues do you see.

  • DBCC SHRINKFILE doesn't use the database files physical name - it uses either the file_id or the logical name.  Query sys.database_files to get the appropriate file_id or name.

    See: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I use the GUI to generate a script, then I can modify and save it as a record of what was done.

  • How many megabytes does the database currently have, how many megabytes to you want it to have, how much FREE SPACE is currently available in the data base, and how much UNUSED space is currently in the database.

    And, yes, there's a huge difference between FREE SPACE and UNUSED space.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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