Post reply

mdf space balance

  • November 28, 2022 at 8:16 pm

    Answer

    #4119138

    Just to be sure, an index "rebuild" won't do a move.  It must be a CREATE INDEX that matches the currently existing index in all ways and it must have the WITH(DROP_EXISTING=ON) option and it must contain the ON filegroup option to move it to the file group of choice.

    Please take the time to lookup the DROP_EXISTING option in the CREATE INDEX documentation.

    https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql

     

     

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

  • November 28, 2022 at 8:18 pm

    #4119139

    Adding another post just to make the forum software actually see 2 pages of posts.

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

  • November 28, 2022 at 10:39 pm

    #4119197

    Bruin wrote:

    Just so I understand I just need to rebuild Index with:

    CREATE UNIQUE CLUSTERED INDEX PK__UserLog__7F8B815172CE9EAE ON UserLog (UserLogID) WITH (DROP_EXISTING = ON) ON HISTORY

    Thx.

    That's it.  No need to drop any constraints, etc.  If you're in the Bulk_logged Recovery Models, it'll be Minimally Logged, as well.  Just remember that you that you can't do that if you're doing replication, etc.  And take a logfile backup immediately before and after using that command.

     

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

  • November 28, 2022 at 10:47 pm

    #4119198

    BTW... it's a really good habit to add brackets to the name of the file group.  It'll let you live for HISTORY without them but it's absolutely required if the file group is [PRIMARY].  So the ON HISTORY part of your code should be ON [HISTORY] just to develop a safe habit.  It's one of the few places where things like that are sometimes required.

     

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

  • November 28, 2022 at 10:53 pm

    #4119201

    As a bit of a future-planning sidebar...

    If that table ever becomes fragmented, you may want to someday defragment it.  To keep the file group from having a whole bunch of wasted space after doing doing such a thing, here's what I do (and I'll use HISTORY in my example.

    1. Create the file group AND the file it contains as HISTORY_1
    2. Do your thing with the CREATE INDEX WITH(DROP_EXISTING = ON) to move it to HISTORY_1 (instead of history).
    3. If you ever need to defragment the index, create a new file group and file each name HISTORY_2.
    4. Do the CREATE INDEX WITH(DROP_EXISTING = ON) to move it to HISTORY_2.
    5. Drop the now empty HISTORY_1 file and file group and that returns the space back to the OS.
    6. Next time you need to do it, reverse it and move it back to a new HISTORY_1 File/Filegroup and drop the now empty HISTORY_2 file/filegroup.

    Of course, that can be automated with a stored procedure.

     

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

  • November 29, 2022 at 12:51 am

    #4119222

    Thanks Jeff .. by default, will all sorting be done in tempdb during Index creation?

  • November 29, 2022 at 3:20 am

    #4119255

    Bruin wrote:

    Thanks Jeff .. by default, will all sorting be done in tempdb during Index creation?

    It took you longer to post the question than it would for you to look it up.  You have to learn to look stuff like this up.  Lookup CREATE INDEX and then search for "tempdb" in that documenation.

    Then try it out and let us know. 😉  I know the answer but want to see if you'll come to the right conclusion.

    I'm not saying that to be snarky or mean.  You need to learn to look stuff up instead of relying on the kindness of strangers.  It will make you much more valuable to your company.  Where there's perceived value, there's normally some $$$ in the form of an annual increase.  Higher performances get a bigger chunk of the bonus pie.

    As a bit of a sidebar, I don't want big tables to sort in tempdb.  I want to save tempdb for other stuff that is running at the same time.  Others will have a different opinion/recommendation but mine is, leave tempdb alone for stuff like this.

     

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

  • November 29, 2022 at 6:00 pm

    This was removed by the editor as SPAM

  • November 29, 2022 at 6:01 pm

    This was removed by the editor as SPAM

Viewing 9 posts - 16 through 23 (of 23 total)

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