SQL Server Agent – Output File Name – Tokenization

Background

I have always been perplexed by the fact that Sql Server Management Studio does not allow us to indicate that we desire timestamp file names for SQL Server Agent Jobs Logging.

SSMS

SQL Server Agent

Job Step Properties

Job Step Properties – Advanced

Initial
Image

Explanation
  1. Output file

 

Usage

Yesterday, I found out that timestamps are actually supported.

General – Image

Advanced – Image

Advanced – Textual

E:\Microsoft\SQLServer\SQLServerAgent\Log\DatabaseBackupFiles_$(ESCAPE_SQUOTE(JOBID))$(ESCAPE_SQUOTE(STEPID))$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

 

Payload

cleanupFilesBak.cmd

Script


@echo off

REM How to use multiple commands in batch using forfiles command
REM https://stackoverflow.com/questions/23318731/how-to-use-multiple-commands-in-batch-using-forfiles-command

setlocal

set "_folderBase=G:\Microsoft\SQLServer\Backup"
set "_SQLInstance=%computername%"
set "_folderTarget=%_folderBase%\%_SQLInstance%\"

set "_fileExt=*.bak"

set "_daysPast=1"

set _appPgm=echo 

REM Added period (.) after folder name for /p argument
if not exist "%_folderTarget%." mkdir "%_folderTarget%"

forfiles /P "%_folderTarget%." /s  /m %_fileExt% /d -%_daysPast% /c ^"cmd /c ^
%_appPgm% ^
  file name is @PATH ^
  file size is @FSIZE ^
  file date is @FDATE ^
^"

endlocal

Output

Explanation

  1. DatabaseBackupFiles_[JobID]_[stepID]_[DateYYYYMMDD]_[timeHHMMSS].txt

 

Tokens

Token Description Version Active
A-DBN Active Database name
(A-SVR) SQL Server Name
(JOBNAME) The name of the Job Not active as of v2014/SP2
(STEPNAME) The name of the step Not active as of v2014/SP2
(DATE) Current date (in YYYYMMDD format).
(INST) Instance name. For a default instance, this token will have the default instance name: MSSQLSERVER.
(JOBID) Job ID
(MACH) Computer name
(SQLDIR) The directory in which SQL Server is installed. By default, this value is C:\Program Files\Microsoft SQL Server\MSSQL.
(SQLLOGDIR) Replacement token for the SQL Server error log folder path – for example, $(ESCAPE_SQUOTE(SQLLOGDIR)).
(STEPID) Step ID.
(SRVR) Name of the computer running SQL Server. If the SQL Server instance is a named instance, this includes the instance name.
(TIME) Current time (in HHMMSS format).
(STRTTM) Current time (in HHMMSS format).
(STRTDT) The date (in YYYYMMDD format) that the job began executing.

 

Dedicated

Dedicated to Ola Hallengren as I found out about this functionality through his script.

And, also to Dave Benham.

Dave has a nice explanation on how to escape commands in batch files and thus span multiple lines.

His explanation is here and it is in response to a Stackoverflow question.  The Question is titled “How to use multiple commands in batch using forfiles command“.

 

Listening

Listening to “Drifting Spirits” @ Teavolve.

Link

References

  1. Docs / SQL / SSMS / Agent
    • Use Tokens in Job Steps
      Link
  2. Thomas Lind
    • SQL Agent job logging with tokens
      Link

 

Microsoft Access – Querying SQL Server Table – Schema Stability Lock

Background

A quick follow-up to a post over the weekend.

The forwarding post is titled “SQL Server – Index Rebuild – Blocked / Blocking” and it is here.

In that post we spoke of how a scheduled Business Back Office Job was hung and could not proceed.

Using Adam Mechanic’s sp_whoIsActive, we discovered we had were indeed experiencing session blocking, but not an actual deadlock, which in fact would have triggered a vote as to which session to abort

We stopped the blocking database maintenance job which is an Index Defrag job.  The Index Defrag job simply calls Ola Hallengren’s IndexOptimize Stored Procedure.

TroubleShooting

TroubleShooting – Day 1

Adam Machanic

dbo.sp_WhoIsActive

Code

exec  [dbo].[sp_WhoIsActive]

Image
Session

SQL Text

Explanation
  1. Sessions
    • Login :- app
      • We are familiar with the app account
      • The sessions are the bottom two
    • SQL :- OpenRowSet
      • A SQL Server Profiler Trace we initiated to track the ongoings
    • Status :- suspended // wait_info :- ASYNC_NETWORK_IO
      • used_memory
        • 10, 000 KB ( 10 MB)

 

TroubleShooting – Day 2

Microsoft

Dynamic Management Views

sys.dm_exec_sessions
SQL

declare @appNameMSFTODBC sysname
declare @appNameMSFTOffice sysname
declare @appNameMSFTSSMS sysname
declare @appNameMSFTJavaJDBC sysname

declare @clienInterfaceName sysname

set @appNameMSFTODBC = 'Microsoft® Windows® Operating System'
set @appNameMSFTOffice = 'Office'

set @appNameMSFTSSMS = 'Microsoft SQL Server Management Studio - Query'
set @appNameMSFTJavaJDBC = 'Microsoft JDBC Driver for SQL Server'

set @clienInterfaceName = 'Microsoft JDBC Driver 4.0'

select 
		  tblDES.session_id
		, tblDES.program_name
		, tblDES.transaction_isolation_level
		, tblDES.open_transaction_count
		, tblDES.host_name
		, tblDES.client_interface_name
		, tblDES.client_version
		, tblDES.[status]
		, tblDES.[row_count]
		, tblDES.[prev_error]
		, tblDES.reads
		, tblDES.last_request_start_time
		, tblDES.last_request_end_time
		, [timeSinceLastCommunicationInMinutes]
			= datediff
				(
					  minute
					, tblDES.last_request_end_time
					, getdate()
				)

from  sys.dm_exec_sessions tblDES

where  tblDES.session_id >= 50

and	   (
			(
						
				   ( tblDES.program_name like @appNameMSFTODBC )
				or ( tblDES.program_name like '%' + @appNameMSFTOffice + '%' )

			)
			and
			(

				       ( tblDES.program_name != @appNameMSFTSSMS )
				   and ( tblDES.program_name != @appNameMSFTJavaJDBC )
			)

	   )


Output

 

Microsoft SQL Server Profiler

Image

Explanation

Here is the conversation captured:

  1. SQL:BatchStarting
    • SELECT Config, nValue FROM MSysConf
  2. SQL:BatchStarting
    • SELECT “dbo”.”oe_dep_audit”.”oe_dep_emp_location”,”dbo”.”oe_dep_audit”.”oe_dep_emp_ssn”,”dbo”.”oe_dep_audit”.”oe_year”,”dbo”.”oe_dep_audit”.”oe_dep_no”,”dbo”.”oe_dep_audit”.”oe_dep_session_ID”,”dbo”.”oe_dep_audit”.”oe_dep_record_type”,”dbo”.”oe_dep_audit”.”oe_dep_record_flag” FROM “dbo”.”oe_dep_audit”
      • Gets all the records in table
  3. SQL:BatchStarting
    • SELECT CASE DATABASEPROPERTYEX( DB_NAME(), ‘Updateability’) WHEN ‘READ_ONLY’ THEN ‘Y’ ELSE ‘N’ END
  4. RPC:Completed
    • SQL
      • declare @p1 int
        set @p1=1
        exec sp_prepexec @p1 output,N’@P1 char(2),@P2 char(9),@P3 char(4),@P4 int,@P5 char(10),@P6 char(1),@P7 char(1)’,N’SELECT “oe_dep_emp_location”,”oe_dep_emp_ssn”,”oe_year”,”oe_dep_no”,”oe_dep_session_ID”,”oe_dep_record_type”,”oe_dep_record_flag”,”oe_dep_name”,”oe_dep_birthdate”,”oe_dep_relationship”,”oe_dep_SSN”,”oe_dep_sex”,”oe_dep_disabled”,”oe_dep_medical”,”oe_dep_dental”,”oe_dep_optical”,”oe_dep_legal”,”oe_dep_PCP”,”oe_dep_current_patient”,”oe_dep_deleted” FROM “dbo”.”oe_dep_audit” WHERE “oe_dep_emp_location” = @P1 AND “oe_dep_emp_ssn” = @P2 AND “oe_year” = @P3 AND “oe_dep_no” = @P4 AND “oe_dep_session_ID” = @P5 AND “oe_dep_record_type” = @P6 AND “oe_dep_record_flag” = @P7′,’01’,’000000000′,’2012′,1,’a733167067′,’B’,’ ‘
        select @p1
    • Prepares a fetch Statement
  5. RPC:Completed
    • SQL
      • exec sp_execute 2,’01’,’00140′,’2013′,3,’a08938′,’B’,’ ‘,’01’,’00140′,’2013′,3,’a08938′,’O’,’A’,’01’,’00140′,’2013′,3,’a57784′,’O’,’A’,’01’,’00140′,’2013′,3,’a94593′,’O’,’ ‘,’01’,’00140′,’2013′,4,’a08938′,’B’,’ ‘,’01’,’00140′,’2013′,4,’a08938′,’O’,’A’,’01’,’00140′,’2013′,4,’a577848305′,’O’,’A’,’01’,’0014′,’2013′,4,’a94593′,’O’,’ ‘,’01’,’00154′,’2005′,100,’R50001′,’O’,’ ‘,’01’,’00154′,’2006′,1,’N6000′,’B’,’ ‘

 

Microsoft Network Monitor

Filter

//IP Address
(

    ( IPv4.Address == 10.1.20.182 )

)
and 
(

	(
          not ( Conversation.ProcessName == "Ssms.exe")
    )

	and
        ( 
             not ( Conversation.ProcessName == "PROFILER.exe")
        )
)

Traffic
Image

Explanation
  1. Using a Network monitor tool we can see that there is quite a bit of ongoing Network Activity between the client node running MS Access and the Database Server
  2. The protocols are plain TCP and TDP
  3. And, the ports are the default SQL Server Port of 1433 and the ephemeral ports from the Source Node

 

Summary

When the table queried from MS Access is reasonably large, the database connection is kept opened.  And, the DB table is locked with an object stability lock.

It is a designed behavior by MS Access and it reduces the amount of local resources on the client host.

SQL Server – Index Rebuild – Blocked / Blocking

Background

Got a call about a hung database job.

 

Check Current Sessions

sp_whoIsActive

Code


exec sp_whoIsActive

Output

Explanation

  1. Session ID :- 161
    • Alter Index Session
    • Being blocked by Session ID :148
  2. Session ID :- 148
    • Select Statement

 

TroubleShooting

Why Is Select Blocking?

sp_block

Let us issue sp_block against the blocker and blocked sessions.

Code


-- exec sp_help sp_lock
declare @spidIndexOptimize int
declare @spidBlocker int

set @spidIndexOptimize = 161
set @spidBlocker = 148

exec sp_lock
		  @spid1 = @spidIndexOptimize 
		
exec sp_lock
		  @spid1 = @spidBlocker

 

Output

 

Explanation

  • Session ID :- 161
    • Schema Stability
    • Schema Modification
  • Session ID :- 148
    • Schema Stability

 

Why Is Index Reorg / Rebuilt?

sysindexes

Let us see how many records we have and how many records have changed.

Code


select 
		  [table] = 
					  object_schema_name(tblSI.id)
					+ '.'
					+ object_name(tblSI.id)
		, tblSI.indid
		, tblSI.[name]
		, tblSI.[rowcnt]
		, tblSI.[rowmodctr]
from   sysindexes tblSI
where  tblSI.indid In ( 0, 1)
and    tblSI.id = object_id('dbo.event_log_bkp')

;

 

sys.dm_db_index_physical_stats

Fragmentation %

Code


use [rbpivr1]
go

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  

SET @db_id = DB_ID(N'rbpivr1');  
SET @object_id = OBJECT_ID('dbo.event_log_bkp');  

IF @db_id IS NULL  
BEGIN;  
    PRINT N'Invalid database';  
END;  
ELSE IF @object_id IS NULL  
BEGIN;  
    PRINT N'Invalid object';  
END;  
ELSE  
BEGIN

	SELECT
			  tblSI.[name]
			--, tblSI.type_desc
			, tblSI.[index_id]
			, tblDIPS.index_type_desc
			, tblDIPS.page_count
			--, tblDIPS.record_count
			--, tblDIPS.*
			, tblDIPS.avg_fragmentation_in_percent 

	FROM   sys.dm_db_index_physical_stats
			(
				  @db_id
				, @object_id
				, NULL
				, NULL 
				, 'LIMITED'
			) tblDIPS

	inner join sys.indexes tblSI
		on   tblDIPS.[object_id] = tblSI.[object_id]
		and  tblDIPS.[index_id] = tblSI.[index_id]
			;  
END;

Output

Explanation

It appears that a couple of indexes are eligible for re-org and others for a rebuild.

Remediation

Handle Blocking

Documentation

Found out that Olla Hallengren’s code has a built-in mechanism for handling blocking.

The documentation is here.

Image

Explanation

  1. WaitAtLowPriorityMaxDuration
    • How long to wait in minutes
  2. WaitAtLowPriorityAbortAfterWait
    • Options
      • NONE
        • Continue waiting for Locks
        • Default Option
      • SELF
        • Abort the online index rebuild operation.
        • Terminate the Index Rebuild Option
      • BLOCKERS
        • Kill user transactions that block the online index rebuild operation.
        • Terminate the Blocker

 

Choice

Our choice, when blocked, will be to wait 10 minutes and terminate the Index Rebuild Option

Code


EXECUTE [AdminDB].dbo.IndexOptimize
	  @Databases = 'USER_DATABASES'
	, @FragmentationLow = NULL
	, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
	, @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'
	, @FragmentationLevel1 = 5
	, @FragmentationLevel2 = 30
	, @UpdateStatistics = 'ALL'
	, @OnlyModifiedStatistics = 'Y'
	, @LogToTable = 'Y'
	--20170813 9:17 PM dadenji
	, @WaitAtLowPriorityMaxDuration=10
	, @WaitAtLowPriorityAbortAfterWait='SELF'

Generated Code

Here is the code that is generated when we make the change outlined above:

 

References

  1. Michael J Swart ( Database Whisper )
    • The Sch-M Lock is Evil
      Link

SQL Server – DBCC Failed on Data Purity Check

Background

Reviewing SQL Server Agent Jobs and noticed that one of Database Maintenance Job was disabled.

Enabled and ran it, and it errored out.

 

Failed Job

Here is the failed Job…

StartJobs-20170303-0521PM (Cropped up)

 

SQL Dump

Unfortunately, not only did the job fail, it created SQL Dump files, as well.

Here is a sample of the created dump files.

Image

SQLDump0333_log_20170303_0526PM ( cropped-up)

 

Textual

 

 


2017-03-03 17:05:26.22 spid60      DBCC CHECKDB (SGS) WITH all_errormsgs, no_infomsgs, data_purity executed by LAB\dbsvc found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.  Internal database snapshot has split point LSN = 00013838:00000171:0001 and first LSN = 00013838:00000170:0001.
2017-03-03 17:07:08.99 spid60      DBCC CHECKDB (SRPA) WITH all_errormsgs, no_infomsgs, data_purity executed by LAB\dbsvc found 10 errors and repaired 0 errors. Elapsed time: 0 hours 1 minutes 42 seconds.  Internal database snapshot has split point LSN = 003029a5:0001b40e:0001 and first LSN = 003029a5:0001b40d:0001.
2017-03-03 17:07:09.14 spid60      Using 'dbghelp.dll' version '4.0.5'
2017-03-03 17:07:09.18 spid60      **Dump thread - spid = 0, EC = 0x0000000175F700F0
2017-03-03 17:07:09.19 spid60      ***Stack Dump being sent to E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0329.txt
2017-03-03 17:07:09.20 spid60      * *******************************************************************************
2017-03-03 17:07:09.20 spid60      *
2017-03-03 17:07:09.20 spid60      * BEGIN STACK DUMP:
2017-03-03 17:07:09.20 spid60      *   03/03/17 17:07:09 spid 60
2017-03-03 17:07:09.20 spid60      *
2017-03-03 17:07:09.20 spid60      * DBCC database corruption
2017-03-03 17:07:09.20 spid60      *
2017-03-03 17:07:09.21 spid60      * Input Buffer 196 bytes -
2017-03-03 17:07:09.21 spid60      *             EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DAT
2017-03-03 17:07:09.21 spid60      *  ABASES', @LogToTable = 'Y'
2017-03-03 17:07:09.21 spid60      *  
2017-03-03 17:07:09.21 spid60      * *******************************************************************************
2017-03-03 17:07:09.21 spid60      * -------------------------------------------------------------------------------
2017-03-03 17:07:09.21 spid60      * Short Stack Dump
2017-03-03 17:07:09.38 spid60      Stack Signature for the dump is 0x00000000000000F4
2017-03-03 17:07:23.11 spid60      External dump process return code 0x20000001.
External dump process returned no errors.

2017-03-03 17:09:07.28 spid60      DBCC CHECKDB (SRPA_CORRUPT2) WITH all_errormsgs, no_infomsgs, data_purity executed by LAB\dbsvc found 10 errors and repaired 0 errors. Elapsed time: 0 hours 1 minutes 41 seconds.  Internal database snapshot has split point LSN = 00302996:0000d702:0001 and first LSN = 00302996:0000d701:0001.
2017-03-03 17:09:07.28 spid60      **Dump thread - spid = 0, EC = 0x0000000175F700F0
2017-03-03 17:09:07.28 spid60      ***Stack Dump being sent to E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0330.txt
2017-03-03 17:09:07.28 spid60      * *******************************************************************************
2017-03-03 17:09:07.28 spid60      *
2017-03-03 17:09:07.28 spid60      * BEGIN STACK DUMP:
2017-03-03 17:09:07.28 spid60      *   03/03/17 17:09:07 spid 60
2017-03-03 17:09:07.28 spid60      *
2017-03-03 17:09:07.28 spid60      * DBCC database corruption
2017-03-03 17:09:07.28 spid60      *
2017-03-03 17:09:07.28 spid60      * Input Buffer 196 bytes -
2017-03-03 17:09:07.28 spid60      *             EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DAT
2017-03-03 17:09:07.28 spid60      *  ABASES', @LogToTable = 'Y'
2017-03-03 17:09:07.28 spid60      *  
2017-03-03 17:09:07.28 spid60      * *******************************************************************************
2017-03-03 17:09:07.28 spid60      * -------------------------------------------------------------------------------
2017-03-03 17:09:07.28 spid60      * Short Stack Dump
2017-03-03 17:09:07.35 spid60      Stack Signature for the dump is 0x00000000000000F4 

 

 

 

Troubleshooting

Review Job

Reviewed the Job and it’s lone step.

 

Repair Database

Scripted out DBCC/Repair Rebuild

Outline

  1. Place database in single user mode
  2. Initiate new transaction
    • Issue DBCC with following options
      • Data purity check
      • Using Repair/Rebuild, attempt to repair errors
      • Display all error messages
      • Withhold informational messages
  3. Exit Database from single user mode

Script

 
use master;

ALTER DATABASE [HRDB]
	SET SINGLE_USER
		WITH ROLLBACK IMMEDIATE;
GO

set nocount on;
set XACT_ABORT on;

declare @dateBegin datetime
declare @dateDBCCCompleted datetime
declare @dateFullCompletion datetime

declare @strLog varchar(80)
declare @DATE_STYLE int

declare @NUMBER_OF_COLUMNS int
declare @CHAR_LINEBREAK varchar(600)

set @NUMBER_OF_COLUMNS = 120
set @DATE_STYLE = 100
set @CHAR_LINEBREAK = replicate('=', @NUMBER_OF_COLUMNS)

begin tran

	print @CHAR_LINEBREAK
	set @dateBegin = getdate()
	set @strLog = 'Date DBCC Began :- ' + convert(varchar(20), @dateBegin, @DATE_STYLE)
	print @strLog

	print @CHAR_LINEBREAK


		DBCC CHECKDB
		(
			  [HRDB]
		   ,  REPAIR_REBUILD 
		)
		with
			    ALL_ERRORMSGS 
			  , no_infomsgs
			  , data_purity


	print @CHAR_LINEBREAK

	set @dateDBCCCompleted = getdate()
	set @strLog = 'Date DBCC Completed :- ' + convert(varchar(20), @dateDBCCCompleted, @DATE_STYLE)
	print @strLog

	print @CHAR_LINEBREAK

rollback tran

	set @dateFullCompletion = getdate()
	set @strLog = 'Date Full Completion :- ' + convert(varchar(20), @dateFullCompletion, @DATE_STYLE)
	print @strLog

	print @CHAR_LINEBREAK

go

ALTER DATABASE [HRDB]
	SET MULTI_USER
		WITH ROLLBACK IMMEDIATE;
GO


 

 

Output

Image

DBCCCheckDB_ProcessLog_20170303_0559PM

 

Textual

 
========================================================================================================================
Date DBCC Began :- Mar 3 2017 5:56PM
========================================================================================================================
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 12 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 13 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 14 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 59 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 60 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 61 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 62 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1237088), slot 63 in object ID 1549248574, index ID 1, partition ID 72057616969498624, alloc unit ID 72057616977821696 (type "In-row data"). Column "Holding" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
CHECKDB found 0 allocation errors and 8 consistency errors in table 'usr_Portfolio_Dirty_Data' (object ID 1549248574).
Msg 2570, Level 16, State 3, Line 35
Page (1:1781040), slot 0 in object ID 1585141138, index ID 1, partition ID 72057616957440000, alloc unit ID 72057616965697536 (type "In-row data"). Column "Value" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
Msg 2570, Level 16, State 3, Line 35
Page (1:1781040), slot 1 in object ID 1585141138, index ID 1, partition ID 72057616957440000, alloc unit ID 72057616965697536 (type "In-row data"). Column "Value" value is out of range for data type "real". Update column to a legal value.
The system cannot self repair this error.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'mkt_FactorData13' (object ID 1585141138).
CHECKDB found 0 allocation errors and 10 consistency errors in database 'HRDB'.
========================================================================================================================
Date DBCC Completed :- Mar 3 2017 5:58PM
========================================================================================================================
Date Full Completion :- Mar 3 2017 5:58PM
========================================================================================================================
 

 

 

Explanation

  • Two tables and columns combination cited
    • Table :- usr_Portfolio_Dirty_Data, Column :- Holding
    • Table :- mkt_FactorData13, Column :- Value
  • All cited columns are defined as having the real datatype
  • Message reads
    • Column “Holding” value is out of range for data type “real”.  Update column to a legal value.
      The system cannot self repair this error.
    • Column “Value” value is out of range for data type “real”.  Update column to a legal value.
      The system cannot self repair this error.

 

 

Data Purity Check

The data purity check cited that some values are not legal.

The data type for the columns cited is real.

 

Validation

 

[constant].[numberMax]

Code


use [master]
go

if schema_id('CONSTANT') is null
begin

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

end
go

if object_id('[constant].[numberMax]') is null
begin

	exec('create view [constant].[numberMax] as select [shell] = 1/0 ')

end
go

alter view [constant].[numberMax] 
with schemabinding
as 
	select 

		/*
			real
				a) - 3.40E + 38 to -1.18E - 38
				b) 0 
				c) 1.18E - 38 to 3.40E + 38	
				
				Size
					:- 4 Bytes

		*/
		  [realNumber1Low] 
			= cast(-3.40E+38 as real)
		
		, [realNumber1High] 
			= cast(-1.18E-38 as real)

		, [realNumber2Low] 
			= cast(1.18E-38 as real)

		, [realNumber2High] 
			= cast(3.40E+38 as real)

go

grant select on [constant].[numberMax] to [public]
go

[dbo].[fn_DecimalPlaces]

Code


use master
go

IF NOT EXISTS 
(
	SELECT * 
	FROM   sys.objects tblSO
	WHERE  tblSO.object_id = object_id('[dbo].[fn_DecimalPlaces]')

)
begin

	exec('create function [dbo].[fn_DecimalPlaces]() returns tinyint as begin return (-1) end ');

end

GO

ALTER FUNCTION [dbo].[fn_DecimalPlaces]
(
	@A float
)
RETURNS int
with schemabinding
AS
BEGIN

	/*
		Topic  :- Count Decimal Places
		Author :- Sergiy
		URL    :- https://www.sqlservercentral.com/Forums/Topic314390-8-1.aspx
	*/
	declare @R int

	IF ( @A IS NULL )
	begin

		RETURN NULL

	end


	set @R = 0

	while @A - str(@A, 18 + @R, @r) <> 0
	begin 

		SET @R = @R + 1

	end

	RETURN @R

END
GO

grant execute on [dbo].[fn_DecimalPlaces] to public
go


 

dbo.Holding

Code



SELECT
		top 15
		  [Holding]
		--, [HoldingTryParse] = TRY_PARSE([Holding] as real) 
		, [HoldingAsFloat] = cast([Holding] as float)
		, [Length] = len([Holding])
		, [NumberofDecimalPlaces]
			= dbo.fn_DecimalPlaces([Holding])

from   [dbo].[usr_Portfolio_Dirty_Data] tblPDD

cross apply [constant].[numberMax] vwCNM

where  (

			  ( [Holding] <> 0.0 )

		/*
			AND 
				(
					    ( [Holding] < CONVERT(real,1.18E-38) OR [Holding] > CONVERT(real,3.40E+38) ) 
					AND ( [Holding] < CONVERT(real,-3.40E+38) OR [Holding] > CONVERT(real,-1.18E-38) )
				)

		*/

			AND 
				(
					(
							  ( [Holding] < [realNumber2Low] ) OR ( [Holding] >  [realNumber2High] )

					)

					AND
					(
							  ( [Holding] < [realNumber1Low] ) OR ( [Holding] >  [realNumber1High] )

					)

				)

	)		



Output

validatedata_holding_20170304_1201m

dbo.mkt_FactorData13

Code

 

 
SELECT  top 5 
		  [WSCode]
		, [DataDate]
		, [Value]
		--, [ValueTryParse] = TRY_PARSE([Value] as real) 
		, [ValueAsFloat] = cast([Value] as float)
		, [ValueIsNumeric] = IsNumeric([Value])
		, [Length] = len([Value])
		, [NumberofDecimalPlaces]
			= [master].dbo.fn_DecimalPlaces([Value])

from   [dbo].[mkt_FactorData13] tblMKT

cross apply [constant].[numberMax] vwCNM

where  (

			  ( [Value] <> 0.0 )
			
			/*
				WHERE col2<>0.0 
				AND (col2 < CONVERT(real,1.18E-38) OR col2 > CONVERT(real,3.40E+38)) 
				AND (col2 < CONVERT(real,-3.40E+38) OR col2 > CONVERT(real,-1.18E-38)) 
			*/

			/*
			AND 
				(
					    ( [Value] < CONVERT(real,1.18E-38) OR [Value] > CONVERT(real,3.40E+38) ) 
					AND ( [Value] < CONVERT(real,-3.40E+38) OR [Value] > CONVERT(real,-1.18E-38) )
				)

			*/

			AND 
				(
					(
							  ( [Value] < [realNumber2Low] ) OR ( [Value] >  [realNumber2High] )

					)

					AND
					(
							  ( [Value] < [realNumber1Low] ) OR ( [Value] >  [realNumber1High] )

					)

				)
	)		



 

 

Output

validatedata_marketing_20170304_1155am

Workarounds

Outline

There are a couple of workarounds

  1. Skip puritch check
  2. Change column datatype from real to float, currency if it is a currency field

Skip Purity Check

Ola Hallengren

Original


sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d AdminDB -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES', @LogToTable = 'Y' " -b

Revision


sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d AdminDB -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES', @LogToTable = 'Y', @PhysicalOnly = 'Y'  " -b

Explanation

  1. Add @PhysicalOnly=Y

Connect

Opened up a Connect Item.

  1. DBCC CHECKDB/show Purity cites data type of float as incorrect even while correct – by Daniel Adeniji
    Item Number: – 3126630
    Link :- Link
    Type :- Bug
    Status :- Active
    Date Opened :- 3/5/2017 2:32:29 PM

GitHub

Uploaded files to GitHub.
Link is here

 

Summary

It seems that on this particular version of SQL Server, SQL sometimes has problems running purity checks against certain records.

The failing column are defined as having the float datatype.

BTW, the version# is

SQL Server Version :- Microsoft SQL Server 2008 R2 (SP3) – 10.50.6000.34 (X64)
Aug 19 2014 12:21:34
Copyright (c) Microsoft Corporation
Enterprise
Product Level :- SP3
Product Version :- 10.50.6000.34
Edition :- Enterprise Edition (64-bit)

References

  1. DBCC CheckDB
    • Developer Network
    • Support.microsoft.com
      • Troubleshooting DBCC error 2570 in SQL Server 2005 and later versions
        Link
    • Sergessqlnotes’s Blog
      • DBCC CHECKDB Msg 2570 Data Purity Errors
        Link
  2. DataType
    • Developer Network
      • Transact-SQL Reference (Database Engine) Data Types (Transact-SQL)  Numeric Types
        • float and real (Transact-SQL)
          Link
    • Tech Republic
      • 10+ common questions about SQL Server data types
        Link
  3. Microsoft Developer
    • SQL with Manoj
      • Use new TRY_PARSE() instead of ISNUMERIC() | SQL Server 2012
        Link

 

Ola Hallengren – DatabaseBackup – Missing Indexes

Background

I am really high on Ola Hallengren’s Database Utility Scripts and I have wanted to get this down for a while now.

 

Performance

 

Missing Indexes

Missing Indexes can be a drag on system performance.

Unfortunately, there are a couple of missing indexes on the msdb.dbo.backupset table.

 

Code

Programmable

dbo.DatabaseBackup

Code Snippet

 


	DECLARE @CurrentDifferentialBaseLSN numeric(25,0)
	DECLARE @CurrentDatabaseName		sysname
	DECLARE @CurrentDatabaseID int

	DECLARE @Version numeric(18,10)
	DECLARE @AmazonRDS bit

	DECLARE @CurrentDifferentialBaseIsSnapshot bit

	SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.'
                          + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))
                          - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))

	SET @AmazonRDS = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END

	SET @CurrentDatabaseName = db_name()
	SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)



	SELECT @CurrentDifferentialBaseLSN = differential_base_lsn
    FROM sys.master_files
    WHERE database_id = @CurrentDatabaseID
    AND [type] = 0
    AND [file_id] = 1

    -- Workaround for a bug in SQL Server 2005
    IF @Version >= 9 AND @Version < 10
    AND EXISTS
    (
           SELECT * FROM sys.master_files 
           WHERE database_id = @CurrentDatabaseID 
           AND [type] = 0 AND [file_id] = 1 
           AND differential_base_lsn IS NOT NULL 
           AND differential_base_guid IS NOT NULL 
           AND differential_base_time IS NULL
    )
    BEGIN
    
          SET @CurrentDifferentialBaseLSN = NULL


    END

    SELECT @CurrentDifferentialBaseIsSnapshot = is_snapshot
    FROM   msdb.dbo.backupset
    WHERE  database_name = @CurrentDatabaseName
    AND    [type] = 'D'
    AND    checkpoint_lsn = @CurrentDifferentialBaseLSN

 

Missing Index

checkpoint_lsn_20161203_0857pm

 

Index Creation Statement

USE [msdb]
GO

CREATE NONCLUSTERED INDEX [INDX_CheckpointLSN_Type_DatabaseName]
ON [dbo].[backupset] 
(
	  [checkpoint_lsn]
	, [type]
	, [database_name]
)

GO


 

 

Code Snippet – INDX_DBA_DatabaseName_IsDamaged
Query Plan

queryplan_20170216_1226pm

