msdb data file shrinkfile - Large Database

  • Hi ,
    The msdb data file on a prod db server  is 6 GB, Though I truncated the large tables  when I try to shrink the data file I get the below error:

    Any idea?

    File ID of database 4 cannot be shrunk as it is either being shrunk by another process or is empty!
    Thanks

  • sqlguy80 - Sunday, September 10, 2017 6:44 PM

    Hi ,
    The msdb data file on a prod db server  is 6 GB, Though I truncated the large tables  when I try to shrink the data file I get the below error:

    Any idea?

    File ID of database 4 cannot be shrunk as it is either being shrunk by another process or is empty!
    Thanks

    It sounds to me like you started a shrink somewhere along the line or someone set it to "auto-shrink" (which should NEVER be done).

    --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)

  • Hi Jeff,
    The initial size of msdb data file is 6 GB  and the tables are very small.

    No matter what I do , to shrink the initial size doesnt become  less in size..1 GB  or so?

    Any idea.

    Thanks Again

  • What command(s) are you using to shrink the files?  If you're using the GUI, what options are you setting?

    --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)

  • dbcc shrinkfile (1,1024)
    goal is to bring the data file back to 1 GB from the init size 6 GB.

  • Dear sqlguy80 ,
    Try to increase the size of the datafile by few MB.
    then run the shrink command.

  • Imran,

    Thank you so much, That worked! Your great.

    Any logic behind that?   I couldnt find this anywhere in google, most folks said recreate msdb, restore etc! but this worked like a charm.

  • sqlguy80 - Monday, September 11, 2017 7:12 AM

    Imran,

    Thank you so much, That worked! Your great.

    Any logic behind that?   I couldnt find this anywhere in google, most folks said recreate msdb, restore etc! but this worked like a charm.

    The next thing is, you have to at least REORGANIZE your indexes because SHRINKFILE doesn't just horribly fragment your database (which usually doesn't matter) but it does some other weird stuff behind the scenes that may cause some rather nasty performance issues.

    --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)

  • Hi Jeff,

    Yes I  rebuilt all indexes and updated all stats hoping that would help with this shrink file operation.

    Thanks

  • sqlguy80 - Monday, September 11, 2017 9:57 AM

    Hi Jeff,

    Yes I  rebuilt all indexes and updated all stats hoping that would help with this shrink file operation.

    Thanks

    It's not supposed to help with the shrink operation.  It's supposed to clean up the mess afterwards.  So just to be sure, you did the defrags AFTER the shrink, correct?

    --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)

  • yes sir. I did it again to be safe.

  • Imran-fast - Monday, September 11, 2017 1:53 AM

    Dear sqlguy80 ,
    Try to increase the size of the datafile by few MB.
    then run the shrink command.

    Good Solution. How increasing the size of data file fixed the issue? Means shink operation would need some available free space for its operation?

    Regards
    VG

Viewing 12 posts - 1 through 11 (of 11 total)

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