We often run out of the space in our drive and that is the number 1 cause of SQL Server engine stop running on various machines. Quite often we wonder how much space if any of the objects takes in the database. It is very simple to find out the space used by any table in the database.
USE AdventureWorks; GO EXEC sp_spaceused 'Sales.Customer'; GO
You can run above script to find the space in your database. It also shows how many rows are there in each table along with the size of the index as well, which can be very much useful.
Here are few additional blog posts related to this subject which I encourage you to read it.
Identify Used Space in Log File- Interview Question of the Week #090
SQL SERVER – Disk Space Monitoring – Detecting Low Disk Space on Server
SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server
SQL SERVER – Size of Index Table for Each Index – Solution 2
SQL SERVER – Who is Consuming my TempDB Now?
If you know any other trick which we can all find useful, I suggest that you send me email and I will publish it with due credit.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
9 Comments. Leave new
This is the best SQL Blog ever. TY.
Cool tip. I really enjoy your blog! I come here whenever I have a SQL problem. Thanks!
Hi Pinal,
Does it shows the excat data. Suppose there is bulk operation happed in that case it will show the excat data..?
Hi Pinal,
Is there any way to use replace function for ntext data type. If i want to change a particular text in all columns and all tables in a database. How can i resolved it ?
Please suggest. Thanks in advanced
I think you need to run DBCC UpdateUsage before you run sp_spaceused in order to get a correct result.
You will need to have sa role to find the space usage of all databases. Is there any work around for this?
Sir m storing image of visitorsand visitor data in a visitor table. The database has become to heavy i doubt its becuz of the size of image is there any way to know the size occupied by a specific column plz. let me know on my email id
Hi Sir,
Could you please tell me how to write query for “Find Space Used For All Tables in single database or instance level database ”
Rahulreddy
[email removed]
i would like to know is there a way to identify the amount of space used by the table for particular id or scenario.