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–Error–“Database ‘msdb’ cannot be opened due to inaccessible files or insufficient memory or disk space”

Background

Upon starting SQL Server, we received the error posted below.

Error

Image

msdb110_upgrade_20171003_1115PM (cropped up)

Textual

Msg 945

Database ‘msdb’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details

Troubleshooting & Remediation

Outline

  1. Preparation
    • Make a note of Service Accounts used for SQL Server Engine and SQL Server Agent
    • Identify Folder’s location and filenames for msdb data and log files
    • Identify location of msdb install files
      • instmsdb.sql
  2. Inspect
    • Review Accounts’ access
      • Review Accounts NTFS Permissions on msdb data and log files
  3. Get out the way
    • Stop MS SQL Server Engine and Agent services
  4. Get Set
    • Safe keep msdb files
      • Copy msdb data and log files to a neutral location
  5. Go!
    • Start MS SQL Server using trace files
      • Trace Flags
        • -T3608
    • Detach msdb database
      • Using SQLCMD or Management Studio, issue sp_detach_db ‘msdb’
    • Rename existing msdb data and log files
    • Create msdb database
  6. Rollout
    • Stop SQL Server
    • Remove SQL Server Trace Flags
    • Retrace Trace Flags
      • -T3608
  7. Restart SQL Server Engine
  8. Validate
    • Access msdb database

Actual Steps

Preparation

  • Make a note of Service Accounts used for SQL Server Engine and SQL Server Agent
  • Identify Folder’s location and filenames for msdb data and log files
  • Identify location of msdb install files
    • instmsdb.sql

Make Note of SQL Service Accounts

SQL Server Configuration Manager
Image

Explanation
  1. Services
    • SQL Server
      • Local System
    • SQL Server Agent
      • [domain]\svc_sql

 

Identify Folder’s location and filenames for msdb data and log files

Identify the folder where system databases ( master, model, and msdb ) are stored.

Dynamic Management Views

If SQL Server is running in normal mode or within trace flags ( -T 3605 ), please get folders and names of msdb files.

SQL
select 

          [dbid] 
            = tblSMF.database_id
            
        , [database] 
            = db_name(tblSMF.database_id)
            
        , [fileID] 
            = tblSMF.file_id            
            
        , [fileSymbolicName]
            = tblSMF.[name]
        
        , [filePhysicalName]
            = tblSMF.[physical_name]
            
        --, tblSMF.*            
        
from   sys.master_files tblSMF

where  tblSMF.[database_id] <= 4

order by
        tblSMF.database_id asc
        

Image

Explanation
  1. Database
    • msdb
      • MSDBData & MSDBLog noted

 

SQL Server Configuration Manager
Image

Explanation
  1. Startup Parameter
    • -d
      • -dE:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf

 

Identify location of msdb install files

Syntax

[InstallDriveLetter]:\Program Files\Microsoft SQL Server[Version].[InstanceName]\MSSQL\Install

Sample

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Install

 

Inspect

Review Accounts’ access

Review Accounts NTFS Permissions on msdb data and log files
Outline

Here are the steps to take to read out the “Effective” permissions

  1. To view effective permissions on files and folders
    1. Open Windows Explorer, and then locate the file or folder for which you want to view effective permissions.
    2. Right-click the file or folder, click Properties, and then click the Security tab.
    3. Click Advanced, click the Effective Permissions tab, and then click Select button.
      • In the Select Window
        • Enter the name of a user or group, and then click OK.
    4. The selected check boxes indicate the effective permissions of the user or group for that file or folder.
Image

Get Set…

Safe keep msdb files

Copy msdb data and log files to a neutral location

Create a safe location and make copies of files to place in Safe Location.

Image

Go!

Start MS SQL Server using trace files

Trace Flags
Trace Flags Outline
  • Trace Flags
    • -T3608

 

