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 (https://blog.sqlauthority.com)
11 Comments. Leave new
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. 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 :)
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
How can i know that how much size of a database actually database engine has allocated it?
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
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)
hi pinal,
thank you, i need a disk space alert script for sql 2008 any hint will be appriciated.
tayyeb
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.
Only possible way is to use undocumented procedure sp_msforeachdb. But you can create a stored procedure to simulate the logic as shown in this post
Could you show me how to get the row count on a table in VB.Net using the sp_spaceused command
Thanks for sharing
Hi,
Quick question on this, when we execute sp_spaceused always it return two select statements, is this possible to get all the columns in one Select statement? . Is any other SP available to get this in one single row for all the databases in the server.
Because only option i think is we have to edit or create a new stored procedure and modify the select statements to make it in a single row. Since it is a System defined stored procedure, thinking whether we have any other Sytem defined SP will do the same job.
Thanks.