SSMS – Scripting – Job – Error – “msdb.dbo.sp_add_jobserver” – “is already targeted at server”

 

Background

Using SSMS, Scripted SQL Server Job so that I can apply it against other SQL Servers, but ran into error.

Scripting

Script SQL Server Agent Job

scriptJob_20180612_0128PM.PNG

 

Error

Error Message


Msg 14269, Level 16, State 1, Procedure msdb.dbo.sp_add_jobserver, Line 101 [Batch Start Line 8]
Job 'SharePublicly' is already targeted at server 'LAB'.

Troubleshooting

When a job is scripted, the code generated includes a portion that invokes sp_add_jobserver.

The sp_add_jobserver procedure binds the job to the targeted server.


EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
         @job_id = @jobId
       , @server_name = N'(local)'

IF (@@ERROR  0 OR @ReturnCode  0)
      GOTO QuitWithRollback

Remediation

Outline

  1. Check msdb system tables
    • dbo.sysjobservers
    • dbo.systargetservers
  2. If matching records
    • Here are the tables that needs to be considered
      • dbo.sysjobservers.jobName ( @jobID )
      • dbo.systargetservers.serverName ( @servername)
    • Conditions satisfied
      • Local variable @skipWhenJobExist is enabled ( set to 1 )
  3. Action
    • Skip invoking msdb.dbo.sp_add_jobserver

Code Snippet


DECLARE @jobId   BINARY(16)
DECLARE @jobName sysname
DECLARE @serverName sysname
DECLARE @serverNameLocal sysname
declare @commit bit
declare @skipWhenJobExist bit

set @jobName = 'SharePublicly';
set @servername = N'(local)'
set @serverNameLocal = N'(local)'

--set @commit = 0
set @commit = 1

set @skipWhenJobExist = 1
--set @skipWhenJobExist = 0

/*
	Check if Job is already attached to Target Server
*/
if not exists
(

	select
			  [src] = 'dbo.sysjobservers'
			, tblSJS.* 

	from   dbo.sysjobservers tblSJS

	left outer join dbo.systargetservers tblSTS
			on tblSJS.server_id = tblSTS.server_id

	where  tblSJS.job_id = @jobId

	and  

		(

			(
					 ( tblSJS.server_id = 0)
				 and ( @servername = @serverNameLocal )

			)

			or
			(
					 ( tblSJS.server_id = tblSTS.server_id)
				 and ( tblSTS.server_name = @serverName )
			)

		)
	and (@skipWhenJobExist = 1)
)
begin

	EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
			  @job_id = @jobId
			, @server_name = @serverName

	IF (@@ERROR  0 OR @ReturnCode  0)
	begin
		GOTO QuitWithRollback
	end

end

Source Control

GitHub

DanielAdeniji/SQLServerScriptingAgentErrorJobAlreadyTargeted
Link

 

SQL Server – Recreate System Database – msdb

Background

One of my brothers called me and said that that he is unable to use his beloved SQL Server Management Studio  ( SSMS ) to edit records in a table.

And, that SQL Server says his msdb database his corrupted.

 

Remediate

Restore from Backup

To remediate I asked if he takes backup of the msdb database and he says No.

And, so I know that is not quite a good look.

 

Recreate MSDB from Scratch

Overview

In the sample script below, we will recreate the MSDB Database.

SQL Server comes with a script called instmsdb.sql.  The file creates the msdb database.

We need to make sure that the database and its files do not exist and so we will do some housekeeping and rid self of the existing files.

It is a long winded code and so let us talk about it a bit.

 

Steps

  1. Preparation
    • Base Values
      • Set Instance Name
      • Set Folder Location of the instmsdb.sql file
      • Set Default Folder Location of system database files
      • Set File names for msdb data and log files
  2. Computed Values
    • Set full file name for MSDB Data and Log files
  3. Get Current Timestamp
  4. Compute Backup folder where we will keep the current data and log files for the msdb database
  5. If Backup folder does not exist, create it
  6. Stop MS SQL Server Engine and other SQL Server Services
  7. Start MS SQL Server Engine in minimal mode
    • Trace Flags
      • 3608
  8. Using sqlcmd.exe, detach msdb database
  9. Using sqlcmd.exe, recreate msdb database using instmsdb.sql
  10. Stop MS SQL Server
  11. Restart MS SQL Server in regular mode

 

Code


@echo on
setlocal

Rem set instance Name to Default
set "_instname=."

Rem Set Folder for location of instmsdb.sql
set _installFLD=E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install

Rem Set Folder for Location of System Database files such as ( master, model, tempdb, msdb)
set _dbSystemFLD=E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\

Rem Set filename for MSDB Database & Log file
set _dbMSDBData=MSDBData.mdf
set _dbMSDBLog=MSDBLog.ldf

Rem Set Full File Name for Data and Log files
set _datafile=%_dbSystemFLD%\%_dbMSDBData%
set _logfile=%_dbSystemFLD%\%_dbMSDBLog%


REM serverfault
REM Need leading zero for batch script using %time% variable
REM Answered by David
REM http://serverfault.com/questions/147515/need-leading-zero-for-batch-script-using-time-variable/529445
:prepare time stamp 
set year=%date:~10,4%
set month=%date:~4,2%
set day=%date:~7,2%
set hour=%time:~0,2%
:replace leading space with 0 for hours < 10
if "%hour:~0,1%" == " "  set hour=0%hour:~1,1%
set minute=%time:~3,2%
set second=%time:~6,2%
set _timeStamp=%year%%month%%day%_%hour%%minute%%second%

set _BACKUPFLD=%_dbSystemFLD%\BACKUP\%_timeStamp%

REM If Backup Folder does not exist, mkdir Backup Folder 
if not exist "%_BACKUPFLD%" mkdir "%_BACKUPFLD%"

REM Stop MSSQLServer
net stop mssqlserver /y

REM Start MSSQLServer
NET START MSSQLSERVER /T3608


REM Using SQLCMD Detach msdb database
sqlcmd  -E -S %_instname% -d master -Q "set quoted_identifier off; if db_id('msdb') is not null begin exec sp_detach_db 'msdb'; end "


REM If MSDB Data & Log file already exist at target folder, pleaes move to backup folder 
if exist "%_datafile%" move "%_datafile%" "%_BACKUPFLD%"
if exist "%_logfile%"  move "%_logfile%"  "%_BACKUPFLD%"


REM Using SQLCMD run instmsdb.sql
SQLCMD -E -S %_instname% -i "%_installFLD%\instmsdb.sql" -o instmsdb_%_timeStamp%.log


REM Stop MSSQLServer
net stop mssqlserver /y


REM Start MSSQLServer
NET START MSSQLSERVER


endlocal


Source Code Control

GitHub

Here is the URL for our repository

 

References

  1. Database Features > Databases (Database Engine) > System Databases > Rebuild System Databases
    Link

SQL Server – v2005 – Single Use Plans

Background

In MS SQL Server v2005, MSDB queries were submitted adhoc.

This can cause Plan Cache bloat and use up a lot of memory.

Diagnostic

Plans Aggregated by Cache Type

Kimberly L. Tripp has the most widely used query. It is shared here.

And, here it is.

Code


/*
	Kimberly Tripp
	Plan cache and optimizing for adhoc workloads
	http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
	2010-04-22
*/
SELECT 
		  [CacheType]
			= objtype

		, [Total Plans]
			= COUNT_BIG(*)

		, [Total MBs]
			= SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 

		, [Avg Use Count]
			= AVG(usecounts) 

		, [Total MBs – USE Count 1]
			= SUM(
				CAST(
						(
							CASE WHEN usecounts = 1 THEN size_in_bytes
								ELSE 0
							END
						) AS DECIMAL(18, 2)
					)
			) / 1024 / 1024 

		, [Total Plans – USE Count 1]
			= SUM
				(
					CASE WHEN usecounts = 1 THEN 1
						 ELSE 0
					END
				) 

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY [Total MBs – USE Count 1] DESC

GO

Output

PlansAggregatedByCacheType-20160512

