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

  • 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

  • 106 automatically created databases....

    Wow! :crazy:

    Interesting script though, thanks! 🙂

  • 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..

  • 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

  • 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

  • @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 🙂

  • 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:

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

  • 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

  • "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:

  • 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:

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

  • 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:

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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 34 total)

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