Post reply

Multiple Shrinkfile operations

  • September 3, 2010 at 10:28 am

    #225682

    Assumptions: Low disk space, no other available storage

    I need to shrink several database files. I've noticed if several shrinkfile operations are issued at the same time the disk idle time drops to 0.

    Is it best to run multiple shrink operations in series vs. in parallel to avoid overworking the disk?

    If so does anyone have any suggestions on scripting out series execution of several shrink operations?

    Thanks,

    Jon

  • September 3, 2010 at 10:59 am

    #1216294

    I prefer to do those in series. Typically better performance - from observation and not hard metrics.

    I don't have a script to auto-generate the shrink scripts though.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 3, 2010 at 12:44 pm

    #1216377

    Not sure if wrapping each in a BEGIN TRAN / EXEC TRAN will work b/c it's a dbcc command...

  • September 3, 2010 at 12:50 pm

    #1216379

    This is borrowed from:

    http://www.eggheadcafe.com/software/aspnet/30488923/script-which-will-shrink-all-databases.aspx

    EXEC sp_MSForEachDB @Command1 = N'DBCC SHRINKDATABASE (?, 10)', @replacechar = '?'"

    There's some other notes and scripts there that may be more in line with what you're looking to do, but I believe this will get you from point a to b with minimal fuss.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • September 3, 2010 at 12:51 pm

    #1216380

    Just for load, I'd do them in series. As noted by Jason, I have no metrics.

    I'd also use shrinkfile instead of shrinkdatabase. Had better luck with that.

  • September 3, 2010 at 12:53 pm

    #1216382

    Thanks, but I'm thinking that's actually going to run a bunch of db shrink commands at the same time...which is the problem I'm looking to address.

  • September 3, 2010 at 6:24 pm

    #1216471

    You may be able to change that msforeachdb command to print out the commands - then you would have more control over the execution of the commands.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 7 posts - 1 through 6 (of 6 total)

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