Script
/*
Missing Index Details from sql01.master )
The Query Processor estimates that implementing the following index could improve the query cost by 92.1703%.
*/

/*
USE [msdb]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[backupset] ([database_name],[is_damaged])
INCLUDE ([database_backup_lsn],[backup_finish_date],[type])
GO
*/

use [msdb]
go

if object_id('[dbo].[backupset]') is not null
begin

	print 'Reviewing [dbo].[backupset] .... '

	/*
		Query:

			declare @CurrentDatabaseName    sysname
			declare @CurrentLatestBackup	datetime
			declare @CurrentDifferentialBaseLSN numeric(20, 1)

			set @CurrentDatabaseName = 'msdb'
			set @CurrentDifferentialBaseLSN = 1

			BEGIN        

				SELECT @CurrentLatestBackup = MAX(backup_finish_date)        
				FROM   msdb.dbo.backupset        
				WHERE (
							   [type] IN('D','I')        
							OR database_backup_lsn < @CurrentDifferentialBaseLSN
					  )        
				AND is_damaged = 0        
				AND database_name = @CurrentDatabaseName

			END

	*/

	/*

		CREATE NONCLUSTERED INDEX [INDX_DBA_DatabaseName_IsDamaged]
		ON [dbo].[backupset] ([database_name],[is_damaged])
		INCLUDE ([database_backup_lsn],[backup_finish_date],[type])

	*/

	if not exists
	(
		select *
		from   sys.indexes tblSI
		where  tblSI.object_id = object_id('[dbo].[backupset]')
		and    tblSI.[name] = 'INDX_DBA_DatabaseName_IsDamaged'
	)
	begin

		print 'Creating Index INDX_DBA_DatabaseName_IsDamaged ...'

		CREATE NONCLUSTERED INDEX [INDX_DBA_DatabaseName_IsDamaged]
		ON [dbo].[backupset] 
		(
			  [database_name]
			, [is_damaged]
		)
		INCLUDE 
		(
			   [database_backup_lsn]
			 , [backup_finish_date]
			 , [type]
		)
		with
		(
			FILLFACTOR=80
		)
		print 'Created Index INDX_DBA_DatabaseName_IsDamaged'

	end

	print 'Reviewed [dbo].[backupset]'

end

go


Ola Hallengren Scripts – Uninstall

Background

Ola Hallengren’s scripts has everything.

But, unfortunately I just installed it on the master DB.

But, our policy is to install it against a user database (AdminDB, UtilDB) or whatever you call it.

Here is a Stored Procedure that will uninstall it and allow you to go ahead it and start a new install.

Code

Stored Procedure

[dbo].[sp_OlaHallengrenScriptsUninstall]




use [master]
go

if object_id('[dbo].[sp_OlaHallengrenScriptsUninstall]') is null
begin

	exec('create procedure [dbo].[sp_OlaHallengrenScriptsUninstall] as ')

end
go


alter procedure [dbo].[sp_OlaHallengrenScriptsUninstall] 
(
	  @scriptOnly bit = 1
	, @categoryID int = 3
)
as