Tally Single Use Plans


select

		 [Database]
			= case 
					when st.dbid = 32767 then 'Resource DB'
					else DB_NAME(st.dbid)
			  end

		, [DatabaseID]
			= st.[dbid]

		, [Number of Plans]
			= COUNT(*)

		, [SizeInMB]
			= cast
				(
					SUM( cast ( cp.size_in_bytes as bigint) ) 
					/ ( 1024 * 1024)
					as bigint
				)

from    sys.dm_exec_cached_plans cp

cross apply sys.dm_exec_sql_text(cp.plan_handle) st

WHERE cp.usecounts < 3

GROUP BY
	   st.[dbid]
         , DB_NAME(st.[dbid])

order by
	[SizeInMB] desc

Output

singleQueryPlansInEachDB - Prior

Compare Single Use Plans Vs All Plans



select

		 [Database]
			= case 
					when st.dbid = 32767 then 'Resource DB'
					else DB_NAME(st.dbid)
			  end

		, [DatabaseID]
			= st.[dbid]

		--, cp.objtype

		, [Number of Plans - Single]
			= sum
				(
					case
						when (cp.usecounts &amp;lt; 3) then 1
						else 0
					end
				)

		, [Number of Plans]
			= COUNT(*)

		, [%Number of Single Plans]
			= cast
				(
					sum
					(
						case
							when (cp.usecounts &amp;lt; 3) then 1
							else 0
						end
					) * 100.00
					/ COUNT(*)
					as decimal(10,2)
				)

		, [SizeInMB - Single]
			= 
				sum
				(
					case
						when (cp.usecounts &amp;lt; 3) 
							then cast(cp.size_in_bytes as bigint)
						else 0
					end							
				)
				/ ( 1024 * 1024)

		, [SizeInMB]
			=
				sum
				(
					cast(cp.size_in_bytes as bigint)
				)
				/ ( 1024 * 1024)

		, [%Number Single Plans Storage]
			=
				cast
					(
						sum
						(
							case
								when (cp.usecounts &amp;lt; 3) 
									then cast(cp.size_in_bytes as bigint)
								else 0
							end							
						) * 100.00
						/
						sum
						(
							cast(cp.size_in_bytes as bigint)
						)
					as decimal(10,2)
				)

from    sys.dm_exec_cached_plans cp

cross apply sys.dm_exec_sql_text(cp.plan_handle) st

GROUP BY
	  st.[dbid]
       , DB_NAME(st.[dbid])
--	, cp.objtype

order by
	    [SizeInMB] desc
	  , DB_NAME(st.[dbid]) asc



Output

singleQueryPlansInEachDBPercentile-Prior

 

Explanation

  1. Database = NULL ( plan is not compiled in a specific database )
    • Count
      • Single use plan count :- 37527
      • Total Plan Count :- 37886
      • Percentile :99.05%
    •  Size
      • Single use plan size :- 2.6 GB
      • Total Plan Cache Size :- 2.732 GB
      • Percentile :- 98.14%

 

Remediate

Force parameterization on specific databases


ALTER DATABASE [MSDB] SET PARAMETERIZATION FORCED;

 

Review

I will say wait a couple of days and see if your plan count stables up a bit.

Here is what we saw once we set parameterization to forced.

Plans Aggregated by Cache Type

Output

CachedPlanStatistics

Tally Single Use Plans

Output

SingleUsePlans-After-v2

 

Compare Single Use Plans Vs All Plans

Output

SingleUsePlansPercentile-After-v2

 

Connect Items

  1. Topic :- sql 2005 sql agent (msdb) is filling up procedure cache with adhoc queries
    • ID :- 445041
    • Opened By :- Geoffrey Crombez
    • Date Opened :- 5/8/2009
    • Status : Closed

Summary

In the images shown above, the database id is null, and we had to look at the detailed query text to determine they were msdb/sql server agent gated.

Also, the after effect shown above were taken just a few minutes later.

To truly measure the efficacy of these type of changes, please wait a few days or a week or two.

These problems take time to build-up and so durability of remedy warrant timeline and steadiness.