none
Big MSDB Database RRS feed

  • Question

  • Hi,

    Our MSDB-database is quite big on one of our SQL Servers because of the huge amount of databases on that server.
    I've tried reducing the logs and thus records in this database with the following result.
    But my database stays @ 3.6Gb with 16 Mb of free space (it's in Simple mode, so nog logfile).

    What am I doing wrong ?

    Regards,
    Sven peeters
    Belgium

    TableName                                                    Rows        DataKB             IndexSizeKB
    ------------------------------------------------------------ ----------- ------------------ ------------------
    backupmediafamily                                            1055        1624 KB            11296 KB
    backupset                                                    1055        664 KB             11536 KB
    backupmediaset                                               1055        1072 KB            6144 KB
    sysmaintplan_logdetail                                       0           4304 KB            8 KB
    sysmail_mailitems                                            0           3664 KB            16 KB
    sysobjvalues                                                 880         1904 KB            16 KB
    backupfile                                                   2110        848 KB             80 KB
    syscolpars                                                   2906        464 KB             224 KB
    sysmaintplan_log                                             0           128 KB             16 KB
    sysjobhistory                                                2           456 KB             40 KB
    backupfilegroup                                              1055        136 KB             40 KB
    sysmail_query_transfer                                       0           360 KB             16 KB
    sysmultiobjrefs                                              2480        128 KB             120 KB
    sysschobjs                                                   686         104 KB             192 KB
    sysobjkeycrypts                                              618         184 KB             16 KB
    sysidxstats                                                  411         64 KB              64 KB
    sysrowsetcolumns                                             1502        96 KB              16 KB
    syshobtcolumns                                               1502        96 KB              16 KB
    sysjobactivity                                               288         112 KB             16 KB
    sysjobsteps                                                  14          16 KB              48 KB
    sysjobs                                                      10          8 KB               56 KB
    syscerts                                                     2           8 KB               56 KB
    sysiscols                                                    567         40 KB              16 KB
    syssingleobjrefs                                             274         16 KB              32 KB
    sysrts                                                       1           8 KB               40 KB
    sysscalartypes                                               27          8 KB               40 KB
    sysowners                                                    22          8 KB               40 KB
    restorehistory                                               0           8 KB               40 KB
    sysdtspackagefolders90                                       2           8 KB               40 KB
    MSagent_parameters                                           253         32 KB              16 KB
    MSdbms_map                                                   248         24 KB              16 KB
    MSdbms_datatype_mapping                                      325         24 KB              16 KB
    sysprivs                                                     302         24 KB              16 KB
    sysallocunits                                                255         24 KB              16 KB
    sysrowsets                                                   218         16 KB              16 KB
    syshobts                                                     218         16 KB              16 KB
    sysnsobjs                                                    1           8 KB               24 KB
    sysclsobjs                                                   23          8 KB               24 KB
    sysxmlplacement                                              17          8 KB               24 KB
    sysqnames                                                    91          8 KB               24 KB
    sysxmlcomponent                                              93          8 KB               24 KB
    sysbinobjs                                                   28          8 KB               24 KB
    sysjobservers                                                10          8 KB               24 KB
    syssubsystems                                                11          8 KB               24 KB
    syssessions                                                  99          8 KB               24 KB
    sysdtscategories                                             3           8 KB               24 KB
    MSdistpublishers                                             1           8 KB               24 KB
    sysmail_account                                              1           8 KB               24 KB
    sysalerts                                                    14          8 KB               24 KB
    sysmail_profile                                              1           8 KB               24 KB
    sysoperators                                                 1           8 KB               24 KB
    sysdtspackages90                                             3           8 KB               24 KB
    sysmail_log                                                  0           8 KB               16 KB
    restorefile                                                  0           16 KB              8 KB
    sysreplicationalerts                                         0           8 KB               16 KB
    restorefilegroup                                             0           16 KB              8 KB
    sysschedules                                                 12          8 KB               8 KB
    sysdbfiles                                                   2           8 KB               8 KB
    sysxmlfacet                                                  97          8 KB               8 KB
    sysconvgroup                                                 8           8 KB               8 KB
    sysdesend                                                    8           8 KB               8 KB
    sysdercv                                                     8           8 KB               8 KB
    sysserefs                                                    255         8 KB               8 KB
    sysfiles1                                                    2           8 KB               8 KB
    MSagent_profiles                                             16          8 KB               8 KB
    MSagentparameterlist                                         99          8 KB               8 KB
    sysmail_send_retries                                         0           8 KB               8 KB
    MSreplmonthresholdmetrics                                    7           8 KB               8 KB
    MSdistributiondbs                                            1           8 KB               8 KB
    MSdistributor                                                1           8 KB               8 KB
    sysmail_configuration                                        7           8 KB               8 KB
    sysmail_server                                               1           8 KB               8 KB
    sysjobschedules                                              10          8 KB               8 KB
    syscategories                                                21          8 KB               8 KB
    sysmaintplan_subplans                                        3           8 KB               8 KB
    sysmail_principalprofile                                     1           8 KB               8 KB
    syscachedcredentials                                         1           8 KB               8 KB
    sysdbmaintplans                                              1           8 KB               8 KB
    sysmail_profileaccount                                       1           8 KB               8 KB
    MSdbms                                                       7           8 KB               8 KB
    MSdbms_datatype                                              141         8 KB               8 KB
    sysmail_servertype                                           1           8 KB               8 KB
    sysdbmaintplan_jobs                                          0           0 KB               0 KB
    systaskids                                                   0           0 KB               0 KB
    systargetservers                                             0           0 KB               0 KB
    systargetservergroups                                        0           0 KB               0 KB
    systargetservergroupmembers                                  0           0 KB               0 KB
    sysnotifications                                             0           0 KB               0 KB
    sysdbmaintplan_history                                       0           0 KB               0 KB
    sysdbmaintplan_databases                                     0           0 KB               0 KB
    log_shipping_secondaries                                     0           0 KB               0 KB
    sysmail_attachments                                          0           0 KB               0 KB
    log_shipping_primaries                                       0           0 KB               0 KB
    sysproxies                                                   0           0 KB               0 KB
    sysdtslog90                                                  0           0 KB               0 KB
    sysmail_attachments_transfer                                 0           0 KB               0 KB
    sysftinds                                                    0           0 KB               0 KB
    systypedsubobjs                                              0           0 KB               0 KB
    sysxprops                                                    0           0 KB               0 KB
    sysrowsetrefs                                                0           0 KB               0 KB
    sysremsvcbinds                                               0           0 KB               0 KB
    sysxmitqueue                                                 0           0 KB               0 KB
    sysguidrefs                                                  0           0 KB               0 KB
    sysdtstasklog                                                0           0 KB               0 KB
    log_shipping_monitor_alert                                   0           0 KB               0 KB
    sysjobstepslogs                                              0           0 KB               0 KB
    sysdtspackagelog                                             0           0 KB               0 KB
    sysdtssteplog                                                0           0 KB               0 KB
    sysproxysubsystem                                            0           0 KB               0 KB
    log_shipping_primary_databases                               0           0 KB               0 KB
    log_shipping_monitor_secondary                               0           0 KB               0 KB
    log_shipping_secondary_databases                             0           0 KB               0 KB
    sysproxylogin                                                0           0 KB               0 KB
    logmarkhistory                                               0           0 KB               0 KB
    sqlagent_info                                                0           0 KB               0 KB
    suspect_pages                                                0           0 KB               0 KB
    sysdownloadlist                                              0           0 KB               0 KB
    sysasymkeys                                                  0           0 KB               0 KB
    syssqlguides                                                 0           0 KB               0 KB
    sysbinsubobjs                                                0           0 KB               0 KB
    sysoriginatingservers                                        0           0 KB               0 KB
    log_shipping_primary_secondaries                             0           0 KB               0 KB
    sysdtspackages                                               0           0 KB               0 KB
    log_shipping_monitor_primary                                 0           0 KB               0 KB
    log_shipping_monitor_history_detail                          0           0 KB               0 KB
    log_shipping_monitor_error_detail                            0           0 KB               0 KB
    log_shipping_secondary                                       0           0 KB               0 KB


    Monday, February 8, 2010 10:50 AM

All replies