TableSpace Update

Comments: 11 Comments
Published on: May 19, 2010

The last post in the series on finding the sizes of your tables showed us how we could find that size via a set-based method similar to using sp_MStablespace.  In that post I showed you how to find the sizes using the SQL 2005 system objects rather than the scheduled to be deprecated objects.  You can read more about it here.

I needed to go back and revisit that post and perform a little spring cleaning on it.  I noticed after publication that the script had multiple columns by the same name that were unintended.  I discovered this as I was prepping and reviewing for my presentation at our SSSOLV (PASS UG) meeting last week.  During that review I came across another bug.  The script was not consistently returning the index size for the tables.  After reviewing the script more closely, against more databases, I found the problem and fixed it.  The change was rather simple – I changed the Join from the #indstats table to go directly to sys.partitions rather than through sys.dm_db_index_usage_stats.  I was able to spot this one due to running the query against one of my databases that is seldom used and thus the indexes were less likely to have statistics populated in that DMV.

So, here is the updated query.  Rather than breaking out into sections like I did in that last article, I am just pasting the script in its entirety.

[codesyntax lang="tsql"]

[/codesyntax]

At some point in the future, I intend on modifying this query to make it more flexible in output, as well as to make it into a stored procedure.

11 Comments - Leave a comment
  1. [...] This post was mentioned on Twitter by Jason Brimhall. Jason Brimhall said: New post: TableSpace Update (http://bit.ly/at34IN) [...]

  2. Does sys.dm_db_partition_stats return a lot of this info? Is the data from that view ever inconsistent with the data from your script? Just curious

    • Jason Brimhall says:

      Yes, the information is available there and is coming up in the next article that discusses my version of sp_spaceused.

  3. Dugi says:

    Jason,

    I guess that SSOLV members enjoy your nice presentation, thanks for sharing and explanation!

    • Jason Brimhall says:

      They did enjoy it. There will be a few follow-ups that will basically show the rest of the presentation.

  4. quoted on Space Used | SQL RNNR says:

    [...] the next installment in this mini-series.  You can find the rest of the articles by perusing the last article I wrote on the topic.  That article was a correction to the TableSpace script about which I had [...]

  5. Ninja says:

    Just a small hint, I’ve noticed the % of DB is based on the data file size rather than the used space of the DB. So my total used space is like 56% rather than 100%. Maybe a new column is in order…

  6. Ninja says:

    Any way to remove the system tables from the list? Not sure it’s a usefull default :-P.

  7. Jason Brimhall says:

    Remi,
    I thought about removing the system tables but decided to leave them. When I removed them I saw a difference in size of the total database size and wanted to make sure the size this script reported was consistent with the size in SSMS.

  8. Ninja says:

    Makes sense, I also saw the commented filters, so it’s easy enough to correct.

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">








Calendar
May 2010
M T W T F S S
« Apr   Jun »
 12
3456789
10111213141516
17181920212223
24252627282930
31  
Content
SQLHelp

SQLHelp


Welcome , today is Saturday, October 25, 2014