Saturday, February 13, 2010
SQL Server Agent Jobs
Q) What are the sub-nodes under sql server agent ?
A)
Jobs
Job Activity Monitor
Alerts
Operators
Proxies
Error Logs
Here error logs is the error log for sql server agent and not for the sql server instance.You find the error log for server instance under management node.
Q) How to create a job ?
A)
use the following sp :
exec msdb.dbo.sp_add_job
Example :
exec msdb.dbo.sp_add_job
@job_name='backupdbkali',
@enabled=1,
@description='this takes backup of db kali',
@category_id=3,
@owner_login_name='home-bix5t88dr6\administrator',
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@notify_email_operator_name='sudarsan',
@delete_level=0.
This example job creates a job that is going to perform a backup of db kali.
Q) How to supply category_id value as input in sp_add_job stored procedure ? or How to find the existing category id's and their description ?
A)
exec msdb.dbo.sp_help_category
Q) What are the possible notify level values that can be supplied as inputs in stored procedure sp_add_job ?
A)
0-Never
1-Success
2-Failure
3-Always
Q) Job can contain multiple steps.Now how to add a step to a job ?
A) Use the following stored procedure :
exec msdb.dbo.sp_add_jobstep
Example :
exec msdb.dbo.sp_add_jobstep
@job_name='backupdbkali',
@step_id=1,
@step_name='backsup kalidb',
@subsystem='TSQL',
@command='use master go backup database kali to disk=''C:\backups\kali.bak''',
@on_success_action=1,
@on_fail_action=2,
@server='home-bix5t88dr6\csfspd1caspro',
@database_name='master',
@database_user_name='dbo',
@retry_attempts=3,
@retry_interval=10,
@output_file_name='C:\backups\outputfilename.txt',
@flags=2
Q) What are the subsystems values in sp_add_jobstep ?
A)
Value - Description
'ACTIVESCRIPTING' - Active Script
'CMDEXEC'-Operating-system command or executable program
'DISTRIBUTION'-Replication Distribution Agent job
'SNAPSHOT'- Replication Snapshot Agent job
'LOGREADER' -Replication Log Reader Agent job
'MERGE'- Replication Merge Agent job
'QueueReader'-Replication Queue Reader Agent job
'ANALYSISQUERY' -Analysis Services query (MDX, DMX).
'ANALYSISCOMMAND'- Analysis Services command (XMLA).
'Dts' -Integration Services package execution
'PowerShell'- PowerShell Script
'TSQL' -(default) Transact-SQL statement
Q) What are the possible on success and on failure option values in sp_add_jobstep ?
A)
Value - Description (action)
1 (default) -Quit with success
2 -Quit with failure
3- Go to next step
4 -Go to step on_success_step_id.
Q) What are possible flag values in sp_add_jobstep ?
A)
Value - Description
0 (default)- Overwrite output file
2 -Append to output file
4 -Write Transact-SQL job step output to step history
8 -Write log to table (overwrite existing history)
16 -Write log to table (append to existing history)
Q) How to schedule a job to run at our customized intervals ?
A)
exec msdb.dbo.sp_add_jobschedule
Q) How to create an alert ?
A)
exec msdb.dbo.sp_add_alert
Q) How to setup notification for an alert ?
A)
exec msdb.dbo.sp_add_notification
Q) How to create an operator (notification recipient) for use with alerts and jobs ?
A)
exec msdb.dbo.sp_add_operator
Q) How to Create a schedule that can be used by any number of jobs ?
A)
exec msdb.dbo.sp_add_schedule
Q) How to attach a schedule to a job ?
A)
exec msdb.dbo.sp_attach_schedule
Q) How to Close the current SQL Server Agent error log file and cycle the SQL Server Agent error log extension numbers just like a server agent restart ? (reinstantiating sql server agent error log)
A)
exec msdb.dbo.sp_cycle_agent_errorlog
Q) How to close the current server error log file and cycle the error log extension numbers just like a server restart ?
(reinstantiating sql server error log)
A)
exec msdb.dbo.sp_cycle_errorlog
Q) How to find information about jobs that are used by SQL Server Agent to perform automated activities in SQL Server ?
A)
exec msdb.dbo.sp_help_job
Q) How to know information about the runtime state of SQL Server Agent jobs ?
A)
exec msdb.dbo.sp_help_jobactivity
Q) How to know the number of jobs i.e. count that a particular schedule is attached to ?
A)
exec msdb.dbo.sp_help_jobcount @schedule_id=9
Q) How to know the information about all those jobs that a particular schedule is attached to ?
A)
exec msdb.dbo.sp_help_jobs_in_schedule @schedule_id=9
Q) How to know information for the steps in a job used by SQL Server Agent service to perform automated activities ?
A)
exec msdb.dbo.sp_help_jobstep
Q) How to know a list of alerts for a given operator or a list of operators for a given alert ?
A)
exec msdb.dbo.sp_help_notification
Example :
EXEC msdb.dbo.sp_help_notification
@object_type = N'ALERTS',
@name = N'sudarsan',
@enum_type = N'ACTUAL',
@notification_method = 7
Q) How to find information about the operators defined for the server ?
A)
exec msdb.dbo.sp_help_operator
Q) How to know all the created proxies by dbo or anyone else with necessary permissions of creating a proxy ?
A)
exec msdb.dbo.sp_help_proxy
Q) How to know all the existing schedules ?
A)
exec msdb.dbo.sp_help_schedule
Q) How to Delete or reassign jobs that belong to the specified login ?
A)
sp_manage_jobs_by_login
[ @action = ] 'action'
[, [@current_owner_login_name = ] 'current_owner_login_name']
[, [@new_owner_login_name = ] 'new_owner_login_name']
Example :
USE msdb ;
GO
EXEC dbo.sp_manage_jobs_by_login
@action = N'REASSIGN',
@current_owner_login_name = N'danw',
@new_owner_login_name = N'françoisa' ;
GO
or
USE msdb ;
GO
EXEC dbo.sp_manage_jobs_by_login
@action = N'DELETE',
@current_owner_login_name = N'danw'
GO
Q) How to add a operator ?
A)
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'blackclouds',
@enabled=1,
@pager_days=0,
@email_address=N'black.clouds.999@gmail.com'
GO
Q) How to notify an operator ?
A)
exec msdb.dbo. sp_notify_operator
[ @profile_name = ] 'profilename' ,
[ @id = ] id ,
[ @name = ] 'blackclouds' ,
[ @subject = ] 'subject' ,
[ @body = ] 'message' ,
[ @file_attachments = ] 'attachment'
[ @mail_database = ] 'mail_host_database'
Q) How to revokes access for the login "terrid" to access the proxy "Catalog application proxy" ?
A)
USE msdb ;
GO
EXEC dbo.sp_revoke_login_from_proxy
@name = N'terrid',
@proxy_name = N'Catalog application proxy' ;
GO
Q) How to Revoke access to a subsystem from a proxy ?
A)
USE msdb ;
GO
EXEC dbo.sp_revoke_proxy_from_subsystem
@proxy_name = 'Catalog application proxy',
@subsystem_name = N'Dts';
Q) How to know information about the alerts defined for the server ?
A)
exec msdb.dbo.sp_help_alert
Q) How to instruct SQL Server Agent to execute a job immediately ?
A)
exec msdb.dbo.sp_start_job @job_name='backup_system_db'
Q) How to instruct SQL Server Agent to stop the execution of a job ?
A)
exec msdb.dbo.sp_stop_job @job_name='backup_system_db'
Q) How to Grant a security principal access to a proxy ?
A)
USE msdb ;
GO
EXEC dbo.sp_grant_login_to_proxy
@login_name = N'adventure-works\terrid',
@proxy_name = N'Catalog application proxy' ;
GO
Q) How to Grant a proxy access to a subsystem ?
A)
USE msdb ;
GO
EXEC dbo.sp_grant_proxy_to_subsystem
@proxy_name = 'Catalog application proxy',
@subsystem_id = 2;
GO
Q) List all the SQL Server Agent subsystems.give query.
A)
exec msdb.dbo.sp_enum_sqlagent_subsystems
Q) Lists permissions for SQL Server Agent proxies to access subsystems.
A)
exec msdb.dbo.sp_enum_proxy_for_subsystem
Q) Lists associations between security principals and proxies.
A)
exec msdb.dbo.sp_enum_login_for_proxy
Q) How to Remove the history records for a job ?
A)
The following example removes the history for a job named NightlyBackups.
USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
@job_name = N'NightlyBackups' ;
GO
The following example executes the procedure with no parameters to remove all history records.
USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory ;
GO
Q) How to remove an association between a schedule and a job ?
A)
The following example removes an association between a 'NightlyJobs' schedule and a 'BackupDatabase' job.
USE msdb ;
GO
EXEC dbo.sp_detach_schedule
@job_name = 'BackupDatabase',
@schedule_name = 'NightlyJobs' ;
GO
Q)Write short notes on sp_add_category ?
A)
Adds the specified category of jobs, alerts, or operators to the server.
Syntax
sp_add_category
[ [ @class = ] 'class', ]
[ [ @type = ] 'type', ]
{ [ @name = ] 'name' }
Arguments
[ @class = ] 'class'
The class of the category to be added. class is varchar(8) with a default value of JOB, and can be one of these values.
Value Description
JOB Adds a job category.
ALERT Adds an alert category.
OPERATOR Adds an operator category.
[ @type = ] 'type'
The type of category to be added. type is varchar(12), with a default value of LOCAL, and can be one of these values.
Value Description
LOCAL A local job category.
MULTI-SERVER A multiserver job category.
NONE A category for a class other than JOB.
[ @name = ] 'name'
The name of the category to be added. The name must be unique within the specified class. name is sysname, with no default.
Return Code Values
0 (success) or 1 (failure)
sp_add_category must be run from the msdb database.
Permissions
Only members of the sysadmin fixed server role can execute sp_add_category.
Examples
The following example creates a local job category named AdminJobs.
USE msdb ;
GO
EXEC dbo.sp_add_category
@class=N'JOB',
@type=N'LOCAL',
@name=N'AdminJobs' ;
GO
Q) Write short notes on sp_add_jobserver.
A)
Targets the specified job at the specified server.
Topic link icon Transact-SQL Syntax Conventions
Syntax
sp_add_jobserver [ @job_id = ] job_id | [ @job_name = ] 'job_name'
[ , [ @server_name = ] 'server' ]
Arguments
[ @job_id = ] job_id
The identification number of the job. job_id is uniqueidentifier, with a default of NULL.
[ @job_name = ] 'job_name'
The name of the job. job_name is sysname, with a default of NULL.
ms178625.note(en-us,SQL.100).gifNote:
Either job_id or job_name must be specified, but both cannot be specified.
[ @server_name = ] 'server'
The name of the server at which to target the job. server is nvarchar(30), with a default of N'(LOCAL)'. server can be either (LOCAL) for a local server, or the name of an existing target server.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None
Remarks
@automatic_post exists in sp_add_jobserver, but is not listed under Arguments. @automatic_post is reserved for internal use.
SQL Server Management Studio provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.
Permissions
By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:
* SQLAgentUserRole
* SQLAgentReaderRole
* SQLAgentOperatorRole
For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.
Only members of the sysadmin fixed server role can execute sp_add_jobserver for jobs that involve multiple servers.
Examples
A. Assigning a job to the local server
The following example assigns the job NightlyBackups to run on the local server.
ms178625.note(en-us,SQL.100).gifNote:
This example assumes that the NightlyBackups job already exists.
USE msdb ;
GO
EXEC dbo.sp_add_jobserver
@job_name = N'NightlyBackups' ;
GO
B. Assigning a job to run on a different server
The following example assigns the multiserver job Weekly Sales Backups to the server SEATTLE2.
ms178625.note(en-us,SQL.100).gifNote:
This example assumes that the Weekly Sales Backups job already exists and that SEATTLE2 is registered as a target server for the current instance.
USE msdb ;
GO
EXEC dbo.sp_add_jobserver
@job_name = N'Weekly Sales Backups',
@server_name = N'SEATTLE2' ;
GO
Q) How to update a job ?
A)
use the sp as follows :
exec msdb.dbo.sp_update_job.
1) If you want to either update or delete regarding jobs alerts etc etc regarding this then you need to simply replace
exec msdb.dbo.sp_add_alert
or
exec msdb.dbo.sp_help_alert
to
exec msdb.dbo.sp_update-alert
or
exec msdb.dbo.sp_delete_alert.
use delete here and not drop.
2) If no proxy_id is specified, no proxy_name is specified, and no user_name is specified, the job step runs as the service account for SQL Server Agent.
A)
Jobs
Job Activity Monitor
Alerts
Operators
Proxies
Error Logs
Here error logs is the error log for sql server agent and not for the sql server instance.You find the error log for server instance under management node.
Q) How to create a job ?
A)
use the following sp :
exec msdb.dbo.sp_add_job
Example :
exec msdb.dbo.sp_add_job
@job_name='backupdbkali',
@enabled=1,
@description='this takes backup of db kali',
@category_id=3,
@owner_login_name='home-bix5t88dr6\administrator',
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@notify_email_operator_name='sudarsan',
@delete_level=0.
This example job creates a job that is going to perform a backup of db kali.
Q) How to supply category_id value as input in sp_add_job stored procedure ? or How to find the existing category id's and their description ?
A)
exec msdb.dbo.sp_help_category
Q) What are the possible notify level values that can be supplied as inputs in stored procedure sp_add_job ?
A)
0-Never
1-Success
2-Failure
3-Always
Q) Job can contain multiple steps.Now how to add a step to a job ?
A) Use the following stored procedure :
exec msdb.dbo.sp_add_jobstep
Example :
exec msdb.dbo.sp_add_jobstep
@job_name='backupdbkali',
@step_id=1,
@step_name='backsup kalidb',
@subsystem='TSQL',
@command='use master go backup database kali to disk=''C:\backups\kali.bak''',
@on_success_action=1,
@on_fail_action=2,
@server='home-bix5t88dr6\csfspd1caspro',
@database_name='master',
@database_user_name='dbo',
@retry_attempts=3,
@retry_interval=10,
@output_file_name='C:\backups\outputfilename.txt',
@flags=2
Q) What are the subsystems values in sp_add_jobstep ?
A)
Value - Description
'ACTIVESCRIPTING' - Active Script
'CMDEXEC'-Operating-system command or executable program
'DISTRIBUTION'-Replication Distribution Agent job
'SNAPSHOT'- Replication Snapshot Agent job
'LOGREADER' -Replication Log Reader Agent job
'MERGE'- Replication Merge Agent job
'QueueReader'-Replication Queue Reader Agent job
'ANALYSISQUERY' -Analysis Services query (MDX, DMX).
'ANALYSISCOMMAND'- Analysis Services command (XMLA).
'Dts' -Integration Services package execution
'PowerShell'- PowerShell Script
'TSQL' -(default) Transact-SQL statement
Q) What are the possible on success and on failure option values in sp_add_jobstep ?
A)
Value - Description (action)
1 (default) -Quit with success
2 -Quit with failure
3- Go to next step
4 -Go to step on_success_step_id.
Q) What are possible flag values in sp_add_jobstep ?
A)
Value - Description
0 (default)- Overwrite output file
2 -Append to output file
4 -Write Transact-SQL job step output to step history
8 -Write log to table (overwrite existing history)
16 -Write log to table (append to existing history)
Q) How to schedule a job to run at our customized intervals ?
A)
exec msdb.dbo.sp_add_jobschedule
Q) How to create an alert ?
A)
exec msdb.dbo.sp_add_alert
Q) How to setup notification for an alert ?
A)
exec msdb.dbo.sp_add_notification
Q) How to create an operator (notification recipient) for use with alerts and jobs ?
A)
exec msdb.dbo.sp_add_operator
Q) How to Create a schedule that can be used by any number of jobs ?
A)
exec msdb.dbo.sp_add_schedule
Q) How to attach a schedule to a job ?
A)
exec msdb.dbo.sp_attach_schedule
Q) How to Close the current SQL Server Agent error log file and cycle the SQL Server Agent error log extension numbers just like a server agent restart ? (reinstantiating sql server agent error log)
A)
exec msdb.dbo.sp_cycle_agent_errorlog
Q) How to close the current server error log file and cycle the error log extension numbers just like a server restart ?
(reinstantiating sql server error log)
A)
exec msdb.dbo.sp_cycle_errorlog
Q) How to find information about jobs that are used by SQL Server Agent to perform automated activities in SQL Server ?
A)
exec msdb.dbo.sp_help_job
Q) How to know information about the runtime state of SQL Server Agent jobs ?
A)
exec msdb.dbo.sp_help_jobactivity
Q) How to know the number of jobs i.e. count that a particular schedule is attached to ?
A)
exec msdb.dbo.sp_help_jobcount @schedule_id=9
Q) How to know the information about all those jobs that a particular schedule is attached to ?
A)
exec msdb.dbo.sp_help_jobs_in_schedule @schedule_id=9
Q) How to know information for the steps in a job used by SQL Server Agent service to perform automated activities ?
A)
exec msdb.dbo.sp_help_jobstep
Q) How to know a list of alerts for a given operator or a list of operators for a given alert ?
A)
exec msdb.dbo.sp_help_notification
Example :
EXEC msdb.dbo.sp_help_notification
@object_type = N'ALERTS',
@name = N'sudarsan',
@enum_type = N'ACTUAL',
@notification_method = 7
Q) How to find information about the operators defined for the server ?
A)
exec msdb.dbo.sp_help_operator
Q) How to know all the created proxies by dbo or anyone else with necessary permissions of creating a proxy ?
A)
exec msdb.dbo.sp_help_proxy
Q) How to know all the existing schedules ?
A)
exec msdb.dbo.sp_help_schedule
Q) How to Delete or reassign jobs that belong to the specified login ?
A)
sp_manage_jobs_by_login
[ @action = ] 'action'
[, [@current_owner_login_name = ] 'current_owner_login_name']
[, [@new_owner_login_name = ] 'new_owner_login_name']
Example :
USE msdb ;
GO
EXEC dbo.sp_manage_jobs_by_login
@action = N'REASSIGN',
@current_owner_login_name = N'danw',
@new_owner_login_name = N'françoisa' ;
GO
or
USE msdb ;
GO
EXEC dbo.sp_manage_jobs_by_login
@action = N'DELETE',
@current_owner_login_name = N'danw'
GO
Q) How to add a operator ?
A)
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'blackclouds',
@enabled=1,
@pager_days=0,
@email_address=N'black.clouds.999@gmail.com'
GO
Q) How to notify an operator ?
A)
exec msdb.dbo. sp_notify_operator
[ @profile_name = ] 'profilename' ,
[ @id = ] id ,
[ @name = ] 'blackclouds' ,
[ @subject = ] 'subject' ,
[ @body = ] 'message' ,
[ @file_attachments = ] 'attachment'
[ @mail_database = ] 'mail_host_database'
Q) How to revokes access for the login "terrid" to access the proxy "Catalog application proxy" ?
A)
USE msdb ;
GO
EXEC dbo.sp_revoke_login_from_proxy
@name = N'terrid',
@proxy_name = N'Catalog application proxy' ;
GO
Q) How to Revoke access to a subsystem from a proxy ?
A)
USE msdb ;
GO
EXEC dbo.sp_revoke_proxy_from_subsystem
@proxy_name = 'Catalog application proxy',
@subsystem_name = N'Dts';
Q) How to know information about the alerts defined for the server ?
A)
exec msdb.dbo.sp_help_alert
Q) How to instruct SQL Server Agent to execute a job immediately ?
A)
exec msdb.dbo.sp_start_job @job_name='backup_system_db'
Q) How to instruct SQL Server Agent to stop the execution of a job ?
A)
exec msdb.dbo.sp_stop_job @job_name='backup_system_db'
Q) How to Grant a security principal access to a proxy ?
A)
USE msdb ;
GO
EXEC dbo.sp_grant_login_to_proxy
@login_name = N'adventure-works\terrid',
@proxy_name = N'Catalog application proxy' ;
GO
Q) How to Grant a proxy access to a subsystem ?
A)
USE msdb ;
GO
EXEC dbo.sp_grant_proxy_to_subsystem
@proxy_name = 'Catalog application proxy',
@subsystem_id = 2;
GO
Q) List all the SQL Server Agent subsystems.give query.
A)
exec msdb.dbo.sp_enum_sqlagent_subsystems
Q) Lists permissions for SQL Server Agent proxies to access subsystems.
A)
exec msdb.dbo.sp_enum_proxy_for_subsystem
Q) Lists associations between security principals and proxies.
A)
exec msdb.dbo.sp_enum_login_for_proxy
Q) How to Remove the history records for a job ?
A)
The following example removes the history for a job named NightlyBackups.
USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
@job_name = N'NightlyBackups' ;
GO
The following example executes the procedure with no parameters to remove all history records.
USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory ;
GO
Q) How to remove an association between a schedule and a job ?
A)
The following example removes an association between a 'NightlyJobs' schedule and a 'BackupDatabase' job.
USE msdb ;
GO
EXEC dbo.sp_detach_schedule
@job_name = 'BackupDatabase',
@schedule_name = 'NightlyJobs' ;
GO
Q)Write short notes on sp_add_category ?
A)
Adds the specified category of jobs, alerts, or operators to the server.
Syntax
sp_add_category
[ [ @class = ] 'class', ]
[ [ @type = ] 'type', ]
{ [ @name = ] 'name' }
Arguments
[ @class = ] 'class'
The class of the category to be added. class is varchar(8) with a default value of JOB, and can be one of these values.
Value Description
JOB Adds a job category.
ALERT Adds an alert category.
OPERATOR Adds an operator category.
[ @type = ] 'type'
The type of category to be added. type is varchar(12), with a default value of LOCAL, and can be one of these values.
Value Description
LOCAL A local job category.
MULTI-SERVER A multiserver job category.
NONE A category for a class other than JOB.
[ @name = ] 'name'
The name of the category to be added. The name must be unique within the specified class. name is sysname, with no default.
Return Code Values
0 (success) or 1 (failure)
sp_add_category must be run from the msdb database.
Permissions
Only members of the sysadmin fixed server role can execute sp_add_category.
Examples
The following example creates a local job category named AdminJobs.
USE msdb ;
GO
EXEC dbo.sp_add_category
@class=N'JOB',
@type=N'LOCAL',
@name=N'AdminJobs' ;
GO
Q) Write short notes on sp_add_jobserver.
A)
Targets the specified job at the specified server.
Topic link icon Transact-SQL Syntax Conventions
Syntax
sp_add_jobserver [ @job_id = ] job_id | [ @job_name = ] 'job_name'
[ , [ @server_name = ] 'server' ]
Arguments
[ @job_id = ] job_id
The identification number of the job. job_id is uniqueidentifier, with a default of NULL.
[ @job_name = ] 'job_name'
The name of the job. job_name is sysname, with a default of NULL.
ms178625.note(en-us,SQL.100).gifNote:
Either job_id or job_name must be specified, but both cannot be specified.
[ @server_name = ] 'server'
The name of the server at which to target the job. server is nvarchar(30), with a default of N'(LOCAL)'. server can be either (LOCAL) for a local server, or the name of an existing target server.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None
Remarks
@automatic_post exists in sp_add_jobserver, but is not listed under Arguments. @automatic_post is reserved for internal use.
SQL Server Management Studio provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.
Permissions
By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:
* SQLAgentUserRole
* SQLAgentReaderRole
* SQLAgentOperatorRole
For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.
Only members of the sysadmin fixed server role can execute sp_add_jobserver for jobs that involve multiple servers.
Examples
A. Assigning a job to the local server
The following example assigns the job NightlyBackups to run on the local server.
ms178625.note(en-us,SQL.100).gifNote:
This example assumes that the NightlyBackups job already exists.
USE msdb ;
GO
EXEC dbo.sp_add_jobserver
@job_name = N'NightlyBackups' ;
GO
B. Assigning a job to run on a different server
The following example assigns the multiserver job Weekly Sales Backups to the server SEATTLE2.
ms178625.note(en-us,SQL.100).gifNote:
This example assumes that the Weekly Sales Backups job already exists and that SEATTLE2 is registered as a target server for the current instance.
USE msdb ;
GO
EXEC dbo.sp_add_jobserver
@job_name = N'Weekly Sales Backups',
@server_name = N'SEATTLE2' ;
GO
Q) How to update a job ?
A)
use the sp as follows :
exec msdb.dbo.sp_update_job.
1) If you want to either update or delete regarding jobs alerts etc etc regarding this then you need to simply replace
exec msdb.dbo.sp_add_alert
or
exec msdb.dbo.sp_help_alert
to
exec msdb.dbo.sp_update-alert
or
exec msdb.dbo.sp_delete_alert.
use delete here and not drop.
2) If no proxy_id is specified, no proxy_name is specified, and no user_name is specified, the job step runs as the service account for SQL Server Agent.
Subscribe to:
Posts (Atom)