Mysteriously wasted space ... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Mysteriously wasted space ...

Author

Topic

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2008-06-27 : 11:14:29

Hello,

I have database with many tables. The backup file size of the database is approximately 27GB, with the datafile of approximately the same size. However, when I add up all the sizes of all the tables (data + index), it is only a bit over 6GB. So, what is taking the rest of 21GB?

Below is the query I use for calculating the sizes of tables and indexes:

SELECT Name, Rows, CONVERT(NUMERIC(10,2),CONVERT(INT,REPLACE(Data,'KB',''))/1024.0) AS 'Data_SpaceUsed(MB)', CONVERT(NUMERIC(10,2),CONVERT(INT,REPLACE(Index_Size,'KB',''))/1024.0) AS 'Index_SpaceUsed(MB)'
FROM #SpaceUsed

Does anyone have any idea what the rest of the space is being used by?

Thank you!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-06-27 : 11:26:51

free space between records + tables - what fillfactor are you using?
also "sql pages" which don't fill up perfectly with records.

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2008-06-27 : 11:37:09

Andrew,

I was under the impression that the fill factor would be counted in when we calculate the space used by the data of a table. I guess I was wrong.

How can I found out the fill factor of each table?

Thank you!

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2008-06-27 : 16:24:00

Guys,

I digged deeper and found out that there is 1 table which is causing this confusion. When I run EXEC sp_spaceused 'Audit', I get:

Name: Audit
Rows: 12228956
Reserved: 24440904 KB
Data: 3768904 KB
Index_Size: 56 KB
Unused: 20671944 KB

So, according to this result, most of this table's space is unused.

But when I look at the available space to shink the database file, by running the following command provided by microsoft on the BOL:

SELECT name , size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sysfiles, I get the following result:

Name AvailableSpaceMB
GN_Data 49.43
GN_Log 2610

This appears to imply that I cannot shrink the data file.

My question is: How can I get rid of the unused space in the "Audit" Table?

Thanks a lot!

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2008-06-27 : 17:00:54

OK, it looks like the solution is to add a clustered index (since I don't have it on this table) and then rebuild it. I will try it out.

This is another example as to why not having the clustered index might cause problems.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources