SQL SERVER – Find Number of Rows and Disk Space Reserved – Using sp_spaceused Interesting Observation

Previously I posted SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL. Today we will look into the same issue but with some additional interesting detail.
We can find the row count using another system SP sp_spaceused. This SP gives additional information regarding disk space reserved on database as well. Well, when I ran the SP on AdventureWorks first time, I suspected that database SP is not providing me correct results.

After a bit investigating I found that it may be possible that due to any reason may be the usage on AdventureWorks database might not be updated. I ran same SP with additional param as shown below and it provided me updated information for the same. Once I ran the SP with @updateusage param, it updated the status of the database. When I ran it again next time without param it gave the same results.

The reason for above behavior is when dellocation of space happened in database (e.g. dropping table or truncating table) it does not release the used space right away. However, when @updateusage param is used database engine scans whole database and update the sapce usage in respective tables.
Let me know if you guys have any interesting observation like this.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

11 thoughts on “SQL SERVER – Find Number of Rows and Disk Space Reserved – Using sp_spaceused Interesting Observation

  1. Hello Pinal!

    I found this article (as many many others) really interesting and usefull daily.

    This one in particular reminds me of the Garbage Collection mechanism of the .Net Framework (e.g. http://msdn.microsoft.com/pt-br/library/system.gc.gettotalmemory(VS.80).aspx) or J2SE .

    Still regarding memory consumption by the SQL Server, i have a less practical but more academical question, and it is about Query Execution Plans.

    I’d like to ask if you could be so kind to write for us as article about how are managed the resources of generating querys executing plans.
    Plus, how are this execution plans reused apon repeating same query / adapting an execution plan to a very similar query VS generating a new execution plan apon every repetition of query / regenerating another execution plan apon slightly different query ?

    Apologies for my poor english skills.
    Thanks for your help :)

  2. Hello pinal,

    do you have any sp for resotraing databases from production to development server directly? it may use Batch file for FTP server.

    I used to do this manually now, by transferrring the BAK file to dev server and by running the restore script , we used to do.

    Please suggest on this.

    i required automation on this scenario.

    Regards
    — Mohit

  3. Hello,

    I do have a question regarding size or space used in SQL Server. I would like to know if you know how I can find the size use for a particular number of rows. for instance, if I have a table like that

    Table products
    person
    products

    I would like to know how much space customer 1 has used in that products tables.

    like that. customer 1 has product 1, customer 2 has product 2 and customer 1 has product 2. I only want to know the size used in this table for customer 1.

    thanks for your time

  4. By using the sp_spaceused stored procedure you can get the details for only one object whereas the below query will return the details for all objects or you can add the filter on the same.

    SELECT ObjectName = OBJECT_NAME(OBJECT_ID),
    Reservedpages = SUM (reserved_page_count),
    Usedpages = SUM (used_page_count),
    Pages = SUM (
    CASE
    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
    ELSE lob_used_page_count + row_overflow_used_page_count
    END
    ),
    Row_Count = SUM (
    CASE
    WHEN (index_id < 2) THEN row_count
    ELSE 0
    END
    )
    FROM sys.dm_db_partition_stats
    GROUP BY OBJECT_NAME(OBJECT_ID)

  5. hi pinal,

    If want to check the spaceused for all the databases in the single query is it possible.Instead of mentioning each and every database name.

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #012 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s