MSDB size rocketed
-
I'm not sure how/when this happened but I've noticed the msdb size on our dev box is 770mb as opposed to 70mb on the production box. (Both log sizes are small).
Is there a way to find out the growth history.... e.g. monday 20mb, tues 20mb, weds 770mb!!
And possibly find out a reason for it?
3 things have happened on this box of late.
1. MS hotfix to allow XP SP2 clients to debug on the client.
2. Upgrade/conversion of a 3rd party DB (approx 6GB) same size as it's predecessor
3. Blue screen of death this morning! (Event viewer also claims that the drive is low on disk space... since remedied)
Thanks for thoughts.
-
Is this data, log or both? Allocated space or used space?
Could be all you need to do is shrink the log file.
--------------------
Colt 45 - the original point and click interface
-
msdb has jobs, dts packages, and replications system data.
Has someone scheduled some jobs with high running frequency?-- select * FROM msdb..sysjobs
Has someone saved lots of DTS packaes there?
Has someone setup replications or logshipping?
Another possibility is that someone selected msdb as the wrong target db and ran some scripts there.
-
Try to find out what table(s) have all this data. An extra 500MB should be easy to spot. I looked around at a number of my servers and can't find an msdb table over 2.6MB.
Msdb contains backup history which may need to be cleaned up, but it is hard to imagine that this could suddenly grow unless you started doing log backups every 5 seconds. Maintenance plan and other job history is also stored in msdb, but again I can't see this suddenly jumping by 100's of MB. Ditto with log shipping plan history.
If someone has been very busy editing and saving DTS packages it would cause some bloat. All previous versions of packages are saved, kind of like SourceSafe.
Finally, look for tables that don't belong in msdb.
-
Thanks all, I answer all (the ones I can) here...
Not the log, the log is only 450k.
MSDBData is (used space) 779MB!!
I've run select * FROM msdb..sysjobs and it looks as it should - same as the live box.
Have checked the objects in MSDB and the tables/sprocs etc all look to be system ones.
No replication or log shipping set up (to my knowledge) how do I doiuble check this?
Is there a way to loop through all the tables in a DB to get the size of each?
For each table in msdn
select table.size
next
sort of thing??
-
Here's a quick way.
EXEC sp_MSforeachtable 'sp_spaceused @objname =''?'''
--------------------
Colt 45 - the original point and click interface -
Thanks Phil.
To my (semi untrained eye) that hasn't unearthed a HDD stealing culprit.
Here is the results of running that for MSDN.
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblIfaceMem 1189 64 KB 48 KB 16 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblWorkspaceItems 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblDatabaseVersion 1 24 KB 8 KB 16 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblDTSProps 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblVersionAdminInfo 2333 192 KB 176 KB 16 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblParameterDef 136 32 KB 16 KB 16 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblClassExtension 69 24 KB 8 KB 16 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblIfaceHier 3349 128 KB 96 KB 16 KB 16 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblNamedObj 2803 384 KB 144 KB 160 KB 80 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblTypeInfo 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblScriptDefs 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblOLPProps 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblEnumerationDef 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblSumInfo 1 32 KB 16 KB 16 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblMDSProps 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblEnumerationValueDef 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblUMLProps 378 64 KB 24 KB 16 KB 24 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblUMXProps 378 72 KB 48 KB 16 KB 8 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblSIMProps 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblGENProps 1 24 KB 8 KB 16 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblDTMProps 7 24 KB 8 KB 16 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblDBMProps 617 136 KB 120 KB 16 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblEQMProps 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblVersions 3657 448 KB 240 KB 176 KB 32 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblDBXProps 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblRelships 8747 1472 KB 976 KB 464 KB 32 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblSites 456 64 KB 16 KB 48 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblProps 393 64 KB 16 KB 48 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblRelshipProps 28 40 KB 8 KB 32 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblPropDefs 797 64 KB 56 KB 16 KB -8 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblRelColDefs 320 64 KB 16 KB 48 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblIfaceDefs 453 104 KB 32 KB 56 KB 16 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblClassDefs 537 2608 KB 2504 KB 56 KB 48 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblTFMProps 0 0 KB 0 KB 0 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblRelshipDefs 144 24 KB 8 KB 16 KB 0 KB
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
RTblTypeLibs 17 24 KB 8 KB 16 KB 0 KB
-
Sorry, forgot that sp_MSforeachtable only iterates through user objects. Try this instead,
SELECT db_name() as db , sObj.[name] as TblName , sInd.[name] as IndName , CONVERT(int, sInd.rowcnt) as [RowCnt] , ((ISNULL(SumDP.DP, 0) + ISNULL(SumTx.TxUsed, 0)) * pg.PageSize) as [DataSize KB] , ((ISNULL(SumIn.InUsed, 0) - (ISNULL(SumDP.DP, 0) + ISNULL(SumTx.TxUsed, 0))) * pg.PageSize) as [IndexSize KB] FROM dbo.sysindexes sInd INNER JOIN dbo.sysobjects sObj ON sInd.[id] = sObj.[id] LEFT JOIN (SELECT [id], sum(dpages) as DP FROM dbo.sysindexes WHERE indid < 2 GROUP BY [id]) as SumDP ON sInd.[id] = SumDP.[id] LEFT JOIN (SELECT [id], isnull(sum(used), 0) as TxUsed FROM dbo.sysindexes WHERE indid = 255 GROUP BY [id]) as SumTx ON sInd.[id] = SumTx.[id] LEFT JOIN (SELECT [id], sum(used) as InUsed FROM dbo.sysindexes WHERE indid in (0, 1, 255) GROUP BY [id]) as SumIn ON sInd.[id] = SumIn.[id] , (SELECT v.[low] / 1024 as PageSize FROM master..spt_values v WHERE v.number=1 and v.type=N'E') as Pg WHERE sInd.indid < 2 AND sInd.rowcnt > 0 ORDER BY [DataSize KB] DESC, [IndexSize KB] DESC, sObj.[name], sInd.[name]
--------------------
Colt 45 - the original point and click interface -
Thanks again Phil,
That's throwing an error around...
ON sInd.[id] = SumIn.[id]
, (SELECT v.[low] / 1024 as PageSize
FROM master..spt_values v
WHERE v.number=1 and v.type=N'E') as Pg
WHERE sInd.indid < 2
AND sInd.rowcnt > 0
ORDER BY
[DataSize KB] DESC, [IndexSize KB] DESC, sObj.[name], sInd.[name]
Is there a join missing toward the end?
In the meantime I'll work through it and try and solve.
-
Cancel that! Was missing a space between 2 words.
Results show....
master syscomments syscomments 2168 7904 48
master syscolumns syscolumns 4956 1472 872
master sysmessages sysmessages 3769 1312 16
master HG_SpatialRef PK__HG_SpatialRef__77017CD9 951 688 80
master HG_SpatialRef_Saved PK__HG_SpatialRef__0C31A3E9 951 688 80
master sysobjects sysobjects 1421 352 272
master syscharsets csyscharsets 114 264 32
master sysindexes sysindexes 110 256 16
master sysdepends sysdepends 5545 232 200
master sysaltfiles sysaltfiles 91 176 16
master spt_values spt_valuesclust 728 56 80
master sysxlogins sysxlogins 197 40 48
master syspermissions syspermissions 896 32 16
master syslanguages csyslanguages 33 24 48
master sysdatabases sysdatabases 46 16 32
master SQL_Statistics SQL_Statistics 96 16 8
master sysusers sysusers 16 8 48
master sysfilegroups sysfilegroups 1 8 32
master sysservers csysservers 4 8 32
master systypes systypes 26 8 32
master spt_datatype_info datatypeinfoclust 36 8 16
master spt_datatype_info_ext datatypeinfoextclust 10 8 16
master spt_provider_types datatypeinfoclust 25 8 16
master spt_server_info serverinfoclust 29 8 16
master sysconfigures sysconfigures 38 8 16
master sysdevices sysdevices 6 8 16
master HG_OneRow HG_OneRow 1 8 8
master MSreplication_options MSreplication_options 2 8 8
master spt_monitor spt_monitor 1 8 8
master sysfiles1 sysfiles1 2 8 8
Apologies if that's hard to read....
-
Make sure you run it in the msdb database. As shown by the output, this list is from the master database.
Also, just a tip, I wouldn't be letting anyone from MS see this list. Those user tables in the master database could void any support warranty.
--------------------
Colt 45 - the original point and click interface -
Doh! I did mean to run it in MSDB but my fried brain wouldn't let me!
OK this appears to be the culprit:
MSDB sysdtspackages pk_dtspackages 1217 657136 208
MSDB sysdtssteplog PK__sysdtssteplog__245D67DE 121237 30992 384
I wonder what makes it so large? Someone saved data with a package or something? </guess>
Which tables in master were non-user?
I've inherited the DBA role here and the instances were already installed when I did.
Continued thanks
-
sorry, ignore the Q asking which were non-user.
It occured to me to look(!!)
-
What's probably happening with the DTS packages is that you've got more than one version for each package. Every time you save a package, even if you don't make any changes, it creates another version of the whole package. This will list out how many versions are stored for each package.
SELECT [name], COUNT(CAST([versionid] as varchar(35))) as [NumVersions] FROM [msdb].[dbo].[sysdtspackages] GROUP BY [name] ORDER BY [NumVersions] DESC
For the sysdtssteplog table, you've probably got package logs that are ages old. You should probably decide how long you want to keep the logs for and clear out the older ones. You can do this using the sp_dump_dtspackagelog procedure and it will remove the logs from sysdtspackagelog, sysdtssteplog and sysdtstasklog. Each log is assigned a unique lineage value, so you pass this into the procedure to remove that individual log record.
--------------------
Colt 45 - the original point and click interface -
234 versions!!!
Is there a way to weed out the intial 233 versions?
This is a feature/annoyance(?) of DTS I was not aware of.
Is there an ability to roll back to previous versions (ala Visual source safe)? If not then why would it retain these versions?
This would explain the difference between live and dev msdb sizes as all changes/testing are done on dev and the finished article copied top live.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply