msdb - in need of space
-
msdb is way too big, almost 30GB! database mail is often used so I tried clearing some of the database mail logs by using, sysmail_delete_mailitems_sp
When I did this, the log file grew! so I stopped it and ran, sysmail_delete_log_sp but it didn't cut it down in size much.
fyi- I also deleted all history past the last 2 weeks using the procedures below:
declare @dt datetime select @dt = cast(N'2007-05-28T14:42:38' as datetime)
exec msdb.dbo.sp_delete_backuphistory @dt
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2007-05-28T14:42:38'
EXECUTE msdb..sp_maintplan_delete_log null,null,'2007-05-28T14:42:38')
Any suggestions?
-
go table by table and see which one has the most rows and why
-
Do you backup the MSDB on a regular basis? Which recovery mode does it use - if it is FULL, are you also doing transaction log backups?
-SQLBill
-
no backups of msdb or transaction log backups. (no need, the data relies on new data being replicated over nightly from another server)
-
Depending on what data you actualy replicate in you msdb, you would need to run sp_delete_backuphistory.
If it's not the case then just make sure the recovery model is set to full, take a full backup, then shrink the db files or the entire db; then you can set back the recovery model to what it was before. You may need to repeat this once per week let's say, depending on you msdb growth rate.
bdw you don't need to keep the above backup if you are not going to use it.
-
You don't need to set the recovery mode to full to make a full backup. If you aren't doing transaction log backups, set the recovery mode to simple. That lets SQL Server handle the issue of the log file size. If you set the database to Full recovery mode, you need to do transaction log backups or the tlog file will grow and grow and grow.
-SQLBill
-
I have them all marked as simple recovery on that server.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply