This script copies jobs from one msdb database to another, useful for when SQL Server is re-installed, and the DBA Forgot to script out the sql agent jobs; but you still have the old msdb mdf and ldf. 

Simply attach the database as msdb_old.

It is also possible to use this via a linked server and copy some sqlagent jobs from one server to another; this script is specific to sql server 2005. 

One item I had to adjust is that some of the jobs appeared "greyed" out afterwards and I had to open each job and adjust the target server to "local". 

But it was still quicker than re-creating all the jobs.  (I was unable to restore the previous version of the msdb database because it was at a lower service pack level than what the new server was built at - had I been smart as the new server was built there would have been a restore of the msdb database completed when the new server was at the same level, but I missed that step and was then left with the task of recreating all the jobs, argh).

I'd be cautious using this script if there were a lot of reporting subscription jobs (as there would have to be some synchronizations with the report database and website), but in this particular case all the jobs were custom backups, tlogs, exports, imports and other purely tsql and ssis related tasks.

http://www.lifeasbob.com/Code/ScriptVault.aspx?script_id=80 

Affinity mask keeping SQL Server Agent from starting.

Recently had a unique issue in one of our HP Polyserve clustered environments.  One of the unique aspects of HP Polyserve is that you can cluster different types of hardware.  This can reduce costs and risks (as a bug in firmware or hardware on one machine is also most likely going to exist in the other node, especially if they are the same make, model, and usually purchased together).

We had an affinity mask set on a 16 processor machine, the instance was intentionally re-hosted to another server in the cluster, which was an 8 way machine.  The instance did start, but SQL Agent would not.

The following error messages were found in the sql errorlog:

initconfig: Warning: affinity mask specified is not valid. Defaulting to no affinity. Use sp_configure 'affinity mask' or 'affinity64 mask' to configure the system to be compatible with the CPU mask on the system. You can also configure the system based on the number of licensed CPUs.

SQL Server blocked access to procedure 'dbo.sp_sqlagent_has_server_access' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.

A quick bit of research showed a similiar issue in a Microsoft Cluster, where the one node did not have the correct permissions for the "lock pages in memory" setting, causing the same result of SQL Agent not starting, you can see a great write up of this in Suhas' blog.

Seems if the "start up" process for a SQL Server instance is interupted that the settings changed from the defaults in sp_configure are not set and the process does not continue.  From what I can determine this "start up" process is the settings contained in sp_configure, if any of these fail along the way the process just stops or rolls them back and the other settings like "Agent XPs" (required to start sql agent), will not occur.

I think this is a behavior that should not exist in SQL Server, why does an incorrect affinity mask keep SQL Server from finishing running the other settings set by sp_configure ?  These options should be mutually exclusive and one failure should not preclude others from running.

I've had similar issues with how SQL Server starts up affecting SQL Agent, and it does not appear to be a priority to fix, see my post on SQL Agent will not start when a user database is in recovery. 

This was easy to fix, but required setting a correct affinity mask and stopping and starting the service.  Generally we don't use an affinity mask on many instances.  HP Polyserve provides pre and post start up scripts which we'll use to address the issue in the future, though in extreme cases of failure no pre shutdown script will be effective.  Plan your failover capacity carefully.

SQL Agent job listing, jobs, steps and tsql / commands executed.

Not really useful, but helpful when someone wants to see all the jobs and the steps they are executing.

Save as an excel file, or publish to rdl and push to a reporting site so they can always see it themselves....

select sj.name,sj.description,sjs.step_name,
 sjs.subsystem,sjs.command,sjs.database_name,
 sjs.output_file_name
 from sysjobs sj
 inner join sysjobsteps sjs on sj.job_id = sjs.job_id
-- where sj.name like 'cy%'
 order by sj.job_id,sjs.step_id

Began receiving alerts on a job failing.  The job was "suspended".  The error in the history event log is:

Unable to start execution of step 2 (reason: The ActiveScripting subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended).  The step failed.

A quick review of the server shows MSDE was installed on the machine, somehow this causes an error in the registry.

Review the below registry entry and ensure there is an "s" at the end.  According to Microsoft this happens sometimes with MSDE installations and upgrades.  You can see the picture of mine, that it was missing

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup]
"SQLPath"="C:\Program Files\Microsoft SQL Server\80\Tools"

I added the "s" to the end.  Started and stop the instance (i did both services because I could, but may only need to have the agent service restarted.)

Everything is working fine now.

This was on a sql server 2000 installation.

SQL Server does a great job of logging, but anyone who administers a sql server should adjust some of the "out of the box" defaults for an instance, and create some custom jobs in sql agent to control output of jobs.

By default sql server logs information to ErrorLog.  Depending on your version of SQL Server (7.0, 2000 or 2005) and a default or named instance it is located along a path similiar to the following:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL${Instance}\LOG

The ErrorLog is a great source of monitoring and should be scraped and reviewed regularly, but by default there are only 6 log files kept on disk, and a new log file is started evertime an instance is started or "cycled".  This is not adequate, an administrator applying a patch to a server seems to require 3 reboots and you don't want to find out that information needed in a log file was lost due to not having enough of them.  We run this to 30 log files kept on disk and cycle the error log daily at 12am via a sql agent job.  This is very easy and can be adjusted via gui in SQL Enterprise Manager or in SQL Server Managemetn Studio by right clicking on the SQL Server Logs and selecting "Configure", which will bring up the dialog below, adjust to any number, but definetly 6 is not enough !

It can also be adjusted via TSQL, but requires xp_instance_regwrite and is highly dependent that you know the structure of the registry for your particular instance and version of sql server, this is one case where using the GUI is the best option, but it is possible to do via tsql:

EXECUTE master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE'
,N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer' --****!!!! VERIFY
,N'NumErrorlogs'
,REG_DWORD
,30 -- number of errorlogs you want to keep
GO

After configuring the instance to retain 30 errorLogs, now you need to implement a sql agent job to cycle the error logs daily at 12am.  This is very easily implemented with a job scheduled to run:
Exec sp_cycle_ErrorLog.

Now onto the good one, SQL Server Agent and the output from jobs.

After managing SQL Server Agent jobs for many years it is apparent that the default options for logging of jobs, Overwrite and Append, is not adequate.  Most of the time we default the logs to append, but overtime and depending on the amount of output these log files, they can become very large, difficult to open, difficult to read and waste valuable time when you have to trouble shoot a job.  This applies to sql server 7.0, 2000 and 2005 and is highly dependent on having all your jobs and logging go to the same directory (if they are currently "scattered" you can correct them manually or through tsql in the sysjobsteps table, with a mass update).

We have developed a jobs that we host on every sql agent instance, Log Archive.  It is a two step job consisting of TSQL and Active X Scripting.  One step moves all the log files to a sub-directory for that day.  The 2nd step of the job removes the sub-directories older than x days (we default to 30).  This makes it very easy to find a log file for a particular day, based on the naming convention of the sub-directories.  This job runs daily at 11:58pm.  Example below:

The code for the two steps is attached, they will require editing for your specific directory structure and number of sub-directories to retain.

Archive_Daily_Logs.txt (.7 KB) 

VBScript_Delete_Old_Dirs.txt (.52 KB) 

This creates a great directory tree, easy to maintain, easy to troubleshoot:

Polyserve clusters SQL Server instances, and can then rehost them or fail them over to other servers.

One of the issues we've run into with Polyserve is when we move a sql server instance from one machine to another where the binaries change location from mssql.a to mssql.b.

SQL Agent (SQLAgent.exe, SQLAgent), utilizes a system table in msdb for starting and executing jobs, called syssubsystems;

Execute: select * from msdb.dbo.syssubsystems and you will see each sub-system of sql agent and the path to the binaries, if a sql server instance is moved through some technology like Polyserve or through manually moving a msdb database from one server to another and the path to the binaries change, the system tables will need to be updated to the new path.

We've experienced this on Polyserve and when we moved a sql server instance from one server to another and copied msdb.

very easy to fix, but frustrating, below is  a snippet which can be run to correct the issue: