Microsoft Dynamics CRM Database can be giant because many reason..
The main reason is files into CRM DB.
Attached to this article two queries.
First query to get database size per table.
Second query to get number of records – filee in Annotation table.
Enjoy,
Rami Heleg
SELECT
so.object_id AS ObjectID,
so.name AS ObjectName,
(CONVERT(decimal(20,4),(SUM (ps.reserved_page_count) * 8))) / 1024 As Reserved_MB,
(CONVERT(decimal(20,4),SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count
END
) * 8)) / 1024 As Data_MB,
(CONVERT(decimal(20,4),(CASE WHEN (SUM(used_page_count)) >
(SUM(CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count
END
)) THEN (SUM(used_page_count) -
(SUM(CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count
END
))) ELSE 0 END) * 8)) / 1024 As Index_MB,
(SUM (
CASE
WHEN (ps.index_id < 2) THEN ps.row_count
ELSE 0
END
)) AS [RowCount]
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.objects AS so ON so.object_id = ps.object_id
WHERE so.object_id > 100
GROUP BY so.object_id, so.name
ORDER BY [Reserved_MB]Desc
=============================================================
SELECT e.logicalname, e.ObjectTypeCode,COUNT( e.ObjectTypeCode) AS NumberOfRecords
FROM Annotation a inner join Entity e
on a.ObjectTypeCode = e.ObjectTypeCode
GROUP BY e.ObjectTypeCode , e.logicalname
order by NumberOfRecords desc
No comments:
Post a Comment