August 6, 2010 at 10:43 am
Hello,
when I notice the space issue on my database drive I notice that MSDB Database is over 31 GB.
Database in Simple Mode,
If I get assistance How to reduce the size, it will greatly appreciated,
Dev
August 6, 2010 at 11:19 am
I would start by checking what is in there making it that size.
That is unusual to say the least.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 6, 2010 at 11:22 am
Do you have an abnormally huge number of SSIS packages?
Preform a Full backup, and that should truncate the log file for you.
How often do you take a Full backup of this Database?
Andrew SQLDBA
August 6, 2010 at 11:49 am
In SSMS, right click on the msdb database. Click Reports > Standard Reports > Disk Usage by Top Tables. That should tell you what's taking up so much space.
I believe certain things log to msdb, including agent job histories. There's a maintenance plan task that can clean those up.
August 6, 2010 at 12:00 pm
I get this info,
dbo. sysssispackages # of records = 20 Data 9544 KB
dbo.sysmail_attachments # of records = 18 Data 4064 KB
dbo.sysjobhistory # of records = 1000 Data = 2184
These are the top 3 please
August 6, 2010 at 12:04 pm
That is actually about normal.
Are you sure this is the size of the Data file, and not the Log file? Look on the drive, and see what one is the large one.
How often do you backup this database?
Andrew SQLDBA
August 6, 2010 at 12:10 pm
When I see the Disk Usage Report for msdb database, the below is what I get,
Total Space Usage 31,051 MB
Data Files Space Usage 31046 MB
Transaction Log Space Usage 4.56 MB
I have no room to do the backup as my drives do not have that much space left but all the backup is done to the Tapes using symantec
August 6, 2010 at 12:19 pm
So your top tables are measured in megs? Are you sure you're reading it right? I can't think of much other than a table that would take up 32G like that. Any of them have anything crazy in any of the other columns (reserved, indexes, unused)? Anything have a high number of records?
Any chance you can export that report and attach it?
August 6, 2010 at 12:26 pm
Run this query
And show the results
SELECT
type_desc AS FileType
,name AS DatabaseName
,size AS DatabaseSize
, size/1024 AS MBSize
,physical_name
FROM
master.sys.master_files
WHERE
name LIKE 'msdb%'
ORDER BY
name ASC
Andrew SQLDBA
August 6, 2010 at 12:30 pm
ROWSMSDBData39739763880D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf
LOGMSDBLog5840D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf
August 6, 2010 at 12:54 pm
OK, this script will give you a row count per table: Lets see what table(s) may be causing this.
USE MSDB
SELECT
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.data * 8 AS data,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
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
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY row_count DESC
Andrew SQLDBA
August 6, 2010 at 12:58 pm
dbosysjobactivity12962001521632
dbosysjobhistory1000338421761281080
dboMSdbms_datatype_mapping4934832160
dboMSdbms_map3745640160
dbosysmail_mailitems277136112168
dboMSdbms_datatype1803216160
dbosysjobsteps107192144480
dbosysmail_log1034024160
dbosyspolicy_facet_events848840480
dbosyspolicy_management_facets7416880
dbosysschedules653216160
dbosysjobs648824640
dbosysjobservers644816320
dbosysjobschedules58328240
dbosysssispackages2196409616240
dbosyscategories2116880
dbosyssessions21328240
dbosysmail_attachments184080406488
dbosyssubsystems12328240
dboMSdbms816880
dbosysmail_configuration716880
dbosysmail_send_retries616880
dbosysmanagement_shared_server_groups_internal5648560
dbosyscollector_config_store_internal516880
dbosysssispackagefolders5488400
dbosyscollector_collection_items_internal57240320
dbosyscollector_collector_types_internal4328240
dbosyspolicy_configuration_internal416880
dbosyscollector_collection_sets_internal3328240
dbosysmaintplan_subplans316880
dbosysoperators3328240
dbosysdtscategories3328240
dbosyscachedcredentials216880
dbosysmail_principalprofile116880
dbosysmail_profile1328240
dbosysmail_servertype116880
dbosysdbmaintplans116880
dbosysmail_server116880
dbosysmail_account1328240
dbosysmail_profileaccount116880
dbosysmaintplan_log116880
dbosysmaintplan_logdetail116880
dbosyscollector_blobs_internal196095280
dborestorehistory0328240
dbolog_shipping_secondaries00000
dbosyspolicy_policy_category_subscriptions_internal00000
dbosysmail_query_transfer016880
dborestorefilegroup016880
dbosysjobstepslogs00000
dbologmarkhistory00000
dbosuspect_pages00000
dbosysmail_attachments_transfer0241680
dbosyspolicy_policy_execution_history_internal00000
dbolog_shipping_primaries00000
dbobackupfile0248160
dbobackupfilegroup0248160
dbobackupset0568480
dbosysoriginatingservers00000
dbosyspolicy_target_set_levels_internal00000
dbosysproxies00000
dbosysdbmaintplan_history00000
dbosyscollector_execution_stats_internal00000
dbobackupmediaset0328240
dbosysproxysubsystem00000
dbosysproxylogin00000
dbobackupmediafamily0408320
dbosyspolicy_target_sets_internal00000
dbosqlagent_info00000
dbosysdownloadlist00000
dbosystaskids00000
dbosysdtssteplog00000
dbolog_shipping_primary_databases00000
dbosyspolicy_policy_categories_internal00000
dbolog_shipping_primary_secondaries00000
dbosyspolicy_conditions_internal00000
dbosysdtstasklog00000
dbosysnotifications00000
dbosysdtspackagelog00000
dbosysdtspackages00000
dborestorefile016880
dbosyspolicy_system_health_state_internal00000
dbosyscollector_tsql_query_collector00000
dbosystargetservers00000
dbosystargetservergroups00000
dbosystargetservergroupmembers00000
dbosysalerts00000
dbosyspolicy_execution_internal00000
dbosysmanagement_shared_registered_servers_internal00000
dbosyspolicy_policy_execution_history_details_internal00000
dbosysssislog00000
dbolog_shipping_monitor_alert00000
dbolog_shipping_monitor_secondary00000
dbosysdbmaintplan_jobs00000
dbolog_shipping_monitor_primary00000
dbosyspolicy_object_sets_internal00000
dbolog_shipping_monitor_history_detail00000
dbolog_shipping_monitor_error_detail00000
dbolog_shipping_secondary00000
dbolog_shipping_secondary_databases00000
dbosyspolicy_policies_internal00000
dbosysdbmaintplan_databases00000
dbosyscollector_execution_log_internal00000
August 6, 2010 at 1:02 pm
Perhaps DBCC UPDATEUSAGE might help?
August 6, 2010 at 1:12 pm
There is approx 17 Gigs in these 4 tables.
dbo syscollector_blobs_internal 1 960 952 8 0
dbo sysmail_attachments 18 4080 4064 8 8
dbo sysssispackages 21 9640 9616 24 0
dbo sysjobhistory 1000 3384 2176 128 1080
You cannot bother anything with the SSIS packages. You can remove the attachments, and delete some job history
Modify the Order By clause to only the "data" column, just to make sure that I have not missed anything.
That will give you a little bit of room. Do you not have a dedicated SSIS Server? If so, move all the SSIS packages to that box.
Andrew SQLDBA
August 6, 2010 at 1:19 pm
You may try to shrink the database. I use this only when necessary.
You may also try DBCC SHRINKFILE with the TRUNCATEONLY attribute. Read the SQL BOL for the exact syntax of both.
From what I see, that is the size for what you are storing the database.
Did you develop all the SSIS packages on that particular box?
Andrew SQLDBA
Viewing 15 posts - 1 through 15 (of 71 total)
You must be logged in to reply to this topic. Login to reply