begin

	set nocount on;

	declare @tblJob TABLE
	(	
		  [id]				int not null identity(1,1)
		, [jobID]			uniqueIdentifier
		, [name]			sysname
		, [description]		varchar(600)
	)


	declare @tblObject TABLE
	(	
		  [id]			int not null identity(1,1)

		, [schema]		sysname

		, [name]		sysname

		, [objectName]
			as quoteName([schema]) + '.' + quoteName([name])

		, [type]		sysname

		, [type_desc]	sysname

		, [dropTarget] as
			case [type]
					when 'U' then 'TABLE'
					when 'P' then 'PROCEDURE'
			end
	)


	declare @tblObjectSought TABLE
	(
		  [id]     int not null identity(1,1)
		, [schema] sysname
		, [name]   sysname
	)

	declare @jobDescription sysname
	declare @idJob			int
	declare @idJobMax		int
	declare @jobName		sysname


	declare @SQL_FORMAT_DELETE_JOB		nvarchar(600)
	declare @SQL_FORMAT_DROP_OBJECT		nvarchar(600)

	declare @idObject			int
	declare @idObjectMax		int
	declare @objectSchema		sysname
	declare @objectName			sysname
	declare @objectType			sysname
	declare @objectTypeLiteral	sysname
	declare @dropTarget			varchar(60)

	declare @sql			nvarchar(4000)

	declare @CHAR_TAB	  char(1)
	declare @CHAR_NEWLINE char(2)

	set @CHAR_TAB = char(9)
	set @CHAR_NEWLINE = char(13)+ char(10)

	set @SQL_FORMAT_DELETE_JOB = 'if exists ( select * from [msdb].[dbo].[sysjobs] where [name] = ''#JOB_NAME#'' ) '
									+ ' begin '
									+ '		EXEC [msdb].[dbo].[sp_delete_job]  @job_name = ''#JOB_NAME#'' '
									+ ' end '

	set @SQL_FORMAT_DROP_OBJECT = 'if object_id(''[#OBJECT_NAME#]'') is not null begin DROP [#TYPE_DESC] [#OBJECT_NAME#] end'

	set @jobDescription = 'Source: https://ola.hallengren.com'


	/*
		Prepare a list of Ola's Objects
	*/
	insert into @tblObjectSought
	(
		  [schema]
		, [name]  
	)
	select 'dbo', 'CommandLog'
	union
	select 'dbo', 'CommandExecute'
	union
	select 'dbo', 'DatabaseBackup'
	union
	select 'dbo', 'DatabaseIntegrityCheck'
	union
	select 'dbo', 'IndexOptimize'

	/*
		Deduce Ola's DB Job
			a) Based on Job's Description 
	*/
	insert into @tblJob
	(	
		  [jobID] 
		, [name]	
		, [description]
	)
	select 
			  tblSJ.[job_id]
			, tblSJ.[name]
			, tblSJ.[description]
	from   [msdb].[dbo].[sysjobs] tblSJ

	where  (
				( tblSJ.[description] = @jobDescription )
			)

	and		(
				( tblSJ.originating_server_id = 0 )
			)

	set @idJob =1
	set @idJobMax = ( select max([id]) from @tblJob )

	print '--Jobs'
	print replicate('-', 4)

	/*
		Ola's DB Job
			a) Script Job Deletion
	*/
	while (@idJob <= @idJobMax)
	begin

		select 
				@jobName = [name]
		from    @tblJob 
		where   [id] = @idJob	

		set @sql = replace(@SQL_FORMAT_DELETE_JOB, '#JOB_NAME#', @jobName)

		print @sql

		if (@scriptOnly = 0)
		begin

			--print @CHAR_TAB + 'Executing ' + @sql

			exec(@sql)

			print @CHAR_NEWLINE

		end

		set @idJob = @idJob + 1

	end

	/*
		Ola's DB Job
			a) Get List of Objects
	*/
	insert into @tblObject
	(	
		  [schema]	
		, [name]	
		, [type]	
		, [type_desc]
	)

	select 
			  tblSS.[name]
			, tblSO.[name]
			, tblSO.[type]
			, tblSO.[type_desc]

	from   [sys].[objects] tblSO

	inner join [sys].[schemas] tblSS

			on tblSO.[schema_id] = tblSS.[schema_id]

	inner join @tblObjectSought tblOS

			on   tblSS.[name] = tblOS.[schema]
			and  tblSO.[name] = tblOS.[name]

	set @idObject =1
	set @idObjectMax = ( select max([id]) from @tblObject )

	/*
		Ola's DB Job
			a) Script Object Deletion
	*/
	print '--Objects'
	print replicate('-', 9)

	while (@idObject <= @idObjectMax)
	begin

		select 
				  @objectName = [objectName]
				, @objectTypeLiteral = [type_desc]
				, @dropTarget = [dropTarget]

		from    @tblObject
		where   [id] = @idObject

		set @sql = @SQL_FORMAT_DROP_OBJECT

		set @sql = replace(@sql, '[#TYPE_DESC]', @dropTarget)

		set @sql = replace(@sql, '[#OBJECT_NAME#]', @objectName)

		print @sql

		if (@scriptOnly = 0)
		begin

			exec(@sql)

			print @CHAR_NEWLINE

		end

		set @idObject = @idObject + 1

	end


end
go

exec sys.sp_MS_marksystemobject '[dbo].[sp_OlaHallengrenScriptsUninstall]'
go


Lab

Invoke



declare @scriptOnly bit
declare @categoryID int

set @scriptOnly = 0
set @categoryID = 3

begin tran

   exec [dbo].[sp_OlaHallengrenScriptsUninstall] 
	  @scriptOnly = @scriptOnly
	, @categoryID = @categoryID

commit tran


Ola Hallengren – Database Maintenance Scripts – Transaction Log Backup not occuring

Background

Made the change discussed here.

Again, the change was to temporary alter our Recovery Mode to SIMPLE and back. In between, shrunk the transaction log files.

 

Issue

Ran the Ola Hallengren’s transaction Log backup step, but nothing is being backed up.

BTW, the Scripts are available here.

 

Trouble Shooting

Let us review the scripts and see where we are failing.

Stored Procedure – dbo.DatabaseBackup

sys.database_recovery_status – Get last_log_backup_lsn

Check sys.database_recovery_status and get last_log_backup_lsn


    IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
    BEGIN
      SELECT @CurrentLogLSN = last_log_backup_lsn
      FROM sys.database_recovery_status
      WHERE database_id = @CurrentDatabaseID
    END

Backup Conditions

skipwhenlogbackupandrecoveryissimpleorcurrentloglsn

Explanation

  1. Skip when LogBackup
    • Recovery Is Simple
    • Or CurrentLogLSN is null

 

sys.database_recovery_status

Script


SELECT 
		  tblSDRS.[database_id] 
		, [databaseName] = db_name(tblSDRS.[database_id])
		, tblSDRS.last_log_backup_lsn

FROM sys.database_recovery_status tblSDRS


Output

sys-database_recovery_status

 

Database Backup – Directly

If we try to backup the database directly by issuing our own “backup log <database-name>” statement. i.e.

Code


BACKUP LOG [eiadev] 
TO  DISK = N'Z:\Backups\eiadev_20161027_1208PM.bak' WITH NOFORMAT, NOINIT
,  NAME = N'enr_Log Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Output

GUI

nocurrentbackup

Textual

Msg 4214, Level 16, State 1, Line 7
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 7
BACKUP LOG is terminating abnormally.

Remediation

Take full backups right away or wait till our next scheduled full backup.

Once that occurs, our transaction backups will run to successful completion.

Summary

So basically because we changed the Recovery Mode from FULL to SIMPLE ( and now back to FULL), we can not issue a transaction log backup statement until we take a FULL Backup.

To avoid that error, Olla’s script proactively checks the sys.database_recovery_status dmv.