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.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s