February 13, 2013 at 6:01 am
Hi All,
Suppose my msdb database has grown to 1 GB all of a sudden. What does it indicate and could be possible reasons for such big msdb. is that replicated txns ? or what else could be possible reasons?
Thanks in advance
February 13, 2013 at 6:11 am
Mail logs due to spikes in activity are a common reason. Job and backup history are others but those do not typically cause sudden jumps, usually more gradual growth.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 13, 2013 at 6:18 am
Check the output of the following query for top consuming tables and post their names
USE msdb
GO
SELECTOBJECT_NAME(i.object_id) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, i.type_desc AS IndexType
, p.partition_number AS PartitionNo
, p.rows AS NumRows
, au.type_desc AS InType
, au.total_pages AS NumPages
, au.total_pages * 8 AS TotKBs
, au.used_pages * 8 AS UsedKBs
, au.data_pages * 8 AS DataKBs
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 au ON
CASE
WHEN au.type IN (1,3) THEN p.hobt_id
WHEN au.type = 2 THEN p.partition_id
END = au.container_id
WHERE OBJECT_NAME(i.object_id) NOT LIKE 'sys%'
AND OBJECT_NAME(i.object_id) NOT LIKE 'queue%'
AND object_name(i.object_id) <> 'dtproperties'
ORDER BY 10 desc
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2013 at 7:45 am
Thank you. It was database mails which is failing.
Thanks a lot.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply