November 25, 2017 at 10:10 am
Dear All,
I have MDS configurated in our work environment. We have just some tables (50 tables) each with less that 50 records but the database keeps growing and it has now 9GB in size which does not make sense in terms of data that is realy stored that.
Can you please help me understand why do we have so much size?
Thank you for the support
P.S - If possible, can you please let me know also what are the maintenance tasks that you are doing for this specific database?
December 4, 2017 at 7:14 am
1. Is the table a heap and has a lot of deletes occurring on it.
If this table is a heap and has a lot of deletes then the empty pages are not released back to the system and the table size keeps growing.
2. How much data in the .MDF
3. How big is the LOG file (LDF)
Can you let us know the results below,
--Query 1 - Checking the size of tables in a database
select
object_name(i.object_id) as ObjectName,
i.[name] as IndexName,
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
from
sys.indexes i
inner join sys.partitions p
on i.object_id = p.object_id and i.index_id = p.index_id
inner join sys.allocation_units a
on p.partition_id = a.container_id
group by
i.object_id,
i.index_id,
i.[name]
order by
sum(a.total_pages) desc,
object_name(i.object_id)
go
--Query 2
SP_HELPDB Databasename
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply