Independent software

1. Sqlpro Studio 1 0 335 – Powerful Database Manager Job Openings
2. Sqlpro Studio 1 0 335 – Powerful Database Manager Job Opportunities

PeterSalterr January 18, 2015 / Version: SQL Database Studio 2.0.9 2015-01-18 04:11:07 By PeterSalterr. Database Administrators need a bachelor’s degree, but the field of study for these candidates can vary. Computer science is a common choice, but they may also come from backgrounds in areas like computer information systems.

You can create jobs on SQL Server to do certain operations at certain intervals. For example, if you want to run a script every half an hour or you may need to get backups every night. We can duplicate samples. In this article I will describe how to create a job.

Right-click Jobs under SQL Server Agent on SSMS and click New Job as follows.

We give a name to job in the Name section of the screen. In the owner part, we determine the owner of the job. I usually set it as sa.

In the Steps tab, we will create a step for the job. We come to the Steps section and click on New and we will see a screen like below.

In the Step name section, we give a name to step.

In the Type section, because we have selected Transact-SQL script (T-SQL), you should write the script you want to run in the Command section instead of “Select 1”.

You can do the different operations you want to do with the job by selecting the following options from the Type section.

For example running a power shell command, operating system command, or executing an executable program.

After creating Step, we click New from the Schedule section to determine the interval at which the job will run.

In the Name section, we give a name to Schedule. We usually choose Recurring in the Schedule type section. By making this selection, we ensure that this job works periodically.

If you select “Start automatically when SQL Server Agent starts”, the job starts when the SQL Server Agent starts.

If you select “Start whenever the CPUs become idle”, the job starts as soon as the CPU reaches the idle position.

If you select “One time”, the job only works once.

We choose Recurring and choose Daily from Occurs as follows.

You can select Daily to run Job every day, Weekly to run it on specific days and times of the week, or Monthly to run it on specific days and times of the month.

We choose 1 Days in the Recurs every section. So the job will work every day.

If we choose an hour from the Occurs once at section, it works every day at the time we choose.

We choose 2 hours from the Occurs every part. So the job will run every 2 hours every day. Click on ok to create our job.

By: Greg Robidoux Updated: 2018-06-02 Comments (7) Related: More >SQL Server Agent

Problem

When working with SQL Agent jobs sometimes it is difficult to determine why ajob failed or even exactly what occurred. For each job step SQL Server providesa message, but it is not always that easy to determine exactly what was occurringduring that step. The default message that is saved in the job history is1024 characters. In most cases this may be enough, but if you have a longrunning process you may need to store more than 1024 characters. Is there any wayto get additional information within the job history to help troubleshoot issuesas well as just knowing what occurred?

Solution

In SQL Server 2005 and later you have the ability to log additional SQL Agentjob output beyond the 1024 characters that is stored in the msdb.dbo.sysjobhistorytable. By default this enhanced logging is not turned on it is something thatyou need to turn on for each job step.

Let's take for example we are running DBCC CHECKDB commands for several databases. This command provides a lot of output data unless you use the WITH NO_INFOMSGS option. If you are not using the NO_INFOMSGS option the command output fills up the 1024characters quite quickly and you can only see the part of the output in the jobhistory message.

Here is a screen shot of the job history for the step that did a DBCC CHECKDB. As you can see we only get a portion of the command output.

To allow additional data to be logged you need to turn on some settings for eachjob step in your job. To do this edit the job step and select the Advancedtab.

On this screen you need to enable both the 'Log to table' and 'Include step outputin history'.

After we make these changes and run this again if we look at the job historyyou will see the same short message.

In order to see the additional logged information you need to use this storedprocedure sp_help_jobsteplog or you could query the msdb.dbo.sysjobstepslogtable directly.

If we run this command in a query window (test2 is the job name):

we will get this additional output: (note: the output was edited to removemost of the middle to keep this web page smaller)

As you can see we can now get the entire output message since the output in thesysjobstepslog is stored as a nvarchar(max) instead of an nvarchar(1024) like insysjobhistory.

Next Steps

• Helpfully this tip is useful for you to get additional job step history,just a couple of clicks can provide a lot more detail
• Keep in mind that only one row is stored per job step. If you usethe 'Append output to existing entry in table' this will just append the datato the existing record.
• Take a look at these other usefulSQLAgent tips

About the author

Sqlpro Studio 1 0 335 – Powerful Database Manager Job Openings

Sqlpro Studio 1 0 335 – Powerful Database Manager Job Opportunities