SQL Server Agent – Configuration on Linux

Background

Post installation of SQL Server on our Linux Box, it is time to review our SQL Server Agent component.

Lineage

  1. SQL Server – Installation on Linux/CentOS
    Link

Check List

Let us go through our checklist.

Outline

Is SQL Server Configured?

SQL Server

Metadata

sys.dm_server_services

SQL

select *
from   sys.dm_server_services 

Output

sys.dm_server_services.pre.20181127.0602AM.PNG

Explanation
  1. sys.dm_server_services
    • Startup Type
      • Automatic
    • Status
      • Stopped
    • Service Account
      • LocalSystem
    • Clustered
      • No

 

Configuration File

/opt/mssql/bin/mssql-conf

List

Let us utilize the SQL Server Configuration Utility ( /opt/mssql/bin/mssql-conf ) to identify configurable elements.

SQL

sudo /opt/mssql/bin/mssql-conf list

Output

mssql-conf.pre.20181127.0607AM.PNG

Explanation
  1. sqlagent.databasemailprofile
    • Database Mail Profile
  2. sqlagent.enabled
    • Is SQL Server Agent Enabled
  3. sqlagent.errorlogfile
    • Error Log File
  4. sqlagent.errorlogginglevel
    • Error Logging Level

 

Configure

/opt/mssql/bin/mssql-conf

Process

Let us utilize the SQL Server Configuration Utility ( /opt/mssql/bin/mssql-conf ) to make configuration changes.

Make Changes

Command

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

Output

mssql-conf.set.20181127.0610AM.PNG

Explanation
  1. sqlagent.enabled
    • Enabled SQL Server Agent

 

Effect Changes

To effect the changes, please use systemctl

Command

sudo systemctl restart mssql-server.service

Output

mssql-conf.set.systemctl.20181127.0732AM

Explanation
  1. Restarted SQL Server Service to effect the changes

 

Configuration

SQL Server

Metadata

sys.dm_server_services

SQL

select *
from   sys.dm_server_services

Output

sys.dm_server_services.post.20181127.0735AM

Explanation
  1. sys.dm_server_services
    • Startup Type
      • Automatic
    • Status
      • Running
    • Process ID
      • 4116 ( pid )
        • Obviously yours will be different
    • Last Startup Time
      • 1600-12-31 16:00:00.0000000 -08:00
        • Not Accurate
    • Service Account
      • LocalSystem
    • Clustered
      • No

Usage

SQL Server Management Studio ( SSMS )

Object Explorer

Images
Images – Before

sqlServerExplorer.pre.20181127.0739AM.PNG

Images – After

sqlServerExplorer.post.20181127.0740AM.PNG

 

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 Agent – Error – “Failed to initialize sqlcmd library with error number -2147467259”

Background

Experienced error running SQL Server Agent job that we are developing.

Error

Error Image

Error Message

Executed as user: LAB\mssql. @profileName :- DBA Mail [SQLSTATE 01000] (Message 0) @recipientsTo :- daniel@lab.org [SQLSTATE 01000] (Message 0) @subject :- Privilege Users on HRDB [SQLSTATE 01000] (Message 0) Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050) @body :- [SQLSTATE 01000] (Error 0). The step failed.

Explanation

  1. The error message states “Failed to initialize sqlcmd library with error number -2147467259.
  2. Unfortunately the error message is not precise

 

TroubleShooting

SQL Server Profiler

Trace Definition

Events Selection

Image

Trace Captured

Image

Explanation

  1. Event
    • Event Class :- User Error Message
    • TextData :- The EXECUTE permission was denied on the object ‘sp_DBRoleMembersList’, database ‘master’, schema ‘dbo’.
    • Application Name :- SQLCMD
    • Error :- 229

SQL Server Agent

SQL Server Agent Account

Let us get SQL Server Agent Account

SQL


select 
        tblSDSS.[servicename]
      , tblSDSS.[service_account]
from   sys.dm_server_services tblSDSS

Output

Explanation

  1. We have the service accounts that we are using for the SQL Server Engine and Agent

Remediation

Grant “SQL Server Agent” service account execute permission on targeted object.

SQL Server Agent Jobs and extraneous contents in logged messages

Background

This morning I needed to dig into a SQL Server Job that was failing, but unfortunately the error was not being exposed\surfaced.

 

Message

Message – Image

Message – Text

Executed as user: SQLADMIN. Jul 11 2017 12:19PM – Transaction Begin …. [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Transaction Began [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Preparing adding records into [UserAcct].[UserActivity] … [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Adding records into [UserAcct].[UserActivity] 0 [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Update records into [UserAcct].[UserActivity] 0 [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Committing Transaction [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Committed Transaction [SQLSTATE 01000] (Message 0). The step succeeded.

 

Explanation

  1. If we take a deep look into the messages pasted above, we will notice multiple occurrence of “[SQLSTATE 01000] (Message 0)

 

Q/A

Googled and found a very helpful comment from Tibor Karaszi.

Tibor Karaszi

How do I suppress [SQLSTATE 01000] messages in sp output?

Link

 

Troubleshooting

SQL Profiler

Ran SQL Server Profiler and here is a screenshot:

Image

 

Outline

  1. SQLAgent – Generic Refresher
  2. SQLAgent – Job Manager
  3. SQLAgent – TSQL JobStep ( Job 0x####…. : Step 1)
    • — network protocol: LPC
      set quoted_identifier on
    • set quoted_identifier off
    • select @@microsoftversion
    • select convert(sysname, serverproperty(N’servername’))
    • SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())
    • SET TEXTSIZE 1024
    • print ‘hello’

Detail

Hello

Hello

  1. Application Name :- SQLAgent – TSQL jobstep ( Job 0x )
  2. NTUserName :- SQLServerAgent
  3. LoginName :- NT Service\SQLServerAgent
  4. Client Process ID :- 2636

 

Task Manager

Reviewed “Task Manager” and confirmed that the process name for process ID is SQLAgent.exe

Remediate

SQL Server Agent Step

Current Job Step Configuration

Revised Job Step Configuration

Changes

  1. Type
    • Original :- Transact-SQL Script (T-SQL)
    • Revised :- Operating System (CmdExec)
  2. Command
    • Original :- exec [csdemo].Products.UpdateCertificatesUserDataAutoConfirm
    • Revised :- sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d demo -Q “exec [csdemo].[Products].[UpdateCertificatesUserDataAutoConfirm]” -b

Explanation

We changed from the internal Transact SQL Script tooling built into SQL Server Agent unto sqlcmd.exe

Sqlcmd exposes a lot more functionality.

 

Recap

Logged Messages

Logged Messages – Original

Logged Messages – Revised

 

Dedicated

Can’t go anywhere but to Tibor Karaszi, SQL Server MVP.

Like Eli Manning an MVP.

SQL Server – SQL Server Agent – Job “Job History (OptimizePlan.Subplan_1)” Failed

Background

Reviewing SQL Server Agent jobs found ones that consistency fails.

One of them is a Maintenance Plan that Organizes Indexes.

 

Error Message

SQL Server Agent

Image

jobhistory-optimizeplan-subplan-20170222-0245pm-cropped-up

 

Textual

Executed as user: DBLAB\sql. … Version 11.0.6020.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 3:00:40 PM Progress: 2017-02-22 15:00:41.05 Source: {04448243-3A2E-4299-BC33-5780CE3F7DEC} Executing query “DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp…”.: 100% complete End Progress Progress: 2017-02-22 15:00:43.82 Source: Progress Progress: 2017-02-22 15:00:43.86 Source: Reorganize Index Task Execut… The package execution fa… The step failed.

 

TroubleShooting

Maintenance Plan

Review the Maintenance Plan and here is what things look like.

OptimizePlan

Image

maintenanceplan_optimizeplan_20170222_0249pm-cropped-up

 

OptimizePlan

Reporting and Logging

Reporting and Logging ( Default )

Here is the default setting with “Log extended information” off.

reportingandlogging_20170222_0250pm

Reporting and Logging ( Post Changes )

Here is what happens when we set “Log extended information” on.

reportingandlogging_20170222_0259pm

 

Logfiles

Went to the identified Log Folder and sought for files matching the maintenance Plan we are trying to dig into.

optimizeplan_folder_20170222_0251pm

 

Logfile Contents

Image

content_20170222_0449pm

Text


USE [LNCD]
GO
ALTER INDEX [NCD_PK] ON [dbo].[NCD] REORGANIZE WITH ( LOB_COMPACTION = ON )
GO
USE [LNCD]
GO
ALTER INDEX [RelatedCase_PK] ON [dbo].[RelatedCase] REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

Reorganize index on Local server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All databases
Object: Tables and views
Compact large objects
Task start: 2017-02-19T00:00:12.
Task end: 2017-02-19T00:01:56.
Failed:(-1073548784) Executing the query "ALTER INDEX [IX_FullNCDCategories_NCDID] ON [dbo]...." failed with the following error:
"The index "IX_FullNCDCategories_NCDID" on table "FullNCDCategories" cannot be reorganized because page level locking is disabled.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:
GO

GO

Explanation

  1. The error Message has the following content
    • USE LNCD
      • Changing to database LNCD
    • Failed:(-1073548784) Executing the query “ALTER INDEX [IX_FullNCDCategories_NCDID] ON [dbo]….” failed with the following error
      • The name of the index is IX_FULLNCCategories
    • “The index “IX_FullNCDCategories_NCDID” on table “FullNCDCategories” cannot be reorganized because page level locking is disabled.”.
      • The targeted table is FULLNCDCategories
    • The Index can not reorganized because page level locking is disabled on the targeted table

 

Remediation

Identify impacted tables

Identify Impacted tables on current database

Code


SELECT 
		  [database] = db_name()

		, [schema]   = schema_name(tblSO.schema_id)

		, [object]   = tblSO.[name]

		, [index]    = tblSI.[name]

		, [allowRowLocks]
			= case ( tblSI.[allow_row_locks])
				when 1 then 'Yes'
				else 'No'
			  end

		, [allowPageLocks]
			= case ( tblSI.[allow_page_locks] )
				when 1 then 'Yes'
				else 'No'
			  end

FROM sys.objects tblSO 

inner join sys.indexes tblSI 

	on tblSO.object_id = tblSI.object_id 

WHERE tblSO.[type] = 'U' 

and ( 
		   ( tblSI.[allow_row_locks] = 0)  
		or ( tblSI.[allow_page_locks] = 0 ) 
	) 

and  tblSO.[is_ms_shipped] = 0 

ORDER BY 
		tblSO.[name]



Image

identifyimpactedtablesoncurrentdb_20170222_0518pm

 

Identify Impacted tables on all databases

Code


DECLARE @commandPLLD	varchar(1000) 

declare @tblObjectPageLevelLockingDisabled TABLE
(
	  [database]			sysname
	, [schema]				sysname
	, [object]				sysname
	, [index]				sysname  null
	, [allow_row_locks]		int null
	, [allow_page_locks]	int null

	, [sqlStatementPreserve] 
			as 
				  'use ' + quoteName([database]) + ';' 
				+ '  '
				+ 'ALTER INDEX '
				+ QuoteName([index])
				+ ' ON '
				+ QuoteName([schema])
				+ '.'
				+ QuoteName([object])
				+ ' '
				+ '	SET (  '
				+ '		   ALLOW_PAGE_LOCKS = '
				+ case([allow_page_locks])
						when 1 then ' ON '
						when 0 then ' OFF '
				  end
				+ '		 , ALLOW_ROW_LOCKS = '
				+ case([allow_row_locks])
						when 1 then ' ON '
						when 0 then ' OFF '
				  end

				+ '		) '


	, [sqlStatementRevise] 
			as 
				  'use ' + quoteName([database]) + ';' 
				+ '  '
				+ 'ALTER INDEX '
				+ QuoteName([index])
				+ ' ON '
				+ QuoteName([schema])
				+ '.'
				+ QuoteName([object])
				+ ' '
				+ '	SET (  '
				+ '		   ALLOW_PAGE_LOCKS = ON  '
				+ '		 , ALLOW_ROW_LOCKS = ON  '
				+ '		) '

)

SELECT @commandPLLD = 'USE [?]; SELECT [database] = db_name(), schema_name(tblSO.schema_id), tblSO.name, tblSI.[name],  tblSI.[allow_row_locks]	, tblSI.[allow_page_locks] FROM sys.objects tblSO inner join sys.indexes tblSI on tblSO.object_id = tblSI.object_id WHERE tblSO.type = ''U'' and ( ( tblSI.allow_row_locks = 0)  or ( tblSI.allow_page_locks = 0 ) ) and  tblSO.[is_ms_shipped] = 0 ORDER BY tblSO.name ' 

insert into @tblObjectPageLevelLockingDisabled
(
	  [database]
	, [schema]
	, [object]
	, [index]
	, [allow_row_locks]		
	, [allow_page_locks]	
)
EXEC sp_MSforeachdb @commandPLLD


select *
		
from   @tblObjectPageLevelLockingDisabled tblPLLD




Explanation

The code snippet above does the following:

  1. Uses sp_MSforeachdb to run the same simple discovery code across all databases
  2. Queries the sys.indexes table looking for
    • allow_page_locks equal to 0
  3. When found it captures the
    • Current SQL for preserving the current Index State
    • The SQL to revise the current state unto set allow_page_lcoks to 1 & allow_page_locks to 1

Output

identifyimpactedtables_20170222_0506pm-croppedup

 

Ran

Captured the script from the sqlStatementRevise column.

Image

jobhistory-optimizeplan-subplan-20170222-0524pm-croppedup

Explanation

All of twenty-seven minutes later, I and we are good.

 

SQL Server – Transaction Log Management – Recovery Mode

Background

It is finally time to revisit a blog posting I started on Dec 21st, 2016.

That post is titled “SQL Server – Error – Cannot shrink log file because the logical file located at the end of the file is in use“.

And, it is here.

Issue

Received an alarm that our database files are growing and so sought to find out why.

 

TroubleShooting

Transaction Log – Utilization Metrics from Performance Counter

transactionLog.IUDF_UtilizationMetricsFromPerformanceCounters

Code

use master
go

if schema_id('transactionLog') is null
begin

	exec('create schema [transactionLog] authorization [dbo]')

end
go

if object_id('[transactionLog].[IUDF_UtilizationMetricsFromPerformanceCounters]') is null
begin

	exec(
			'create function [transactionLog].[IUDF_UtilizationMetricsFromPerformanceCounters]
			()
			RETURNS table
			AS
			RETURN 
			(
				SELECT 
					[shell] = 1/0
		   );
	   	   '
		)

end
go


alter function [transactionLog].[IUDF_UtilizationMetricsFromPerformanceCounters]
()
returns TABLE
as
return
(

	with cte
	(
		  [dbid]
		, [database]
		, [counter]
		, [size]
	)
	as
	(
 
		SELECT
				  [dbid]
					= db_id(RTRIM(instance_name)) 
 
				, [database]
					= RTRIM(instance_name) 
 
				, tblDMOPC.counter_name
 
				, cntr_value log_size_kb
 
		FROM  sys.dm_os_performance_counters tblDMOPC
 
		WHERE tblDMOPC.object_name = 'SQLServer:Databases'
 
		AND   tblDMOPC.counter_name like 'Log File' + '%'
 
		AND   tblDMOPC.instance_name <> '_Total'
 
	)
	, ctePivot
	as
	(
		select *
 
		from   cte
		pivot 
				(
					avg ([size]) 
							for [counter] in
								(
									  [Log File(s) Used Size (KB)]                                                                                                           
									, [Log File(s) Size (KB)]                                                                                                           
								)
				) as avgSize
 
	)
 
	select 

			  [cteP].[dbid]

			, [cteP].[database]
 
			, [usedMB] = [Log File(s) Used Size (KB)]
							/ 1000
 
			, [totalMB] =  [Log File(s) Size (KB)]
							/ 1000
 
			, [%]
				= cast
					(
						( [Log File(s) Used Size (KB)] * 100.00)
						/ ( [Log File(s) Size (KB)])
						as decimal(10,2 )
					)

			, [recoveryModel]
				= tblSD.recovery_model_desc

			, [log reuse wait]
				= tblSD.log_reuse_wait_desc
			
	from   ctePivot cteP

	inner join sys.databases tblSD

		on cteP.[dbid] = tblSD.database_id
 

)
go


Output

qa_20170207_0958pm

Explanation

  1. Before capturing the screenshot above, our target database, QA_rbpivr1, was in FULL RECOVERY
    • It’s transaction log size was 8 GB
    • And, 3.2 GB of the 8GB is in use

 

Why so much Transaction Log is being held up?

Recovery Mode

Again for QA_rbpiv1  3.2 GB is being used.

Let us review how database is configured

qa_recoverymode_20170207_1009am

We see that the Recovery Mode for our database is Full.

Backup Dates

databaseproperty_options_general_backupdate_qa_20170207_1013am

 

Explanation
  1. The last full backup was taken last night Feb 2nd, 2017 and it completed at 8:35 PM
  2. And, the last Transaction backup was taken on Sunday Feb 5th, 2017 at 6 PM

 

 

Transaction Backup History

Let us review our Transaction Backup History

 

sqlserveragent_transactionbackuphistory_qa_20170207_1019am

Explanation:

  1. Before today, transaction backups were occurred on 2/5/2017, 1/29/2017, 1/22/2017
  2. And, they were occurring each hour
  3. Looked back at the Calendar and those days fell on Sundays

 

 

Review Scheduled Transaction Backup

jobproperties-databasebackup-userdatabases-log-schedules-sunday-eachhour

 

Explanation:

  1. Schedule is Weekly on Sundays
  2. And, the frequency is each hour

 

Correction

Our bad.

Revise Schedule

sqlserveragent_transactionbackuphistory_qa_20170207_1028am

Explanation:

  1. Changed Schedule’s frequency from Weekly to Daily

 

Metadata

Review Checkpoints in Error Log File

Enable Logging of Checkpoints in ErrorLog file

Enable Trace Flags

Code

dbcc traceon (3502, 3504, 3605, -1 )

Review

Code

dbcc tracestatus

Output

dbcctracestatus

Review ErrorLog file

Let us review our error logs and make sure that Checkpoints are indeed been logged.

logfileviewer_20170207_1220pm

 

transactionLog.sp_checkPointsInErrorLogFileAggregate

 

Code

 


use [master]
go

if object_id('transactionLog.sp_checkPointsInErrorLogFileAggregate') is null
begin

	exec('create procedure [transactionLog].[sp_checkPointsInErrorLogFileAggregate] as ')

end
go

alter procedure [transactionLog].[sp_checkPointsInErrorLogFileAggregate] 
as  

begin

	set nocount on;
  
	declare @p1  INT
	declare @p2  INT
	declare @p3  VARCHAR(255)
	declare @p4  VARCHAR(255)
  
	declare @tblErrorLog     TABLE
	(
		  [id] int not null
			identity(1,1)
  
		, [LogDate] datetime
  
		, [ProcessInfo] sysname
  
		, [Text]        varchar(600) null
  
		, primary key
			(
				  [LogDate]
				, [id]
			)
  
		, INDEX INDX_ID
			(
				[id]
			)
  
	)
  
	set @p1 = 0 -- current
	set @p2 = 1 -- error log
	set @p3 = 'Ckpt'
	set @p3 = 'Ckpt dbid'
	set @p4  = ''
  
	insert into @tblErrorLog
	(
		  [LogDate]
		, [ProcessInfo]
		, [Text]        
	)
	exec [sys].[sp_readerrorlog]
			 @p1   = @p1  
		   , @p2   = @p2
		   , @p3   = @p3
		   , @p4   = @p4
  
  
	select
			  tblSD.[name]
  
			, tblSD.log_reuse_wait_desc
  
			, [count] 
				= count(tblEL.LogDate)
  
			, [LogDateMin]
				= min(tblEL.LogDate)
  
			, [LogDateMax]
				= max(tblEL.LogDate)
   
	from  sys.databases tblSD
  
	left outer join @tblErrorLog tblEL
  
			on   tblEL.[Text]  like '%' + cast(tblSD.database_id as varchar(10)) + '%'
          
			and  tblEL.[Text] like 'Ckpt dbid ' + '%' + 'started'
 
	group by
  
			   tblSD.[name]
			 , tblSD.log_reuse_wait_desc
  
	order by
  
			   tblSD.log_reuse_wait_desc asc
			 , count(tblEL.LogDate) desc
			 , tblSD.[name]

end
go



 

Output

errorlogfile_checkpoints_transactionlogcheckpoint_20170207_1036am

Explanation

  1. Database
    • QA_rbpivr1
      • LogDateMax
        • From digging in the Transaction Log File, we can see that the last Transaction Log on 2017-02-06 20:34
        • The timeline is Yesterday, Monday Feb 6th, 2017 8:34 PM
      • LogDateMin
        • LogDateMin is 2017-01-15 04:00
        • In readable term Jan 15th, 2017 @ 4 AM

 

Source Control

GitHub

  1. DanielAdeniji/SQLServerTransactionLogMetrics
    Link

 

Summary

In summary, our transaction size is growing and not being pruned because unfortunately our scheduled Transaction backup job was incorrectly set to only occur on Sundays.

Please keep in mind this only affects databases set for FULL RECOVERY.

From the metrics above, we can see that our identified database Transaction Log Checkpoints are only occurring at the tail end of Full Backups.

And, as aforementioned on Sundays.

 

SQL Server – Messages – Backup

Background

Reviewing SQL Server Agent jobs and came upon some messages that I wanted to be sure do not pose any risk.

 

SQL Server Agent

Backup Jobs

backupdatabaselogbrushed-up

 

Messages

The specific messages that we will be digging more into are:

  1. 3014
  2. 3211

 

What do the messages mean?

Code


set nocount on;

declare @language		   sysname
declare @languageID		   int	

declare @languageIDDefault int
declare @languageIDEnglish int

declare @sqlMessageID_3014 int
declare @sqlMessageID_3211 int

set @sqlMessageID_3014 = 3014
set @sqlMessageID_3211 = 3211

set @languageIDDefault = 0 
set @languageIDEnglish = 1033

set @language        = @@language

/*
	Message
		Executed as user: LABDOMAIN\sqlAgent. ...rcent processed. [SQLSTATE 01000] (Message 3211)  
		19 percent processed. [SQLSTATE 01000...  The step succeeded.
*/
if (@language is not null)
begin

	select @languageID	= tblSL.[langid]
	from   sys.syslanguages tbLSL
	where  tblSL.[name] = @language

end

if (
		(@languageID is null)
	)
begin

	set @languageID = @languageIDDefault

end

print 'Default Language ' + isNull(@language, '')
print 'Language ID ' + cast(isNull(@languageID, -1) as sysname)

select top 100 *
from   sys.messages tbLSM
where  (
			   (tblSM.language_id = @languageID)
			or (tblSM.language_id = @languageIDEnglish)
	   )		
and    tblSM.message_id	in
		 (
			  @sqlMessageID_3014		
			, @sqlMessageID_3211
		 )


 

Explanation

What we did is query the master.sys.messages table

We filtered on

  1. The messages ID
    • 3014
    • 3211
  2. And, passed along the language ID

 

Output

output-grid

 

Explanation

Message ID Severity  Interpretation
 3014 10  At the completion of backups, we are told how many pages were backed, how long it took, and throughput
 3211  10  When the stats option is passed, backup and restore commands render percentile status message on the progress

 

BTW, Severity 10 is an informational message as demonstrated here.

Database Engine Error Severities

levelsofseverity

 

Summary

If bothered by the 3211 message please remove the stats operand from the backup command.

Using the 3014 message, backup completions are logged to both the console and SQL Server’s errorlog.

Please enable the 3605 Trace Flag to suspend logging of backup completion to the errorlog.

It is not clear if one is able to dissuade logging to the console.