Post reply

The Real World: Rebuilding Index - 1 Instance, 106 Databases

  • February 16, 2015 at 9:38 pm

    #314679

    Comments posted to this topic are about the item The Real World: Rebuilding Index - 1 Instance, 106 Databases

    Br. Kenneth Igiri
    https://kennethigiri.com
    All nations come to my light, all kings to the brightness of my rising

  • February 16, 2015 at 9:39 pm

    #1777937

    106 automatically created databases....

    Wow! :crazy:

    Interesting script though, thanks! 🙂

  • February 16, 2015 at 9:57 pm

    #1777938

    Hi,

    . wow..nice script...:-)...very true.....I am having the same scenario. And index maintenance become a very crucial in this situation. I am using more or less same script to build that. But what I have found that, because we hold all the database .mdf file in same drive that's why it gets fragmented more because the external frag. is more then internal one. But I can't make that much drives. I am still thinking on it..

  • February 17, 2015 at 3:40 am

    #1778005

    shivendrakumaryadav60 (2/16/2015)

    Hi,

    . wow..nice script...:-)...very true.....I am having the same scenario. And index maintenance become a very crucial in this situation. I am using more or less same script to build that. But what I have found that, because we hold all the database .mdf file in same drive that's why it gets fragmented more because the external frag. is more then internal one. But I can't make that much drives. I am still thinking on it..

    OK. But if your databases are not being auto created, you can also trying pre-growing your data files to take care of external fragemntation.

    Br. Kenneth Igiri
    https://kennethigiri.com
    All nations come to my light, all kings to the brightness of my rising

  • February 17, 2015 at 3:41 am

    #1778006

    jdurandt (2/16/2015)

    106 automatically created databases....

    Wow! :crazy:

    Interesting script though, thanks! 🙂

    haha... 106 as at last count... I would have mentioned the application but I fear being sued 😀

    Br. Kenneth Igiri
    https://kennethigiri.com
    All nations come to my light, all kings to the brightness of my rising

  • February 17, 2015 at 4:01 am

    #1778007

    @Br. Kenneth,

    . yes you are correct and I am thinking in that direction too. But for that I am analyzing the avg size need to take. This will reduce it but not prevent. it will increase the threat to full the disk space even though it has with some unused files but not release to OS if I have not chosen a appropriate initial size.

    Thanks for your reply 🙂

  • February 17, 2015 at 4:56 am

    #1778017

    You guys need Minion Reindex in the worst kinda way.

    We've got tons of cool features including extensive logging.

    Check out the link on the little green guy below. It's completely free.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • February 17, 2015 at 6:30 am

    #1778045

    An application performing on-the-fly DDL? That wouldn't come anywhere near my network.

  • February 17, 2015 at 7:32 am

    #1778062

    Just a thought since there's another article today talking about SQL Server Express. Could Minion be made to work with Express? There's no Agent but perhaps some simple scripts to call the procs so that they can be scheduled with Windows Task Scheduler

    Gerald Britton, Pluralsight courses

  • February 17, 2015 at 7:33 am

    #1778063

    "on-the-fly DDL" meaning what exactly? Temp tables, table vars, CTEs and the like?

    Either you have an extremely simple environment or you're expecting us to believe that you run an enterprise of DBs without even the simplest of coding practices.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • February 17, 2015 at 7:36 am

    #1778064

    g.britton (2/17/2015)

    Just a thought since there's another article today talking about SQL Server Express. Could Minion be made to work with Express? There's no Agent but perhaps some simple scripts to call the procs so that they can be scheduled with Windows Task Scheduler

    I don't see why not. You could either call it from sqlcmd or ssis, or anything else that can make a DB connection. I'm not going to write it for you, but they're just SPs so if you can make a connection you can call Minion Reindex. The Agent scheduler is just how we usually do things. You could even wrap the SQL call into a powershell and run that from the windows scheduler.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • February 17, 2015 at 7:58 am

    #1778069

    Check out this : - https://ola.hallengren.com/

  • February 17, 2015 at 8:00 am

    #1778070

    Trust me, you'll be happier with Minion Reindex. We do more.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • February 17, 2015 at 8:31 am

    #1778080

    There is no such thing as an accurate, global freespace percentage. Freespace needs to be set index by index. Thus, a generic rebuild should never specify a fillfactor, but let it default to what has already been set for that index.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • February 17, 2015 at 8:33 am

    #1778082

    ScottPletcher (2/17/2015)

    There is no such thing as an accurate, global freespace percentage. Freespace needs to be set index by index. Thus, a generic rebuild should never specify a fillfactor, but let it default to what has already been set for that index.

    Are you talking to me or to the original article?

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

Viewing 15 posts - 1 through 15 (of 33 total)

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