Clean up table sysdbmaintplan_history 1

[RobertT687]

Are there procedures for cleaning out older rows in the following tables in msdb:

dbo.sysdbmaintplan_history
dbo.backupfile
dbo.backupset
dbo.backupmediafamily
dbo.backupmediaset

They seem to contain information back to the startup of the server. I don't use maintnenance plans for my backups, and don't see a need to keep more than about the last 2-3 months of this data. I don't want to break something by using issuing a Delete in ignorance.

Thanks in advance.

 

[mrdenny]

Look up "sp_delete_backuphistory" in BOL. That will take care of the backup tables. You can use the undocummented procedure "sp_clear_dbmaintplan_by_db" to the Maintenance plans. If you don't use maintenance plans then there shouldn't be anything in that table.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[RobertT687]

sp_delete_backuphistory was just what I was looking for. It works great.

I cannot find the procedure "sp_clear_dbmaintplan_by_db".
Does it exist in SQL2000? If so which database?

In any case, a star for the first one.

Many thanks.

 

[mrdenny]

It's in the msdb database. I have it on my 2000 servers.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com