April 14, 2006 at 2:27 pm
Hi,
When i use sp_add_maintenance_plan - the maintenance plan gets created in msdb. The field max_hisotry_rows shows 0. IF i want this field to be 100 - how do i do it through query analyzer or in the sp_add_maintenance_plan.
I can set it to 100 - if i use the maintenance wizard.
Any suggestions....
Thnx.
April 17, 2006 at 8:00 am
This was removed by the editor as SPAM
April 17, 2006 at 8:27 am
Can you update the table?
Doesn't look like the system procedures are really detailed.
April 17, 2006 at 9:07 am
April 17, 2006 at 11:14 am
Hi,
I have the following code:
declare @new_plan_id uniqueidentifier
declare @new_command nvarchar(1000)
exec msdb.dbo.sp_add_maintenance_plan 'System DB Maint',@plan_id=@new_plan_id output
exec msdb.dbo.sp_add_maintenance_plan_db @new_plan_id, 'All System Databases'
/* entry for max_history_rows field. */
IF EXISTS (SELECT *
FROM msdb.dbo.sysdbmaintplans
WHERE plan_name='System DB Maint')
BEGIN
update msdb.dbo.sysdbmaintplans
set max_history_rows =1000
where plan_id = @new_plan_id
END
/* followed by agent job creations */
When i execute the above - all code part goes through fine but i get this msg.:
Server: Msg 14262, Level 16, State 1, Procedure sp_add_maintenance_plan_db, Line 21
[Microsoft][ODBC SQL Server Driver][SQL Server]The specified @db_name ('All System Databases') does not exist.
When using the data maintenance plan wzd - it shows name, databases, servers, actions. Through my code - i get all displayed except the databases - where i want it to show All System Databases
Any idea as to why i get the above err msg.
Thnx.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply