none
big msdb RRS feed

  • Question

  • 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

    Wednesday, February 13, 2013 1:00 PM

Answers

All replies

  • Hello,

    This can have several reason. First you should check the existing tables for size & count of rows with the following script:

    SELECT SCH.name AS SchemaName, 
           OBJ.name AS ObjName, OBJ.type_desc AS ObjType,
           INDX.name AS IndexName, INDX.type_desc AS IndexType,
           PART.partition_number AS PartitionNumber,
           PART.rows AS PartitionRows, 
           STAT.row_count AS StatRowCount,
           STAT.used_page_count * 8 AS UsedSizeKB,
           STAT.reserved_page_count * 8 AS RevervedSizeKB
    FROM sys.partitions AS PART
         INNER JOIN sys.dm_db_partition_stats AS STAT
             ON PART.partition_id = STAT.partition_id
                AND PART.partition_number = STAT.partition_number
         INNER JOIN sys.objects AS OBJ
             ON STAT.object_id = OBJ.object_id
         INNER JOIN sys.schemas AS SCH
             ON OBJ.schema_id = SCH.schema_id
         INNER JOIN sys.indexes AS INDX
             ON STAT.object_id = INDX.object_id
                AND STAT.index_id = INDX.index_id
    ORDER BY SCH.name, OBJ.name, INDX.name, PART.partition_number


    Olaf Helper

    Blog Xing

    Wednesday, February 13, 2013 1:08 PM
    Moderator
  • Hi Manu,

    As msdb contains history of SQL Jobs, backup & restore information so you can check that time to time you are recycling job histories.

    Wednesday, February 13, 2013 1:09 PM
  • Hi Manu,

    As msdb contains history of SQL Jobs, backup & restore information so you can check that time to time you are recycling job histories.

    Hallo Suraj,

    can you please stop marking your posts as "proposed answer"! It is not because you are wrong but it indicates in the thread list that this issue seems to be closed. In some cases I found your answers which are wrong and I'm wondering how many readers did not read the thread because they think it is answered.

    Basically it should be an obligation of the thread initiator to decide what answer covers his requirements. If there is no reaction than most of the regulars mark proposed answers but - just my point of view - it can not be the correct way that the writer himself marks his own answer without checking the answers of others which may better cover the requirements of the initiator.

    Thank you for considering this!


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Wednesday, February 13, 2013 2:34 PM
  • Hi,

    Thank for you suggestions, i see database mail logs getting filled up.

    Many Thanks again

    Wednesday, February 13, 2013 2:44 PM
  • can you please stop marking your posts as "proposed answer"! It is not because you are wrong but it indicates in the thread list that this issue seems to be closed.


    There is a TechNet Wiki article about it: Whether or Not You Should Self Propose an Answer in an MSDN or TechNet Forum

    Olaf Helper

    Blog Xing

    Wednesday, February 13, 2013 2:49 PM
    Moderator
  • i see database mail logs getting filled up.

    Hello,

    you can clear the mail queue with sysmail_delete_mailitems_sp (Transact-SQL); see also Troubleshooting Database Mail: Mail queued, not delivered


    Olaf Helper

    Blog Xing

    Wednesday, February 13, 2013 5:26 PM
    Moderator