June 27, 2008 at 9:16 am
Hello,
I have database with many tables. The backup file size of the database is approximately 27GB. 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!
June 27, 2008 at 10:55 am
Possibly unused space?
select
[FileSizeMB] =
convert(numeric(10,2),sum(round(a.size/128.,2))),
[UsedSpaceMB] =
convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
[UnusedSpaceMB] =
convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
[Type] =
case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
[DBFileName] = isnull(a.name,'*** Total for all files ***')
from
sysfiles a
group by
groupid,
a.name
with rollup
having
a.groupid is null or
a.name is not null
order by
case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
a.groupid,
case when a.name is null then 99 else 0 end,
a.name
June 27, 2008 at 10:59 am
June 27, 2008 at 11:30 am
Adam,
Thank you for the query. I ran it and it shows that only 28MB are unused and the other 26+GB are used. So I am guessing it is something else.
Thank you!
June 27, 2008 at 11:31 am
Mani,
Thank you for the link. I heard that it is not advisable to shrink the database file. Also, shouldn't I have free space before I can shrink it? Checking the space using Adam's query showed that all the space I have is used ... although I am not sure by what.
Please let me know
Thank you!
June 27, 2008 at 2:23 pm
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!
June 27, 2008 at 2:28 pm
Try rebuilding the clustered index on that table. If need be, you might care to check that the fill factor on the clustered index isn't set to something too low.
If you don't have a clustered index, then you need to do something like a SELECT....INTO a new table, drop the old one, and rename the "new" one to the old name.
Once you do - the space should be "freed up" in the Database, and then the shrink should yield something.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 27, 2008 at 2:48 pm
use QUEST capability manager to see it in visual.
June 27, 2008 at 3:02 pm
Matt,
I will try that.
Thank you!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply