sp_spaceused rewritten as one query (no cursors)

  • Greg Drake

    Ten Centuries

    Points: 1020

    Comments posted to this topic are about the item sp_spaceused rewritten as one query (no cursors)

  • Caruncles

    Hall of Fame

    Points: 3239

    I'm a relative novice. How can I use this?

    Wallace Houston
    Sunnyland Farms, Inc.

    "We must endeavor to persevere."

  • Steven Willis

    SSCrazy Eights

    Points: 9893

    Very nice! Added to my utility "tool box."

    For Caruncles: If you have a situation where your db is running out of alloted space (such as on a 3rd-party shared server) you can use this script to see what objects are the space hogs. Often there are tables that can be "trimmed" of old data, or indexes rebuilt that will reduce the size of the db and avoid upgrade fees from the host.

    Or, in development, you can use this tool to see what your procedures are doing and confirm that they haven't created some endless loop that would cause a table to grow until it brings the server to its knees.

    Or, as I had to do just the other day, you need to analyze someone else's database and just want to see what's there. This tool will show almost instantly the most important tables because those would generally be the ones with high row counts. It will also tell you that certain tables are likely never used if the row count is zero.

    Just lots of good info about your db's tables and indexes.

     

  • Caruncles

    Hall of Fame

    Points: 3239

    Well stated, Old Hand. Thanx!

    Wallace Houston
    Sunnyland Farms, Inc.

    "We must endeavor to persevere."

  • Marcia J

    SSCertifiable

    Points: 5635

    Excellent! 🙂

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    I thought this looked familiar. Thanks again.

  • aureolin

    Hall of Fame

    Points: 3518

    Stolen -- err, 'borrowed'.

    Actually, this came at a very appropriate time. We're just getting ready to talk to a vendor about their vastly over-spec'ed database server. This query helps give me details about our current data size and helps my ability to demonstrate adequate performance over middling sized data (our largest table is only 2 Gb.) on much, much smaller hardware.

    Thanks again!

Viewing 8 posts - 1 through 8 (of 8 total)

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