msdb db move - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

msdb db move

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-11-17 : 10:49:29

Dear All,

I am DBA for production server DB with SQL server 2005 Enteprise Edition,as i am new to system DB file move.

I want to move msdb data and log files from C:\ drive to E:\ drive
without system offline.

I have scheduled job of around 100-120 jobs but the size of my msdb database is very huge..!! what would be the size of the msdb database ??

My msdb size: msdbdata.mdf --> 2.04 GB
msdblog.ldf ---> 2.7 GB

Thanks
Gangadhar'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-17 : 11:19:41

Here you go: http://support.microsoft.com/kb/224071

You likely need to cleanup msdb's job history: http://weblogs.sqlteam.com/tarad/archive/2008/06/18/How-to-delete-backup-and-restore-history-from-msdb.aspx

You may need to cleanup Database Mail attachments too: http://weblogs.sqlteam.com/tarad/archive/2008/04/28/60575.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-11-17 : 21:20:28

Hi Tara,
Thanks.
I ran this procedure in SQl server 2005.

But my msdb size didn't come down still its having size as earlier.

create PROCEDURE [dbo].[Delete_Backup_History_new]
(@days_to_retain int = 4)
AS
BEGIN
SET NOCOUNT ON

DECLARE @Err int
DECLARE @rc int
DECLARE @target_date datetime

SET @target_date = GETDATE() - @days_to_retain

BEGIN TRAN

DELETE FROM msdb..restorefile
FROM msdb..restorefile rf
INNER JOIN msdb..restorehistory rh ON rf.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..restorefilegroup
FROM msdb..restorefilegroup rfg
INNER JOIN msdb..restorehistory rh ON rfg.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..restorehistory
FROM msdb..restorehistory rh INNER JOIN msdb..backupset bs
ON rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

SELECT media_set_id, backup_finish_date
INTO #Temp
FROM msdb..backupset
WHERE backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupfilegroup
FROM msdb..backupfilegroup bfg
INNER JOIN msdb..backupset bs ON bfg.backup_set_id = bs.backup_set_id
INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupfile
FROM msdb..backupfile bf
INNER JOIN msdb..backupset bs ON bf.backup_set_id = bs.backup_set_id
INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupset
FROM msdb..backupset bs
INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id
WHERE bs.backup_finish_date < @target_date

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupmediafamily
FROM msdb..backupmediafamily bmf
INNER JOIN msdb..backupmediaset bms ON bmf.media_set_id = bms.media_set_id
INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
WHERE NOT EXISTS (SELECT media_set_id FROM msdb..backupset bs
WHERE bs.media_set_id = t.media_set_id
AND bs.backup_finish_date >= @target_date)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

DELETE FROM msdb..backupmediaset
FROM msdb..backupmediaset bms
INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id
WHERE NOT EXISTS (SELECT media_set_id FROM msdb..backupset bs
WHERE bs.media_set_id = t.media_set_id
AND bs.backup_finish_date >= @target_date)

SET @Err = @@ERROR

IF @Err <> 0
GOTO Error_Exit

COMMIT TRAN

SET @rc = 0

GOTO isp_DeleteBackupHistory_Exit

Error_Exit:

ROLLBACK TRAN

SET @rc = -1

isp_DeleteBackupHistory_Exit:

DROP TABLE #Temp

SET NOCOUNT OFF

RETURN @rc
END

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-11-17 : 21:37:19

After running above msdb job history cleaning and database mail clean up i didn't get any disk space cleared from msdb,could you please suggest.

Thanks,
Gangadhar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-17 : 23:19:55

You have to run DBCC SHRINKFILE after you've purged the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-11-18 : 03:41:07

As per the general thumb rule we should't prepare to shrink the DB files unless until its really required,do we have any other options to get freee the DB space.

Thanks,
Gangadhar

Kristen
Test

22859 Posts

Posted - 2010-11-18 : 07:03:48

No. This is one of those "really required" cases. There was run-away expansion of the file. You have corrected that, and deleted the extraneous data, so now the file needs to be shrunk back to appropriate size. You should not do this often - so you need to prevent the run-away expansion happening again (e.g. by scheduling the housekeeping / cleanup task) - rather than shrinking it again in a short while

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-18 : 12:38:58

And to add to Kristen's post, the only want to reduce the file size is to shrink them using DBCC.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources