Space allocation in data file

Question

Space allocation in data file

Noel Kennedy

Ten Centuries

Points: 1214
More actions
July 15, 2010 at 4:01 am

#142040

I have an 8gb SQL 2005 database. Not big by modern standards but the tables within the database only take up 540mb of that space. What on earth could be using up the rest of the space?!
Size:7791.81 MB
Space available:0.63 MB
Primary data file: 6,892 MB
Log file : 900 MB
sp_spaceused was used on all user tables in the DB.
This database is a replicated database. It also makes use of SQL Service Broker.
Any clues as to where I should be looking?! 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

How are you measuring the total size of all tables?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Noel Kennedy Ten Centuries Points: 1214 More actions · Answer 2

I used this proc. In summary: It runs spaceused on all tables where OBJECTPROPERTY(id, N'IsUserTable') = 1:

DECLARE @TableName VARCHAR(100) --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing

DECLARE tableCursor CURSOR

FOR

select Name

from dbo.sysobjects

where OBJECTPROPERTY(id, N'IsUserTable') = 1

FOR READ ONLY

--A procedure level temp table to store the results

CREATE TABLE #TempTable

(

tableName varchar(100),

numberofRows varchar(100),

reservedSize varchar(50),

dataSize varchar(50),

indexSize varchar(50),

unusedSize varchar(50)

)

--Open the cursor

OPEN tableCursor

--Get the first table name from the cursor

FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch

WHILE (@@Fetch_Status >= 0)

BEGIN

--Dump the results of the sp_spaceused query to the temp table

INSERT #TempTable

EXEC sp_spaceused @TableName

--Get the next table name

FETCH NEXT FROM tableCursor INTO @TableName

END

--Get rid of the cursor

CLOSE tableCursor

DEALLOCATE tableCursor

--Select all records so we can use the reults

SELECT *

FROM #TempTable

--Final cleanup!

DROP TABLE #TempTable

GO

EXEC GetAllTableSizes

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

And what, when totalled comes to that 540MB? Data size, index size, unallocated size, sum of those?

If you run DBCC UpdateUsage, does anything change?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Noel Kennedy Ten Centuries Points: 1214 More actions · Answer 4

I totalled the reserved size: 538,808 KB.

DBCC UPDATEUSAGE reported:

DBCC UPDATEUSAGE: counts updated for table 'sysobjvalues' (index 'clst', partition 1):

USED pages (LOB Data): changed from (108) to (107) pages.

RSVD pages (LOB Data): changed from (132) to (130) pages.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

But no significant changes in the sizes reported for the tables.

My concern, is that replication or sql service broker needs some maintenance but I can't confirm where the space is going so I don't know what to maintain!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Can you query sys.dm_db_index_physical_stats and see if the sizes agree?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Noel Kennedy Ten Centuries Points: 1214 More actions · Answer 6

I executed:

select name, * from sys.dm_db_index_physical_stats

(DB_ID(N'MyDbName'),null, null, null,null)

There was no size in KB in the result set of the query. I get a page_count though, that summed and times by data page size (8KB) = 580,224 KB

I did notice that nearly all the tables were heaps. I've put clustered indexes on them which has slightly increased the size of the db. The query I posted earlier now reports 585,128 KB.

So in answer to your question, I think sys.dm_db_index_physical_stats & sp_spaceused agree quite closely.

ps, thanks for your help so far 🙂

Noel Kennedy Ten Centuries Points: 1214 More actions · Answer 7

I've finally tracked this down! I noticed my DBCC was taking ages on : dbcc ssb check

This is not terribly well documented but is a consistency check on sql service broker objects. After a lot more digging I found that there were rather a lot of unclosed SSB conversations in the DB :

set transaction isolation level read uncommitted

select count(*)

from sys.conversation_endpoints

These unclosed conversations acounted for the discrepancy between the space allocated to tables and the size of the DB.

Hope this helps someone else! 🙂