February 22, 2011 at 3:00 am
Iam trying this in sql server 2008.
I have created the maintaince plan using the below script.
--*
-- Script generated on 3/15/2007 10:28 AM
-- Optimizations Job
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'LDR Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'LDR Database Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Optimizations Job for LDR Maintenance Plan')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Optimizations Job for LDR Maintenance Plan'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Optimizations Job for LDR Maintenance Plan'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Optimizations Job for LDR Maintenance Plan', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'LDR Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'EXECUTE master.dbo.xp_sqlmaint N''-Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\LDR Maintenance Plan0.txt" -DelTxtRpt 2WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 2048 10 ''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 20070315, @active_start_time = 10000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
-- Script generated on 3/15/2007 10:26 AM
-- Integrity Checks Job
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'LDR Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'LDR Database Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Integrity Checks Job for LDR Maintenance Plan')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Integrity Checks Job for LDR Maintenance Plan'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Integrity Checks Job for LDR Maintenance Plan'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Integrity Checks Job for LDR Maintenance Plan', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'LDR Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'EXECUTE master.dbo.xp_sqlmaint N''-Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\LDR Maintenance Plan2.txt" -DelTxtRpt 2WEEKS -WriteHistory -CkDBNoIdx ''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 20070315, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
-- Script generated on 3/15/2007 10:29 AM
-- DB Backup Job
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'LDR Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'LDR Database Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'DB Backup Job for LDR Maintenance Plan')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''DB Backup Job for LDR Maintenance Plan'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'DB Backup Job for LDR Maintenance Plan'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'DB Backup Job for LDR Maintenance Plan', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'LDR Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'EXECUTE master.dbo.xp_sqlmaint N''-Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\LDR Maintenance Plan4.txt" -DelTxtRpt 2WEEKS -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBNoIdx -BkUpMedia DISK -BkUpDB -UseDefDir -CrBkSubDir -BkExt "BAK"''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 20070315, @active_start_time = 20000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
-- Script generated on 3/15/2007 10:30 AM
-- Transaction Log Backup Job
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'LDR Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'LDR Database Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Transaction Log Backup Job for LDR Maintenance Plan')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Transaction Log Backup Job for LDR Maintenance Plan'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Transaction Log Backup Job for LDR Maintenance Plan'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Transaction Log Backup Job for LDR Maintenance Plan', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'LDR Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = N'EXECUTE master.dbo.xp_sqlmaint N''-Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\LDR Maintenance Plan6.txt" -DelTxtRpt 2WEEKS -WriteHistory -BkUpOnlyIfClean -CkDBNoIdx -BkUpMedia DISK -BkUpLog -UseDefDir -CrBkSubDir -BkExt "TRN"''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 20070315, @active_start_time = 0, @freq_interval = 126, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
-- create maintenance plan
use msdb
go
declare @LDR_plan_id uniqueidentifier
select @LDR_plan_id = plan_id from sysdbmaintplans where plan_name = 'LDR Maintenance Plan'
if @LDR_plan_id is not null
exec sp_delete_maintenance_plan @LDR_plan_id
-- create LDR Maintenance Plan
exec sp_add_maintenance_plan N'LDR Maintenance Plan', @plan_id = @LDR_plan_id output
-- Add database to LDR Maintenance plan
exec sp_add_maintenance_plan_db @LDR_plan_id, 'LDR'
-- Add jobs to LDR Maintenance plan
declare @LDR_job_id uniqueidentifier
declare jobs_cursor cursor for
select sj.job_id from sysjobs sj , syscategories sc where sj.category_id = sc.category_id and sc.name = 'LDR Database Maintenance'
open jobs_cursor
fetch next from jobs_cursor
into @LDR_job_id
while @@fetch_status = 0
begin
exec sp_add_maintenance_plan_job @LDR_plan_id, @LDR_job_id
fetch next from jobs_cursor
into @LDR_job_id
end
close jobs_cursor
deallocate jobs_cursor
declare @sql varchar(1000)
-- Update Optimizations Job step
set @sql = N'EXECUTE master.dbo.xp_sqlmaint N''-Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\LDR Maintenance Plan0.txt" -DelTxtRpt 2WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 2048 10 -PlanID ' + cast(@LDR_plan_id as varchar(50)) + ''''
exec sp_update_jobstep @job_name = 'Optimizations Job for LDR Maintenance Plan', @step_id = 1, @command = @sql
-- Update Integrity Checks Job step
set @sql = N'EXECUTE master.dbo.xp_sqlmaint N''-Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\LDR Maintenance Plan2.txt" -DelTxtRpt 2WEEKS -WriteHistory -CkDBNoIdx -PlanID ' + cast(@LDR_plan_id as varchar(50)) + ''''
exec sp_update_jobstep @job_name = N'Integrity Checks Job for LDR Maintenance Plan', @step_id = 1, @command = @sql
-- Update DB Backup Job step
set @sql = N'EXECUTE master.dbo.xp_sqlmaint N''-Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\LDR Maintenance Plan4.txt" -DelTxtRpt 2WEEKS -WriteHistory -BkUpOnlyIfClean -CkDBNoIdx -BkUpMedia DISK -BkUpDB -UseDefDir -CrBkSubDir -BkExt "BAK" -PlanID ' + cast(@LDR_plan_id as varchar(50)) + ''''
exec sp_update_jobstep @job_name = N'DB Backup Job for LDR Maintenance Plan', @step_id = 1, @command = @sql
-- Update Transaction Log Backup Job step
set @sql = N'EXECUTE master.dbo.xp_sqlmaint N''-Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\LDR Maintenance Plan6.txt" -DelTxtRpt 2WEEKS -WriteHistory -BkUpOnlyIfClean -CkDBNoIdx -BkUpMedia DISK -BkUpLog -UseDefDir -CrBkSubDir -BkExt "TRN" -PlanID ' + cast(@LDR_plan_id as varchar(50)) + ''''
exec sp_update_jobstep @job_name = N'Transaction Log Backup Job for LDR Maintenance Plan', @step_id = 1, @command = @sql
----------------------------------------------------------------------------------------------
when i execute a job manually it show me the below error messages
Message
Executed as user: test/tes123. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:14:17 PM Progress: 2011-02-22 15:14:19.15 Source: {954B7F7A-BDD6-4515-87AD-E3FD3DA0EDD3} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2011-02-22 15:14:19.36 Code: 0xC002F210 Source: Delete Old Report Files Execute SQL Task Description: Executing the query "EXECUTE master.dbo.xp_delete_file 1,N'C:\Program F..." failed with the following error: "xp_delete_file() returned error 2, 'The system cannot find the file specified.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:14:17 PM Finished: 3:14:19 PM Elapsed: 1.872 seconds. The package execution failed. The step failed.
February 22, 2011 at 4:50 am
Snippet of error
Delete Old Report Files Execute SQL Task Description: Executing the query "EXECUTE master.dbo.xp_delete_file 1,N'C:\Program F..." failed with the following error: [highlight=#ffff11]"xp_delete_file() returned error 2, 'The system cannot find the file specified.'".[/highlight]
Possible causes & solutions
-Error message is related to sub task of deleting a log file perhaps , if you are using xp_delete_file() in script check if option xp_cmdshell is enable at instance level.
- Also check if the file the script is looking for exists in system.
Note : Enabling the option give more privleage to user to issue commands to server.
Cheers
Sat
Cheer Satish 🙂
February 22, 2011 at 5:00 am
xp_cmdshell is enabled?
February 22, 2011 at 5:03 am
xp_cmdshell /
how to check it?
February 22, 2011 at 5:11 am
sp_configure 'show advanced options' , '1'
reconfigure
go
sp_configure
February 22, 2011 at 5:23 am
nameminimummaximumconfig_valuerun_value
access check cache bucket count06553600
access check cache quota0214748364700
Ad Hoc Distributed Queries0100
affinity I/O mask-2147483648214748364700
affinity mask-2147483648214748364700
affinity64 I/O mask-2147483648214748364700
affinity64 mask-2147483648214748364700
Agent XPs0111
allow updates0100
awe enabled0100
blocked process threshold (s)08640000
c2 audit mode0100
clr enabled0100
cost threshold for parallelism03276755
cross db ownership chaining0100
cursor threshold-12147483647-1-1
Database Mail XPs0100
default full-text language0214748364710331033
default language0999900
default trace enabled0111
disallow results from triggers0100
filestream access level0200
fill factor (%)010000
ft crawl bandwidth (max)032767100100
ft crawl bandwidth (min)03276700
ft notify bandwidth (max)032767100100
ft notify bandwidth (min)03276700
index create memory (KB)704214748364700
in-doubt xact resolution0200
lightweight pooling0100
locks5000214748364700
max degree of parallelism06400
max full-text crawl range025644
max server memory (MB)16214748364721474836472147483647
max text repl size (B)-121474836476553665536
max worker threads1283276700
media retention036500
min memory per query (KB)512214748364710241024
min server memory (MB)0214748364700
nested triggers0111
network packet size (B)5123276740964096
Ole Automation Procedures0100
open objects0214748364700
optimize for ad hoc workloads0100
PH timeout (s)136006060
precompute rank0100
priority boost0100
query governor cost limit0214748364700
query wait (s)-12147483647-1-1
recovery interval (min)03276700
remote access0111
remote admin connections0100
remote login timeout (s)021474836472020
remote proc trans0100
remote query timeout (s)02147483647600600
Replication XPs0100
scan for startup procs0100
server trigger recursion0111
set working set size0100
show advanced options0111
SMO and DMO XPs0111
SQL Mail XPs0100
transform noise words0100
two digit year cutoff1753999920492049
user connections03276700
user options03276700
xp_cmdshell0100
February 22, 2011 at 5:26 am
mhdmehraj (2/22/2011)
nameminimummaximumconfig_valuerun_valuexp_cmdshell0100
there is youre answer
its not enabled
sp_configure 'xp_cmdshell' , '1'
reconfigure
go
will enable it
February 22, 2011 at 5:33 am
What i get from this result?
February 22, 2011 at 5:36 am
it will enable xp_cmdshell
you will notice that if you run sp_configure again.
probably you re query will run then correct as now xp_cmdshell features are disabled
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply