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

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4614

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

    Br. Kenneth Igiri
    www.scribblingsage.com
    All nations come to my light, all kings to the brightness of my rising

  • jdurandt

    SSChasing Mays

    Points: 628

    106 automatically created databases....

    Wow! :crazy:

    Interesting script though, thanks! 🙂

  • shivendrakumaryadav60

    SSC Eights!

    Points: 811

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

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4614

    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
    www.scribblingsage.com
    All nations come to my light, all kings to the brightness of my rising

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4614

    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
    www.scribblingsage.com
    All nations come to my light, all kings to the brightness of my rising

  • shivendrakumaryadav60

    SSC Eights!

    Points: 811

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

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    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:

  • phegedusich

    Ten Centuries

    Points: 1353

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

  • g.britton

    SSChampion

    Points: 13689

    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, MCSE-DP, MVPToronto PASS Chapter[/url]

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

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

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    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:

  • Thayal Muhunthan

    Old Hand

    Points: 358

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

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    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:

  • ScottPletcher

    SSC Guru

    Points: 98441

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

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    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