SQL Server – DBCC Info

Background

A few posts back we spoke about “SQL Server – Mapping DBCC Commands to Dynamic Management Views ( DMV )“.

That post is here.

 

DBCC DBINFO

Introduction

DBCC DBINFO offers concise metadata at the database level.

 

SYNTAX

Its syntax is elementary and here it is “DBCC dbinfo with tableresults“.

 

Sample

ROWS

Display result as rows

Query


dbcc dbinfo with tableresults, no_infomsgs

Output

 

COLUMNS

Query



set nocount on
go

set XACT_ABORT on
go


declare @tblDBInfo TABLE
(
      [parentObject] sysname not null

    , [object]		 sysname not null

    , [field]		 sysname not null

    , [sequenceNbr]  smallint 
                     identity(1,1) not null

    , [value]        nvarchar(4000) not null

    , primary key
        (
              [parentObject] 
            , [object]		 
            , [field]		 
            , [sequenceNbr]
        )
)

declare @servername sysname
declare @dbid		int
declare @dbname		sysname

set @servername = cast(
                        serverproperty('servername')
                        as sysname
                      )

set @dbid = db_id()

set @dbname = db_name()

insert into @tblDBInfo
(
      [parentObject]
    , [object]		
    , [field]		
    , [value]       
)
exec ('dbcc dbinfo with tableresults, no_infomsgs')

select *

from   @tblDBInfo

order by 
        [field] asc

; with cteExclusion
(
      [dbid]
    , [Field] 
)
as
(
    select 
           2 as [dbid]
        , 'dbi_dbccLastKnownGood' as [Field]

)
, cteDBInfo
(
      [servername]
    , [dbid]
    , [Field]
    , [VALUE]
)
as
(

    select 
              @servername
            , @dbid
            , tblDBI.Field
            , tblDBI.[VALUE]

    from   @tblDBInfo tblDBI

    where  tblDBI.Field in
            (
                  'dbi_dbccLastKnownGood'
                , 'dbi_version'
                , 'dbi_createVersion'
                , 'dbi_LastLogBackupTime'
                , 'dbi_crdate'
                , 'dbi_dbname'
                , 'dbi_dbid'
                , 'dbi_cmptlevel'
            )


    and not exists
        (
            select 1
            from   cteExclusion cteEX
            where  cteEX.[dbid] = @dbid
            and    cteEX.Field = tblDBI.[Field]   
        )


)

, ctePivot
as
(
    select 
              cteDBI.[serverName]
            , cteDBI.[dbid]
            , cteDBI.[dbi_dbccLastKnownGood]
            , [dbi_version]
            , [dbi_createVersion]
            , [dbi_LastLogBackupTime]
            , [dbi_crdate]
            , [dbi_dbname]
            , [dbi_dbid]
            , [dbi_cmptlevel]	

    from   cteDBInfo cteDBI

    PIVOT
        (
            MAX(VALUE)
    
            FOR [Field] in
            (
                  [dbi_dbccLastKnownGood]
                , [dbi_version]
                , [dbi_createVersion]
                , [dbi_LastLogBackupTime]
                , [dbi_crdate]
                , [dbi_dbname]
                , [dbi_dbid]
                , [dbi_cmptlevel]
            )

        ) cteDBI

)

select 
          cteDBI.[servername]
        , cteDBI.[dbid]
        , cteDBI.[dbi_dbccLastKnownGood]
        , [dbi_version]
        , [dbi_createVersion]
        , [dbi_LastLogBackupTime]
        , [dbi_crdate]
        , [dbi_dbname]
        , [dbi_dbid]
        , [dbi_cmptlevel]

from   ctePivot cteDBI


Output

Summary

DBCC DBINFO has a wealth of information.

Nuggets include:

  1. Database Attributes
    • Compatibility level
    • Collation
    • Containment
    • Create Date
    • Database ID ( dbid )
    • Database name ( dbname )
    • MirrorId ( Mirrored database )
    • familyGUID ( Restored DB & Snapshot )
    • Safety ( Mirrored database )
    • Database Version
    • Database Create Version
  2. Operation Tracking
    • Last time dbcc checkdb was ran
    • Last time Transaction Log backup was taken

SQL Server – Mapping “DBCC Commands” to Dynamic Management Views ( DMV )

Background

Wanted to map DBCC Commands to their corresponding Dynamic Management Views ( DMV ).

Matrix

DBCC Command Dynamic Management Views Usage SQL Version Introduced
DBCC LOGINFO
sys.dm_db_log_info Virtual Log Files v2016/SP2
v2017/RTM
dbcc sqlperf(logspace)
sys.dm_db_log_space_usage Database Transaction Log  Usage v2012
DBCC Memory Status
 sys.dm_os_memory_clerks Types of objects stored in memory  v2008/R2
 sys.dm_os_buffer_descriptors Actual identifiers for the objects stored in the Buffer Pool  v2008/R2
DBCC SHOW CONTIG
 sys.dm_db_index_physical_stats Fragmentation  v2008
DBCC SHOW _STATISTICS
 sys.dm_db_stats_properties Statistics  v2008
sys.dm_db_incremental_stats_properties Incremental Statistics SQL Server 2014 (12.x) Service Pack 2

SQL Server 2016 (13.x) Service Pack 1.

sys.dm_db_stats_histogram Statistics Histogram  v2016

Use

sys.dm_db_log_info

Sample



select  

          [database]
            = tblSD.[name]

        , [file]
            = tblSMF.[name]

        , vLI.[file_id]

        , [seq]
            = RANK() OVER   
                (
                    PARTITION BY 
                          tblSMF.database_id
                        , vLI.[file_id]

                    ORDER BY 
                        vLI.vlf_begin_offset asc
                ) 

        , vLI.vlf_begin_offset

        , vLI.vlf_size_mb

        , vLI.vlf_sequence_number

        , vLI.vlf_active

        , [active]
            = case vLI.vlf_active
                when 0 then 'VLF is not in use'
                when 1 then 'VLF is active'
              end		

        , vLI.vlf_status

        , [status]
            = case vLI.vlf_status
                when 0 then 'VLF is inactive'
                when 1 then 'VLF is initialized but unused'
                when 2 then 'VLF is active'
              end		

        , vLI.vlf_parity

        --, vLI.vlf_first_lsn

        --, vLI.vlf_create_lsn

from   sys.databases tblSD

inner join sys.master_files tblSMF

    on tblSD.[database_id] = tblSMF.database_id

cross apply sys.dm_db_log_info
            (
                tblSMF.database_id
            ) vLI

where tblSMF.[database_id] = vLI.database_id

and   tblSMF.[file_id] = vLI.[file_id]

/*
    Database is online
*/


and  databasepropertyex
            ( 
                  tblSD.[name]
                , 'Collation'
            ) is not null

order by

      tblSD.[name]

    , tblSMF.[name]

    , vLI.vlf_begin_offset



Output

sys.dm_db_log_space_usage

Sample


set nocount on
go

set XACT_ABORT on
go

declare @tblLogSpace TABLE
(
	    
      [dbname]			  sysname

	, [logSizeTotalInMB]  decimal(10,2)

	, [logSizePercentile] decimal(10,2)

	, [logSizeInUseInMB]  
			as 
				(
					cast
					(
						(
							[logSizeTotalInMB] * 1.00
							*
							(
								[logSizePercentile]
								/
								100.000
							)

						)

						as decimal(10, 2)					  	


					)
				)

	, [status]	int

)

insert into @tblLogSpace
(
	  [dbname] 
	, [logSizeTotalInMB] 
	, [logSizePercentile]
	, [status]			 
)
exec('dbcc sqlperf(logspace)')

select *
from   @tblLogSpace tblLS
where  tblLS.dbname = db_name()

select 

	  tblDMLSU.database_id

	, [database]
		= db_name(tblDMLSU.database_id)

	, [dbid] 
		= db_id()

	, tblDMLSU.total_log_size_in_bytes

	, [totalLogSizeInMB]
		= 
		cast
		(

			(
				( tblDMLSU.total_log_size_in_bytes * 1.00 )
				/ 
				( 1000 * 1000 )
			)
			
			as decimal(20, 2)
		)
			  				
	, tblDMLSU.[used_log_space_in_bytes]

	, [usedLogSizeInMB]
		= cast
			(	
					
				(
				tblDMLSU.[used_log_space_in_bytes] * 1.00
				)
				/ 
				( 1000 * 1000 )

				as decimal(20, 2)
			)			

	, tblDMLSU.used_log_space_in_percent

	, tblDMLSU.[log_space_in_bytes_since_last_backup]

	, [usedLogBytesInMBSinceLastBackup]
	  = cast
		(
			(
				( tblDMLSU.log_space_in_bytes_since_last_backup * 1.0 )
				/ 
				( 1000 * 1000 )
			)
			as decimal(20, 2)
		)

from [sys].[dm_db_log_space_usage] tblDMLSU

sys.dm_os_buffer_descriptors

Sample


select 
     [database] 
		= case
			when [database_id] = 32767
				then 'Resource DB'
			else db_name(dmOSBD.[database_id])
			end

	, [bufferedPageCount]
		= count(*)

	, [bufferPoolMB]
		= count_big(*) * 8192 
			/ 
			(1024 * 1024)

from sys.dm_os_buffer_descriptors dmOSBD

GROUP BY 
	      DB_NAME(database_id) 
		, database_id  

ORDER BY 
		count_big(*) DESC

;  


 

sys.dm_os_memory_clerks

Sample


select 
           dmOSMC.[type]

        , [pagesKB]
            = sum(dmOSMC.pages_kb)

        , [pagesMB]
            = sum(dmOSMC.pages_kb)
                / 1000

        , [pagesGB]
            = cast
                (
                    (
                        sum(dmOSMC.pages_kb) * 1.00
                        / ( 1000 * 1000)
                    )
                    as decimal(10, 2)
                )

 from   sys.dm_os_memory_clerks dmOSMC

 group by
        dmOSMC.[type]

 order by
        sum(dmOSMC.pages_kb) desc


;  


Output

sys.dm_db_index_physical_stats

Sample


declare @dbname sysname
declare @dbid   int

declare @objectID int
declare @indexID  int
declare @partitionNumber int
declare @mode            sysname

select 
      [database]
        = db_name(tblIPS.database_id)

    , [object]
        = quoteName
            (
                object_schema_name
                (
                      tblIPS.object_id
                    , tblIPS.database_id	
                )
            )

            + '.'

            + object_name
                (
                      tblIPS.object_id
                    , tblIPS.database_id	
                )

    , tblIPS.[object_id]

    , tblIPS.index_id

    , tblIPS.index_type_desc

    , tblIPS.partition_number

    , tblIPS.avg_fragmentation_in_percent

from sys.dm_db_index_physical_stats 
(
      @dbid
    , @objectID
    , @indexID
    , @partitionNumber
    , @mode

) tblIPS

order by
    tblIPS.avg_fragmentation_in_percent desc

sys.dm_db_stats_properties

Sample


select 

      [database]
        = db_name()

    , [object]
        = quoteName
            (
                object_schema_name
                (
                      tblSO.object_id
                )
            )

          + '.'

          + quotename
            (
                object_name
                (
                    tblSO.object_id
                )
            )

    , [stat]
        = tblSS.[name]

    , [userCreated]
        = case tblSS.user_created
                when 1 then 'Yes'
                else 'No'
          end

    , tblDMSP.[rows]
    
    , tblDMSP.[rows_sampled]

    , [%sampled]	
        = (
            cast
            (
                (
                    tblDMSP.[rows_sampled] * 100.00
                )
                / 
                NULLIF
                (
                      tblDMSP.[rows]
                    , 0
                )

            as decimal(6,2)
          
          )

        )

    , tblDMSP.[steps]
    , tblDMSP.[last_updated]

from  sys.objects tblSO

inner join sys.stats tblSS

    on tblSO.object_id = tblSS.object_id

cross apply sys.dm_db_stats_properties 
(
      tblSS.[object_id]
    , tblSS.[stats_id]

) tblDMSP

where  tblSO.[type] = 'U'

and    tblSO.is_ms_shipped = 0

order by
        tblDMSP.[rows] desc
    , 	tblDMSP.[rows_sampled] desc

Output

sys.dm_db_stats_histogram

Sample



declare @object   sysname
declare @stat     sysname
declare @objectID int

set @object = '[stats].[skewedData]'
set @stat = 'indx_id_measure'

set @objectID = OBJECT_ID(@object)

select 

      [database]
        = db_name()

    , [object]
        = quoteName
            (
                object_schema_name
                (
                      tblSO.object_id
                )
            )

            + '.'

            + quotename
                (
                    object_name
                    (
                      tblSO.object_id
                    )
                )

    , [stat]
        = tblSS.[name]

    , [userCreated]
        = case tblSS.user_created
                when 1 then 'Yes'
                else 'No'
          end

   , tblDMSH.[stats_id] 
   
   , tblDMSH.[step_number]

   , tblDMSH.range_high_key
   
   , tblDMSH.range_rows
   
   , tblDMSH.equal_rows

from  sys.objects tblSO

inner join sys.stats tblSS

    on tblSO.object_id = tblSS.object_id

cross apply sys.dm_db_stats_histogram
(
      tblSS.[object_id]
    , tblSS.[stats_id]

) tblDMSH

where  tblSO.[type] = 'U'

and    tblSO.[is_ms_shipped] = 0

and    tblSS.[object_id] = @objectID

and    tblSS.[name] = @stat

