April 8, 2009 at 7:22 am
ok my dev machine I was running low on space to restore a gig plus backup file, so I'm looking to cleanup extra space; old .BAK files, etc.
I see the msdb.mdf file is bloated to over 4 gig of space. I don't have any jobs or SSIS packages, so there's nothing i can think of that eats that much space.
properties says it's 4.2 gig with 0.09Mb available space, so it's not that there's a lot of reserved space...it used somewhere (in theory).
i made a cursor for sp_spaceused, and the results are below; there is not much in any of hte tables...300 rows max for the most part.
where do i look next for why my msdb database is so huge?
name rows reserved data index_size unused
MSdbms 7 16 KB 8 KB 8 KB 0 KB
MSdbms_datatype 141 16 KB 8 KB 8 KB 0 KB
MSdbms_datatype_mapping 325 40 KB 24 KB 16 KB 0 KB
MSdbms_map 248 40 KB 24 KB 16 KB 0 KB
backupfile 135 64 KB 48 KB 16 KB 0 KB
backupfilegroup 67 16 KB 8 KB 8 KB 0 KB
backupmediafamily 49 32 KB 8 KB 24 KB 0 KB
backupmediaset 49 32 KB 8 KB 24 KB 0 KB
backupset 67 72 KB 40 KB 32 KB 0 KB
log_shipping_monitor_alert 0 0 KB 0 KB 0 KB 0 KB
log_shipping_monitor_error_detail 0 0 KB 0 KB 0 KB 0 KB
log_shipping_monitor_history_detail 0 0 KB 0 KB 0 KB 0 KB
log_shipping_monitor_primary 0 0 KB 0 KB 0 KB 0 KB
log_shipping_monitor_secondary 0 0 KB 0 KB 0 KB 0 KB
log_shipping_primaries 0 0 KB 0 KB 0 KB 0 KB
log_shipping_primary_databases 0 0 KB 0 KB 0 KB 0 KB
log_shipping_primary_secondaries 0 0 KB 0 KB 0 KB 0 KB
log_shipping_secondaries 0 0 KB 0 KB 0 KB 0 KB
log_shipping_secondary 0 0 KB 0 KB 0 KB 0 KB
log_shipping_secondary_databases 0 0 KB 0 KB 0 KB 0 KB
logmarkhistory 0 0 KB 0 KB 0 KB 0 KB
restorefile 246 136 KB 72 KB 8 KB 56 KB
restorefilegroup 122 64 KB 56 KB 8 KB 0 KB
restorehistory 122 48 KB 16 KB 32 KB 0 KB
sqlagent_info 0 0 KB 0 KB 0 KB 0 KB
suspect_pages 0 0 KB 0 KB 0 KB 0 KB
sysalerts 0 0 KB 0 KB 0 KB 0 KB
syscachedcredentials 0 0 KB 0 KB 0 KB 0 KB
syscategories 21 16 KB 8 KB 8 KB 0 KB
sysdbmaintplan_databases 0 0 KB 0 KB 0 KB 0 KB
sysdbmaintplan_history 0 0 KB 0 KB 0 KB 0 KB
sysdbmaintplan_jobs 0 0 KB 0 KB 0 KB 0 KB
sysdbmaintplans 1 16 KB 8 KB 8 KB 0 KB
sysdownloadlist 0 0 KB 0 KB 0 KB 0 KB
sysdtscategories 3 32 KB 8 KB 24 KB 0 KB
sysdtslog90 0 0 KB 0 KB 0 KB 0 KB
sysdtspackagefolders90 2 48 KB 8 KB 40 KB 0 KB
sysdtspackagelog 0 0 KB 0 KB 0 KB 0 KB
sysdtspackages 0 0 KB 0 KB 0 KB 0 KB
sysdtspackages90 0 0 KB 0 KB 0 KB 0 KB
sysdtssteplog 0 0 KB 0 KB 0 KB 0 KB
sysdtstasklog 0 0 KB 0 KB 0 KB 0 KB
sysjobactivity 0 0 KB 0 KB 0 KB 0 KB
sysjobhistory 0 0 KB 0 KB 0 KB 0 KB
sysjobs 0 0 KB 0 KB 0 KB 0 KB
sysjobschedules 0 0 KB 0 KB 0 KB 0 KB
sysjobservers 0 0 KB 0 KB 0 KB 0 KB
sysjobsteps 0 0 KB 0 KB 0 KB 0 KB
sysjobstepslogs 0 0 KB 0 KB 0 KB 0 KB
sysmail_account 0 0 KB 0 KB 0 KB 0 KB
sysmail_attachments 0 0 KB 0 KB 0 KB 0 KB
sysmail_attachments_transfer 0 0 KB 0 KB 0 KB 0 KB
sysmail_configuration 7 16 KB 8 KB 8 KB 0 KB
sysmail_log 0 0 KB 0 KB 0 KB 0 KB
sysmail_mailitems 0 0 KB 0 KB 0 KB 0 KB
sysmail_principalprofile 0 0 KB 0 KB 0 KB 0 KB
sysmail_profile 0 0 KB 0 KB 0 KB 0 KB
sysmail_profileaccount 0 0 KB 0 KB 0 KB 0 KB
sysmail_query_transfer 0 0 KB 0 KB 0 KB 0 KB
sysmail_send_retries 0 0 KB 0 KB 0 KB 0 KB
sysmail_server 0 0 KB 0 KB 0 KB 0 KB
sysmail_servertype 1 16 KB 8 KB 8 KB 0 KB
sysmaintplan_log 0 0 KB 0 KB 0 KB 0 KB
sysmaintplan_logdetail 0 0 KB 0 KB 0 KB 0 KB
sysmaintplan_subplans 0 0 KB 0 KB 0 KB 0 KB
sysnotifications 0 0 KB 0 KB 0 KB 0 KB
sysoperators 0 0 KB 0 KB 0 KB 0 KB
sysoriginatingservers 0 0 KB 0 KB 0 KB 0 KB
sysproxies 0 0 KB 0 KB 0 KB 0 KB
sysproxylogin 0 0 KB 0 KB 0 KB 0 KB
sysproxysubsystem 0 0 KB 0 KB 0 KB 0 KB
sysschedules 0 0 KB 0 KB 0 KB 0 KB
syssessions 9 32 KB 8 KB 24 KB 0 KB
syssubsystems 11 32 KB 8 KB 24 KB 0 KB
systargetservergroupmembers 0 0 KB 0 KB 0 KB 0 KB
systargetservergroups 0 0 KB 0 KB 0 KB 0 KB
systargetservers 0 0 KB 0 KB 0 KB 0 KB
systaskids 0 0 KB 0 KB 0 KB 0 KB
Lowell
April 8, 2009 at 7:29 am
oh and i did do my due diligence before posting; i googled and went thru all the steps found here:
http://www.mssqltips.com/tip.asp?tip=1461
but with results like 135 rows of data, nothing there jumped out at me.
Lowell
April 8, 2009 at 7:33 am
sp_spaceused includes data and log size, but not log free space..
run "dbcc sqlperf(logspace)"
your log file is probably blown up 🙂
April 8, 2009 at 7:36 am
ok that returned this info for that database:
Database Name Log Size (MB) Log Space Used (%)
msdb 0.4921875 81.74603
so the log is .49Mb, so i don't think that's the problem....weird.
Lowell
April 8, 2009 at 7:46 am
interesting, what's this return:
interesting, what's this return:
DECLARE @dataspacetemp TABLE
( FileID int,
FileGrp int,
TotExt int,
UsdExt int,
LFileNm varchar( 100),
PFileNm varchar( 1000)
)
insert @dataspacetemp
exec( 'use msdb dbcc showfilestats')
select substring( lFileNM, 1, 20) logicalFilename,
((cast( TotExt as numeric( 10, 4))* 32) / 512) totalMB,
((cast( UsdExt as numeric( 10, 4))* 32) / 512) usedMB,
((cast( TotExt - UsdExt as numeric( 10, 4))* 32) / 512) freeMB,
((((cast( TotExt - UsdExt as numeric( 10, 4))* 32) / 512) / ((cast( TotExt as numeric( 10, 4))* 32) / 512)) * 100) free_percent,
'Data' type
from @dataspacetemp
April 8, 2009 at 7:57 am
it returns this:
logicalFilenametotalMBusedMBfreeMBfree_percenttype
MSDBData4237.437500004237.437500000.000000000.00000000000000000Data
i've found some other links where they cannot explain the bloat, but the solution was to isntall an express version, stop tehs erver, adn swap out the new unbloated msdb.mdf with the oversized one.
obviously, if i had jobs, i'd need to recreate them, but i have none so it's a non issue.
Lowell
April 8, 2009 at 8:03 am
Asan FYI, the steps outlined above worked fine...stopping my regualr dev server and the new express server, renaming the dev msdb mdf and ldf files, and then replacing with the express's copies, then starting both instances back up.
kind of a pain, but i recovered 4+gig of space. I'd still like to know WHY msdb bloats like that for what i think is no reason.
Lowell
April 8, 2009 at 8:06 am
Yes, that is interesting.. what build/version of SQL are you running? I'll have to watch out for that happening.
April 8, 2009 at 8:26 am
it started out life as SQL Express and then had SQL Express With Advanced Services re-installed over the top of it:
select @@version
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)
Lowell
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply