database size clash

Question

Post reply

database size clash

ALI SQLDBA

SSCrazy

Points: 2121
More actions
January 20, 2010 at 10:39 am

#137508

Hello Gurus
I have database in prod whose size is 21GB approx and unused space is 4GB. So, the actual database size is 16GB Approx. When I looked at Disk Usage by Top Tables in SSMS I see the Total reserved space in KB as 9350832 KB (~ 9.3GB). Again, using the following code, I checked the data size of the Database. which summed to 5.6GB Approx.
USE AdventureWorks
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp
May be I am looking at three different things? I am not sure. If I am looking at three different things please advice me on what is the difference.
Thank you very much
Ali

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I'm wondering if you are comparing apples to oranges.

How are you determining the first values (21 GB, 4 GB).

How are you determining the second value ~ 9 GB.

I see how you are getting the third set, but does that include the size of the indexes on the tables?

Does some of the values you are looking at also include the space used by and filled in the transaction log?

ALI SQLDBA SSCrazy Points: 2121 More actions · Answer 2

How are you determining the first values (21 GB, 4 GB).

SSMS right click on database--> Properties

How are you determining the second value ~ 9 GB.

Right click on the DB --> Reports --> Standard Report --> Disk Usage by Top Tables

I am sure the above two methods include the index spaces. By the way, if this information is useful, we have only couple of views and good no.of SP's. But still I dont think this will quantify the difference.

Thanks

Ali

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 3

Can you run the query below and see the sizes then...

DBCC updateusage(myDB)

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

ALI SQLDBA SSCrazy Points: 2121 More actions · Answer 4

This query is giving out lot of data.. is there any way that I can consolidate this whole information??

Thanks

Ali

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Use DBCC UPDATEUSAGE with no output and then run sp_SpaceUsed... or, there's a parameter that you can add to sp_SpaceUsed that will do it for you. It's in Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLPirate SSCrazy Points: 2880 More actions · Answer 6

SSMS right click on database--> Properties

This value reflects both the size of your data file(s) and the size of the log file(s)

You can see the breakdown between the two using the Disk Usage report.

Right click on the DB --> Reports --> Standard Report --> Disk Usage by Top Tables
I am sure the above two methods include the index spaces. By the way, if this information is useful, we have only couple of views and good no.of SP's. But still I dont think this will quantify the difference.

You are right, both methods include the index spaces. The 5.6 GB sum from the query in your initial post does not include indexes. I think the majority of the space discrepency is from the log space.

Use DBCC SQLPERF('logspace') to see if the Log Size (MB) for the database covers that gap you see.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

SQLPirate is correct on the space info. Run the query he suggested. You will likely see the discrepancy there.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ALI SQLDBA SSCrazy Points: 2121 More actions · Answer 8

The query answered my question 😀

Thanks Guys. This website is being very helpful for me. Even though, I am new to sql, I am able to cope up in my job place because of the articles and forums in this website...

Thanks to you all!!!

Ali

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

You're welcome

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events