order by
          [object]
        , tblSS.[name]
        , tblDMSH.[stats_id] asc
        , tblDMSH.[step_number] asc

Output

References

  1. Microsoft
    • Docs > SQL > Relational databases > System dynamic management views
      • sys.dm_db_log_info (Transact-SQL)
        • sys.dm_db_log_info
          Link
      • sys.dm_db_log_space_usage
        • sys.dm_db_log_space_usage
          Link
      • sys.dm_os_memory_clerks
        • sys.dm_os_memory_clerks
          Link
      • sys.dm_db_index_physical_stats (Transact-SQL)
        • sys.dm_db_index_physical_stats (Transact-SQL)
          Link
      • sys.dm_db_stats_properties
        • sys.dm_db_stats_properties
          Link
      • sys.dm_db_stats_histogram ( Transact SQL )
        • sys.dm_db_stats_histogram
          Link

DBCC MemoryStatus

Background

Wanted to place another stone in our pond of water where we review Memory Usage in MS SQL Server Instance.

DBCC MemoryStatus

Sample Output

Text File

Sample content when we run “dbcc memusage” and capture the output into a text file.

Notepad++

Here is what things look like in Notepad++.

 

Others Work

Wanted to see how others have consumed “dbcc memusage“.

Slava Murygin

Took to the Internet and found a nice post by Slava Murygin.

It is here.

 

Revision

Here is a revised version…



SET NOCOUNT ON
GO


declare @tblMemoryStatusDump TABLE
(
      [id] INT IDENTITY(1,1) PRIMARY KEY
    , [dump] VARCHAR(600)

);


declare @tblMemoryStatus TABLE
(
     id			INT
   , [measure]  VARCHAR(200) 
   , [counter]  VARCHAR(300) 
   , [value]	VARCHAR(200)
   , [rowType]  char(1)
   , [dump]		varchar(600) 

);

declare @serverName  sysname
declare @queryFormat nvarchar(600)
declare @query       nvarchar(600)

declare @tblMemoryStatusHeader TABLE
(
    [seqNumber] smallint not null
        identity(1,1)

    , [id]   int not null
    
    , [dump] nvarchar(600) not null

    , [rangeStart] int null
    , [rangeEnd]   int null

)

set @queryFormat = 'xp_cmdshell ''sqlcmd -E -S %s -Q "DBCC MEMORYSTATUS" ''';

/*

    sys.xp_cmdshell
    Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 19]
    SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' 
    because this component is turned off as part of the security configuration for this server. 
    A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. 
    For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

*/

/*

    exec sp_configure 'show advanced options',1
    reconfigure with override;

    exec sp_configure 'xp_cmdshell',1
    go

    reconfigure with override;

*/


set @serverName = cast (serverproperty('servername') as sysname)
exec master..xp_sprintf
          @query output
        , @queryFormat
        , @serverName

print @query

INSERT INTO @tblMemoryStatusDump
(
    [dump]
)
exec(@query)


insert into @tblMemoryStatus
(
       [id]
     , [dump]
     , [counter] 
     , [value] 

)
select 
          tblMSD.[id]

        , tblMSD.[dump]
        
        , [counter]
            = substring
                (
                    tblMSD.[dump]
                    , 1
                    , 42
                )

        , [value]
            = ltrim(rtrim
              (
                substring
                    (
                        tblMSD.[dump]
                        , 43
                        , 20
                    )
              ))

from   @tblMemoryStatusDump tblMSD

update tblMSD

set	
            
       [rowType] 
            = case
                when tblMSDNext.[dump] like '---%' then 't' 
                when tblMSD.[dump] like '---%' then 'd' 
                when tblMSD.[dump] like '(%rows affected)' then 'c' 
                when tblMSD.[dump] like 'DBCC execution completed%' then 'i' 
                when tblMSD.[dump] is null then '0'
                else 'v'
              end
                 
from   @tblMemoryStatus tblMSD

inner join @tblMemoryStatus tblMSDNext

        on tblMSD.[id] + 1  = tblMSDNext.[id]


insert into @tblMemoryStatusHeader
(
      [id] 
    , [dump]
)
select 
      tblMSD.[id]
    , [dump]
        = substring
                (
                    tblMSD.[dump]
                    , 1
                    , 41
                )		

from   @tblMemoryStatus tblMSD

where  tblMSD.[rowType] = 't'

/*
    Get Range
*/
update tblMSH

set	
          [rangeStart] = tblMSH.[id]

        , [rangeEnd] = tblMSHNext.[id]

from   @tblMemoryStatusHeader tblMSH

inner join @tblMemoryStatusHeader tblMSHNext

        on tblMSHNext.[seqNumber] = tblMSH.seqNumber + 1

/*
    Get Range for last record
*/
update tblMSH

set		[rangeEnd] = ( select max([id]) from @tblMemoryStatus tblMSD )

from   @tblMemoryStatusHeader tblMSH

where   rangeEnd is null

/*
    Based on Range, set measure column
*/
update tblMSD

set			
       [measure] = tblMSH.[dump]
                 
from   @tblMemoryStatus tblMSD

inner join @tblMemoryStatusHeader tblMSH

        on tblMSD.[id] between tblMSH.rangeStart and tblMSH.rangeEnd

where tblMSD.[rowType] = 'v'


/*
    remove extra record
*/
delete tblMS
 
from  @tblMemoryStatus tblMS

where tblMS.[rowType] in 
        ( 
              'd'
            , 'c'
            , '0'
            , 'i'
        )

select  
        tblMS.*

from   @tblMemoryStatus tblMS

where  tblMS.rowType in ( 'v')


 

Dedicated

Dedicated to Slava Murygin.

SQL Server – Database Restore Steps thru lenses of errorlog

Introduction

Wanted to document database restore steps as seen from trail left in SQL Server Instance’s errorlog file.

Restore

Restore – Full Database

Step Sub Task Logged Explanation

Start Database (Database started in boot-strap mode )
Starting up database ‘csAnalytics’.

Recovery or NO Recovery
NO Recovery
The database ‘csAnalytics’ is marked RESTORING and is in a state that does not allow recovery to be run. Indicate that we are restoring and will not proceed to actual database recovery; as we would have done during normal SQL Server Instance start.
Recovery
Recovery is writing a checkpoint in database ‘csAnalytics’ (9). This is an informational message only. No user action is required. Database restore wrote a checkpoint ledger to indicate progress.
2018-03-20 05:46:51.110    spid70    Recovery completed for database rbpivr1 (database ID 42) in 12 second(s) (analysis 5444 ms, redo 20 ms, undo 5171 ms.) This is an informational message only. No user action is required. Informational in terms of duration.

Here is what is logged – Database name, Assigned Database ID, and total time.

Time breakdown – Analysis, redo, undo.

2018-03-20 05:46:51.550    spid70    Filegroup fgLOB in database rbpivr1 is unavailable because it is Offline. Restore or alter the filegroup to be available. In the case of partial restores, the file groups implicitly excluded.
2018-03-20 11:10:58.790    Backup    Database was restored: Database: DBMirror, creation date(time): 2018/03/05(14:52:06), first LSN: 35:341:37, last LSN: 35:360:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Microsoft\SQLServer\Backup\DBMirror.bak’}). Informational message. No user action required. Informational in terms of statistics.

Data logged includes name given to target database, creation time, LSN, number of dump/backup devices, and backup device names.


In-Memory Database ( Hekathon )
[INFO] HkCheckpointCtxtImpl::StartOfflineCkpt(): Database ID: [42]. Starting offline checkpoint worker thread on a hidden SOS scheduler. Hekathon Offline Checkpoint
Check Database
CHECKDB for database ‘csAnalytics’ finished without errors on 2011-08-03 00:06:43.420 (local time). This is an informational message only; no user action is required. Minimal Database Consistency ran.
Resource DB Alignment
2018-03-20 05:46:51.950    spid70    Synchronize Database ‘rbpivr1’ (42) with Resource Database. Database system objects aligned with Instance’s Resource DB.

Especially important when database backup file restored from earlier SQL Server version.


Restore Complete
Restore is complete on database ‘rbpivr1’.  The database is now available. Restore Completed

Database Setting
Setting database option MULTI_USER to ON for database csAnalytics. Database transitioned from Single to Multi User

 

Restore – Transaction Log

Logged Explanation
Log was restored. Database: DBMirror, creation date(time): 2018/03/05(14:52:06), first LSN: 35:341:37, last LSN: 35:422:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Microsoft\SQLServer\Backup\DBMirror.trn’}). This is an informational message. No user action is required. Conveys that Transaction Log backup file was applied to specified database.  It lets us in on the starting and ending LSN.  And, the name of the transaction backup file.

Summary

Data logged in the SQL Server Instance log is quite useful in gleaning what is occurring behind the scene when a database is restored.

Comparing logs from different SQL Server Instances and Database setup affords familiarity with how various database setup and restore options is considered and acted upon by the DB restore facility.

As summary, the code will consider:

  1. Will the database be recovered or left in no_recovery
    • No recovery basically means subsequent transaction logs will be applied
  2. Fast Database Consistency check occurs
  3. Redo and Undo Log Processing
    • Logs contain both redo and undo portions
      • Redo
        • Once database is fully restored, committed transactions that are still in the transaction log, but have yet to be checkpoint into the database data files are applied to restored database datafiles
      • Undo
        • Undo means that data is not committed and still in flight.
        • The data can later be committed ( commit tran ) or discarded ( rollback )
  4. Resource DB Alignment
    • When a database from a previous version of SQL Server is restored unto a newer version the restored db system dictionary has to be brought up to date
  5. In Memory Database
    • In-Memory tables and programmable objects are read from database files into memory

SQL Server – Linked Server – TroubleShooting

Background

There are a variety of avenues for diagnosing Linked Server related connectivity issues.

 

Follow Up

As a quick follow up to earlier conversations on SQL Server and heterogeneous data sources:

  1. SQL Server / Linked Server – Oracle ODAC
    Link

 

Outline

Here are some of the tools for diagnosis heterogenous data related issues:

  1. Query
    • DBCC
      • Trace Flag 7300
  2. SQL Server Profiler
  3. Event Viewer
  4. SysInternals
    • Process Explorer

 

 

Query

DBCC

Trace Flag 7300

Query
Syntax

dbcc traceon(7300) 
	with no_infomsgs
	;
go

dbcc traceoff(7300)
	with no_infomsgs
	;
go


Sample

dbcc traceon(7300) 
	with no_infomsgs
	;
go

declare @linkedServer sysname

set @linkedServer = 'HRDB';

exec sp_tables_ex
		@table_server = @linkedServer

go

dbcc traceoff(7300)
	with no_infomsgs
	;
go


Sample – Grid
Sample – Textual

OLE DB provider "OraOLEDB.Oracle" for linked server "PRD" returned message "ORA-12170: TNS:Connect timeout occurred".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41 [Batch Start Line 4]
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "PRD".


Explanation
  1. Error Message
    • OLE DB provider “OraOLEDB.Oracle” for linked server “PRD” returned message
      • “ORA-12170: TNS:Connect timeout occurred”.
    • Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “PRD”.
  2. Using DBCC TraceOn (7300)
    • Allows us to see that we are experiencing connection timeout to the Oracle Server
    • Likely means network or TNS configuation error

 

 

SQL Server Profiler

Trace Properties

Trace Event Selections

Here are the events that we will be tracing on…

Event Category Event Event Description
Errors and Warnings
ErrorLog  Indicates error events logged in the SQL Server error log.
EventLog  Indicates events logged in the Windows application event log.
Exception  Indicates that an exception has occurred in SQL Server.
Execution Warnings  Indicates any warnings that occurred during the execution of a SQL Server statement or stored procedure.
User Error Message  Displays the error message as seen by the user in the case or an error or exception. The error message text appears in the TextData field.
OLEDB
OLEDB Call Event Includes event classes produced by the execution of stored procedures.
OLEDB Data Read Event Displays OLE DB IRowset::GetData calls made by SQL Server for fetching row data for distributed queries and remote stored procedures.
OLEDB Errors Indicates that an OLE DB error has occurred.
OLEDB Provider Information Occurs when a distributed query is run and collects information corresponding to the provider connection. This event class contains all the properties collected from the remote provider using various property sets such as DBPROPSET_DATASOURCEINFO, SQLPROPSET_OPTHINTS, DBPROPSET_SQLSERVERDATASOURCEINFO (SQL Server only), DBPROPSET_SQLSERVERDBINIT (SQL Server only) and DBPROPSET_ROWSET and interface IDBInfo.
OLEDB Query Interface Event Displays OLE DB IUnknown::QueryInterface calls made by SQL Server for distributed queries and remote stored procedures.
Security Audit
Audit Login Collects all new connection events since the trace was started, such as when a client requests a connection to a server running an instance of SQL Server.
Audit Logout Collects all new disconnect events since the trace was started, such as when a client issues a disconnect command.
Stored Procedures
RPC:Completed Occurs when a remote procedure call has been completed.
TSQL
SQL:BatchCompleted Occurs when the Transact-SQL statement has completed.
SQL:BatchStarting Occurs when a Transact-SQL batch is starting.

 

 

Traces

Sample Traces

Sample Trace #01
Image – Full Conversation

Image – OLEDB Call Event
Explanation
  1. SQL:BatchStarting
    • Issues sp_tables_ex
    • Query Linked Server asking for a listing of tables
  2. OLEDB QueryInterface Event
    • IID_IDBProperties
      • <ppunk>0x0000003C47F941D8</ppunk>
        • Set ppunk to 0x0000003C47F941D8
  3. OLEDB Call Event
    • Property
      • DBPROP_INIT_TIMEOUT
      • DBPROP_INIT_GENERALTIMEOUT
      • DBPROP_INIT_DATASOURCE
      • DBPROP_AUTH_USERID
      • DBPROP_AUTH_PASSWORD
    • Result
      • hresult
        • 265946
          • ???
  4. OLEDB QueryInterface Event
    • IID_IDBInitialize
      • input
        • IID_IDBInitialize
      • hresult
        • 0
      • ppunk
        • <ppunk>0x0000003C47F941E0</ppunk>
          • returns 0x0000003C47F941E0
    • IID_ISSAsynchStatus
      • input
        • IID_ISSAsynchStatus
      • hresult
        • -2147467262
  5. OLEDB Errors
    • hresult
      • -2147467259
  6. OLEDB Query Interface Event
    • input
      • IID_ISupportErrorInfo
    • hresult
      • 0
    • outputs
      • 0x00000044686EBFC0
  7. OLEDB Call Event
    • input
      • IID_IDBInitialize
    • hresult
      • 0
  8. User Error Message
    • OLE DB provider “OraOLEDB.Oracle” for linked server “PRD” returned message “ORA-12170: TNS:Connect timeout occurred“.
  9. Exception
    • Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “PRD”.
  10. User Error Message
    • Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “PRD”.

 

Event Viewer

Security

Filter

Tabulate

Courtesy of SANS.org:

SANS Institute
InfoSec Reading Room
Windows Logon Forensics
Link

Event  ID = Windows XP / Windows 2000 Windows 2008/2012/2014, etc Event Description
 528 4624 Successful logon: A user successfully logged on to a computer. For
information about the type of logon, see the next section
 529 4625  Logon failure. A logon attempt was made with an unknown user name
or a known user name with a bad password. For Windows 2008 and
above, event ID 4625 logs every failed logon attempt with failure status
code regardless of logon type or type of account
530 4625 Logon failure for a logon attempt to log on outside of the allowed time
 531 4625  Logon failure for a logon attempt using a disabled account.
 532 4625  Displays the error message as seen by the user in the case or an error or exception. The error message text appears in the TextData field.
 533  4625 Logon failure. A logon attempt was made by a user who is not allowed
to log on at this computer.
 534 4625 Logon failure. The user attempted to log on with a type that is not allowed.
 535 4625 Logon failure. The password for the specified account has expired.
 536 4625 Logon failure. The Net Logon service is not active
 537 4634 Logon failure. The logon attempt failed for other reasons. In some
cases, the reason for the logon failure may not be known.
 538 4634 The logoff process was completed for a user.
 538/551 4647 A user initiated the logoff process. It is logged for Interactive and
RemoteInteractive logons in place of logoff event 538/4634.
 539 4625 Logon failure. The account was locked out at the logon
 540 4624 Successful network logon: A user successfully logged on over a
network.
 552 4648 A user successfully logged on to a computer using explicit credentials while already logged on as a different user
 638 4778 A user has reconnected to a disconnected terminal session.
 683 4625 A user disconnected a terminal session without logging off.

 

Image

 

Sample
Sample 01
Image – Top

Image – Bottom
Explanation
  1. Security ID
    • NULL SID
  2. Logon Type
    • Logon Type 3 is Network
  3. Impersonation Level
    • Impersonation
  4. New Logon
    • Security ID
      • LABdadeniji
    • Account Name
      • dadeniji
    • Account Domain
      • LAB
  5. Network Information
    • Workstation Name
      • DADENIJI
    • Source Network Address
      • w.x.y.z
    • Source Network Port
      • #####
  6. Detailed Authentication
    • Logon Type
      • NtLmSsp
    • Authentication Package
      • NTLM
    • Package Name (NTLM only)
      • NTLM V2
    • Key Length
      • 128

SysInternals

Process Explorer

Application Context

OLE-DB Provider can be configured to run in process or out of process.

If run in process we need to track the sql server service.

When ran out of process, we need to track via dllhost.exe

Sample

Sample – 01
Image

Explanation
  1. dllhost.exe
    • COM Class
      • MSDAINITIALIZE Class
      • c:program filescommon filessystem32ole dboledb32.dll
      • OLEDB Core Services

tasklist

Preface

The OLE-DB Provider can either be configured to run as in-process or out-of process.

When in process it runs within the sqlservr.exe address space.

When out of process, it runs within a surrogate process, dllhost.exe.

out of Process – dllhost.exe

syntax

tasklist /m /fi "Imagename eq dllhost.exe"

Output
Output # 01

Explanation
  1. We can see that Oracle dlls are loaded within the dllhost.exe

 

References

  1. Microsoft
    • Microsoft Developer
      • SQL BI / Data Access Technologies
        • Snehadeep
          • Troubleshooting “Cannot create an instance of OLE DB provider”
            Link
    • CSS SQL Server Engineers
      • pssql
        • How to get up and running with Oracle and Linked Servers
          Link
  2. Gianluca Sartori
    • Setting up linked servers with an out-of-process OLEDB provider.
      Link
  3. Sans Institute
    • Windows Login Forensics
      Link

SQL Server – Unable to shrink Transaction Log file

Background

Received an alert stating that we have gotten below our threshold of 10% on one of DB Servers.

Thanks goodness no late night phone calls.

Only saw it coming to work this morning.

The good and bad about smartphone.

TroubleShooting

Windows Desktop

Folder

Image

Explanation

  1. ppsivr_8.ldf is 28 GB

SQL Metadata

Outline

Here are dynamic management views ( DMVs) that we will access…

  1. dbcc sqlperf(logspace)
  2. sys.database_files
  3. sys.databases
  4. sys.dm_exec_requests & sysprocesses

 

Review Transaction Log File Sizes

SQL


dbcc sqlperf(logspace)

Output

 

Explanation

  1. ppsivr
    • Log Size MB
      • 27,699 MB
      • 27 GB

Review Database File Sizes

SQL


-- replace with targeted database
use [database]

SELECT 
		  [name]

		, [type]
			= tblSDF.[type_desc]

		, [filename]
			= tblSDF.[physical_name]

		, [allocatedMB]
			= tblSDF.size / 128

		, [usedMB]
			= CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)
				/ 128

		, [availableSpaceMB]
				 = ( size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) )
					/ 128

FROM sys.database_files tblSDF

order by
		tblSDF.size desc
;

Output

Review Database Transaction Log Re-usability State

SQL


declare @dbid   int
declare @dbname sysname

set @dbname = 'ppsivr'
set @dbid = db_id(@dbname)

select 
		    tblSD.[database_id]
		  , tblSD.[name]
		  , tblSD.[log_reuse_wait_desc]

from   sys.databases tblSD

where  (
			   (@dbid is null)

			or ( tblSD.[database_id] = @dbid)
		
	   )

Output

Explanation

  1. log_reuse_wait_desc
    • The database is not re-cycling its transaction log due to an ‘Active Transaction’

Review Current DB Requests

Code



declare @dbid   int
declare @dbname sysname

set @dbname = 'ppsivr'
set @dbid = db_id(@dbname)

select 
		  tblSDER.[session_id]

		, [database] 
			= db_name(tblSDER.[database_id])

		, [openTransaction]
			= tblSP.[open_tran]

		, tblSDER.[status]

		, tblSDER.[command]

		, tblSDER.[percent_complete]

		, tblSDER.wait_type

		, tblSDER.last_wait_type

		, tblSDER.[reads]

		, tblSDER.[writes]

from   sys.dm_exec_requests tblSDER

inner join master..sysprocesses tblSP

	on tblSDER.[session_id] = tblSP.[spid]

where  (

	         ( tblSDER.[open_tran] != 0 )

	      or ( tblSDER.[database_id] = @dbid)

      )



Output

Explanation

  1. User Transactions
    • It does not appear that we have user transactions that are current using our targeted database
  2. Background
    • WAIT_XTP_OFFLINE_CKPT_NEW_LOG
      • It does does not apply that WAIT_XTP_OFFLINE_CKPT_NEW_LOG updates the percent_complete column as so it is a bit difficult to track its current progress

 

Remediation

Here are the things we tried:

  1. dbcc shrinkfile
  2. Take database offline and bring back online

Attempt to Shrink Transaction Log Files

SQL


use [ppsivr]
go

dbcc shrinkfile('ppsivr_log')
go

Output

Explanation

  1. We want to take a good look at Current Size and Used Pages
    • In our case they are same
    • What is in our Transaction Log File?

 

Take Database Offline and bring it back online

Force termination of ongoing sessions

SQL


use master
go

ALTER DATABASE [ppsivr]
	SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	;

alter database [ppsivr] set offline;

alter database [ppsivr] set online;

ALTER DATABASE [ppsivr]
	SET MULTI_USER WITH ROLLBACK IMMEDIATE
	;

 

Summary

But, nothing worked!

We will come back and talk about what worked.

References

  1. Microsoft
    • Support.Microsoft.com
      • FIX: Offline checkpoint thread shuts down without providing detailed exception information in SQL Server 2014
        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