Trace Flag ID What does it do Applicable Effective
3608 It will not start and recover any database except the MASTER system database. So none of the system databases; msdb, tempdb, model, etc will start or recover if this trace flag is used while starting SQL Server. Yes Yes
 902  Trace Flag 902 will bypass the internal upgrade script on startup No  N/A
Trace Flags Steps
SQL Server Configuration Manager
Add(ing) Trace Flag :- -T3608

Add(ed) Trace Flag :- -T3608

 

Start MS SQL Server
SQL Server Configuration Manager

 

Detach msdb
SQL

exec sp_detach_db 'msdb'

 

Rename MSDB Data & Log Files
Image

Create msdb
Command Line

sqlcmd -S . -i instmsdb.sql

SSMS
SSMS – Output

Rollout

Outline

  1. Stop SQL Server
  2. Remove SQL Server Trace Flags
  3. Retrace Trace Flags
    • -T3608

Stop MS SQL Server

Image

Trace Flags
Trace Flags Remove

remove Trace Flags

Trace Flags Remove – removed

Summary

There is a lot of very empty spaces here.

So much was left out to make this post concise and address them during more specific subject materials.

 

References

  1. SQLServerCentral.com
    • mssqlfun
      • How to rebuild MSDB database?
        Link
  2. Microsoft
    • Microsoft \ TechNet \ Windows Server
      • Access Control > Managing Permissions > Set, View, Change, or Remove Permissions on an Object
        Link
    • msdn
      • Sql Server 2012 – Cannot recover the master database
        Link
  3. Trace Flags
    • 3608
      • Paul Randal
        • How to recreate the msdb database in SQL Server 2005?
          Link
    •  902
      • Patrick Keisler, MSFT – Premier Field Engineer
        • Use Trace Flag 902 to Recover from a Cumulative Update Failure
          Posted on :- 2017-July-7th
          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 – SQL Server Agent – Optimizing DB Traffic

Background

In our last post, we spoke about how MSDB is responsible for some of our Single use queries.

Let us dig more in by identifying some of those queries and see if we can change, modify, or reduce their frequency.

 

MSDB – Queries

We have identified a couple of queries and here they are:

  1. msdb.dbo.sp_sqlagent_get_perf_counters
  2. msdb.dbo.sysmail_help_profile_*

 

SQL – msdb.dbo.sp_sqlagent_get_perf_counters

SQL Server Profiler

SQL Server Profiler – Captured

Image

sqlserverprofiler-20160908-0334pm

 

Textual

  1. Text Data :- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
  2. Application Name: – SQLAgent – Alert Engine

 

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters”
    • Every 20 seconds

 

Remediation

Steps

  1. Script out all current SQL Server Agent Alerts
  2. Remove all the alerts
    • Please keep in mind that it is not enough to disable them
Before

sqlserveragentalerts-20160908-0254pm

 

SQL Server Agent – Mail

SQL Server Profiler

Image

sqlserverprofiler-20160908-0426pm

Textual


exec msdb.dbo.sysmail_help_profile_sp @profile_name=N'Local Relay Server'
exec msdb.dbo.sysmail_help_profileaccount_sp @profile_id=1
exec msdb.dbo.sysmail_help_admin_account_sp @account_id=1

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sysmail_help*” commands
    • Every 5 minutes

Remediation

Steps

  1. Access SQL Server Agent Properties
  2. Access the “Alert System” Tab
  3. Disable “Enable Email profile”
Before

alertsystem-before

 

After
alertsystem-after

 

SQL Server Profiler ( After)

Image

sqlserverprofiler-20160908-0437pm

Textual


exec msdb.dbo.sysmail_help_profile_sp @profile_name=N''

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sysmail_help_profile_sp” commands
    • Every 5 minutes
    • Captured Output
      • Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42 profile name is not valid 

Summary

If you are not using SQL Server Agent for alerts and email notification services, you might be able to get away with removing alerts and disabling the email profile setup for SQL Server Agent.

Again, please help in mind we are specifically targeting a v2005 SQL instance.

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.