December 16, 2003 at 8:46 am
SQL7
My msdb is 400MB.
I ran sp_delete_backuphistory which ran about 2 hours.
The size of msdb only shrank to 379MB.
Is there any other maintenance or tables within msdb that I need to look at to reduce this more?
Thanks in advance
December 16, 2003 at 8:56 am
Do you have replication set up?
Log shipping?
MAster/Target Servers?
December 16, 2003 at 9:04 am
Check how many records in following tables.
sysjobhistory
restorehistory
sysdbmaintplan_history
December 16, 2003 at 11:09 am
NO - Do you have replication set up?
NO - Log shipping?
NO - MAster/Target Servers?
Check how many records in following tables.
862 - sysjobhistory
0 - restorehistory
1,149 - sysdbmaintplan_history
December 16, 2003 at 11:48 am
Do you create many DTS or large DTS packages?
Run sp_spaceused @updateusage = true to MSDB and check the size again.
December 16, 2003 at 12:07 pm
Do you create many DTS or large DTS packages?
We have 188 DTS packages most of them small.
December 16, 2003 at 12:58 pm
Allen you got me thinking.
I am looking at the number of versions of these DTS packages and some of them number over 30. I am deleting all but 2 versions of almost everything and now I can see the amount of used space shrinking.
Thanks.
Randy
December 16, 2003 at 1:12 pm
Do you mean the physical size of the database or the amount of data within the database?
If you use sp_helpfile within your database this will give you the files used within your database.
You can then use DBCC SHRINKFILE(<<filename>>,<<suitable size>>)
If you try and shrink a 20Mb containing 15Mb of data to 10Mb then the command won't shrink the file below 15Mb so it won't wreck you server.
========================
He was not wholly unware of the potential lack of insignificance.
December 16, 2003 at 1:38 pm
Do you mean the physical size of the database or the amount of data within the database?
I was referring to the data within. I backed up the database and shrank it.
Thank you.
Life is good.
December 17, 2003 at 9:24 am
run this:
dbcc shrinkdatabase (msdb)
exec msdb..sp_spaceused @updateusage = true
if size of msdb still do:
1- run
select 'exec msdb..sp_spaceused ' + '''' + name + ''''
from msdb..sysobjects
where type like 'u'
order by name
2- run results
3- check rows for tables (especially sysdtspackagelog, sysdtssteplog)
4- run any script that delete by one record and backup the log transaction for each record, regardless log full
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply