SQL Server – Backup – When offline filegroups exist

Background

A few months we performed a partial restore.

When we tried to backup the partially restored database we ran into potholes.

Issues

Sample SQL and Error Messages

  1. SQL :- “backup database [DBLab] FILEGROUP =’PRIMARY’ TO DISK = ‘C:\Microsoft\SQLServer\Backup\DBLab\DBLab.bak’ WITH INIT, FORMAT , STATS=1
    • Error Message :- “Msg 3004, Level 16, State 1, Line 1
      The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.”
    • Corrective Measures
      • SQL :- “backup database [DBLab] FILEGROUP =’PRIMARY’, READ_WRITE_FILEGROUPS TO DISK = ‘C:\Microsoft\SQLServer\Backup\DBLab\DBLab.bak’ WITH INIT, FORMAT , STATS=1
  2. SQL :- “backup database [QA_rbpivr1] TO DISK = ‘Z:\SQLBackups\QA_rbpivr1\QA_rbpivr1.bak’
    • Error Message :- Msg 3007, Level 16, State 1, Line 1
      The backup of the file or filegroup “rbpivrLOB_01” is not permitted because it is not online. Container state: “Vacant” (6). Restore status: 0. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.”
    • Corrective Measures
      • SQL :- “backup database [QA_rbpivr1] FILEGROUP =’PRIMARY’, FILEGROUP =’DATA’, FILEGROUP =’INDEX’, FILEGROUP =’fg_rbpivr1_memory’
        TO DISK = ‘Z:\SQLBackups\QA_rbpivr1\QA_rbpivr1.bak’ WITH INIT, FORMAT , STATS=1″

 

Remediation

To backup we have to explicitly specify each filegroup we will like backed up.

And, in doing so, omit the filegroups that are offline.

 

Code

Sample


backup database [QA_rbpivr1]    
FILEGROUP ='PRIMARY'
    , FILEGROUP ='DATA'
    , FILEGROUP ='INDEX'
    , FILEGROUP ='fg_rbpivr1_memory'  
 TO DISK  = 'Z:\SQLBackups\QA_rbpivr1.bak' 
 WITH INIT, FORMAT  , STATS=1

Stored Procedure

 


use [master]
go

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

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

end
go

alter procedure [dbo].[sp_DBBackupCustomized]
(
      @dbname			sysname
    , @filenameBackup	sysname = null --= 'NUL'
    , @stat				smallint = 1
    , @scriptOnly		bit = 0
    , @debugGUI			bit = 0
)
as
begin

    set nocount on;

    declare @dbid int
    declare @tblFile TABLE
    (

          [id]			int not null identity(1,1)
        , [name]		sysname not null

        , [dataSpaceID] int null

        , [filegroup]	sysname null
                            default ''

        , [type]		int not null
        , [type_desc]	sysname not null

        , [state]		int not null
        , [state_desc]	sysname not null

        , [include]	as 
                        case
                            when (
                                        ( [type] in (0, 2) )
                                    and ( [state] = 0) 
                                 )
                                    then 1
                            else 0
                        end
    )

    declare @tblFileFromDB TABLE
    (
          [id]			int not null identity(1,1)	
        , [name]		sysname
        , [fileid]		int
        , [filename]	sysname 
        , [filegroup]	sysname null
        , [size]		sysname
        , [maxsize]		sysname
        , [growth]		sysname
        , [usage]		sysname
    )

    declare @tblFileGroupFromDB TABLE
    (
          [id]				int not null identity(1,1)
        , [name]			sysname
        , [dataSpaceID]		int
        , [type]			sysname
        , [type_desc]		sysname
    )

    declare @tblFileGroupIncluded TABLE
    (
          [id]		  int not null identity(1,1)
        , [filegroup] sysname not null
    )

    declare @id int
    declare @idMax int

    declare  @cmd nvarchar(255)

    declare  @FORMAT_FILEGROUP_QUERY		nvarchar(4000);
    declare  @FORMAT_BACKUP_CMD			nvarchar(4000);
    declare  @cmdBackup			nvarchar(4000);
    declare  @statAsVarchar     varchar(10)

    declare @fileGroupName		nvarchar(200)
    declare @fileGroupSingle	nvarchar(200)
    declare @fileGroupBuffer	nvarchar(4000)

    declare @fileGroupBufferNotEmpty bit

    declare @CHAR_NEWLINE		varchar(10)
    declare @CHAR_QUOTES_SINGLE	varchar(10)
    declare @CHAR_COMMA			varchar(10)

    DECLARE @pathBackupDefault NVARCHAR(4000) 

    set @CHAR_NEWLINE = char(13) + char(10)
    set @CHAR_QUOTES_SINGLE = ''''
    set @CHAR_COMMA	  = ' , '
        
    set @fileGroupBufferNotEmpty = 0

    set @FORMAT_BACKUP_CMD=
            'backup database [%s] '
                + ' %s ' -- FILEGROUP
                + ' TO DISK '
                + ' = ''%s'' '
                + @CHAR_NEWLINE + @CHAR_NEWLINE
                + ' WITH INIT, FORMAT '
                + ' , STATS=%s'
                
    set @FORMAT_FILEGROUP_QUERY
            = N'select [name], [data_space_id], [type], [type_desc] from [%s].sys.filegroups'

    set @dbid = db_id(@dbname);

    if (@filenameBackup is null)
    begin

       EXEC master.dbo.xp_instance_regread 
                      N'HKEY_LOCAL_MACHINE'
                    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
                    , N'BackupDirectory'
                    , @pathBackupDefault OUTPUT
                    , 'no_output' 

        set @filenameBackup = @pathBackupDefault + '\' + @dbname + '.bak'

    end

    insert into @tblFile
    (

          [name]
        , [dataSpaceID]

        , [type]
        , [type_desc]

        , [state]
        , [state_desc]

    )
    select 
              tblSMF.[name]
            , tblSMF.[data_space_id]

            , tblSMF.[type]
            , tblSMF.[type_desc]

            , tblSMF.[state]	
            , tblSMF.[state_desc]

    from   sys.master_files tblSMF

    where  (

                    --( tblSMF.[state] = 0 )
                    ( tblSMF.[database_id] = @dbid )

           )	

    --has_dbaccess(@dbname)
    --print N' '
    --select @cmd = N'select [name], [data_space_id], [type], [type_desc] from [%s].sys.filegroups'

    exec master.sys.xp_sprintf
              @cmd output
            , @FORMAT_FILEGROUP_QUERY
            , @dbname

    insert into @tblFileGroupFromDB
    (
          [name]		
        , [dataSpaceID]		
        , [type]	
        , [type_desc]	
    )
    exec (@cmd)

    update tblF
    
    set [filegroup] = isNull(tblFGFDB.[name], '')

    from   @tblFile tblF

    inner join @tblFileGroupFromDB tblFGFDB

            on tblF.[dataSpaceID] = tblFGFDB.[dataSpaceID]

    if (@debugGUI=1)
    begin

        select 
                 [src] = '@tblFile'
                , tblF.*

        from   @tblFile tblF

    end

    set @statAsVarchar = cast(@stat as varchar(10));

    set @id = 1
    set @idMax = ( select max([id]) from @tblFile)

    set @fileGroupBuffer = '';
    set @fileGroupBufferNotEmpty = 0;

    while (@id <= @idMax)
    begin

        set @fileGroupName = null

        select @fileGroupName = tblF.[filegroup]
        from   @tblFile tblF
        where  tblF.id = @id
        and    tblF.[include] = 1
        and    not exists
                    (
                        select 1
                        from   @tblFileGroupIncluded tblFGI
                        where  tblFGI.[filegroup] = tblF.[filegroup]
                    )

        if (@fileGroupName is not null)
        begin

            set @fileGroupSingle = 'FILEGROUP =' 
                                    + @CHAR_QUOTES_SINGLE 
                                    + @fileGroupName 
                                    + @CHAR_QUOTES_SINGLE;
            
            if  (@fileGroupBufferNotEmpty = 1)
            begin

                set @fileGroupBuffer = isNull(@fileGroupBuffer, '') 
                                            + @CHAR_NEWLINE
                                            + @CHAR_COMMA

            end
            else
            begin

                set @fileGroupBuffer = '  ' 
                                        + isNull(@fileGroupBuffer, '') 
                                        + @CHAR_NEWLINE

            end

            set @fileGroupBuffer = @fileGroupBuffer + @fileGroupSingle;

            set @fileGroupBufferNotEmpty = 1

            insert @tblFileGroupIncluded
            (
                [filegroup]
            )
            values
            (
                @fileGroupName
            )

        end

        set @id = @id + 1

    end

    exec master.dbo.xp_sprintf
              @cmdBackup output
            , @FORMAT_BACKUP_CMD
            , @dbname
            , @fileGroupBuffer
            , @filenameBackup
            , @statAsVarchar

    print '@cmdBackup ' + @CHAR_NEWLINE + @cmdBackup

    if (@scriptOnly =0)
    begin

        exec(@cmdBackup)

    end

end

go




SQL Server – Measuring Transaction Log Utilization at Session Level

Background

In the last few posts, we touched on measuring File IO utilization.

This is yet another post on that trail.

 

Transaction Log

What is Transaction Log?

Here is Microsoft’s definition of Transaction Log

Link
Every SQL Server database has a transaction log that records all transactions and the database
modifications made by each transaction.

What type of measurements are available

Here are some of the often used commands:

Granularity Command Reset
 Database
 dbcc sqlperf(logspace) dbcc sqlperf(‘sys.dm_os_wait_stats’,CLEAR);
backup log
Transaction Log Operation  fn_dblog

 

 

Reset Transaction Log

Here are some of the often used commands:

Command Explanation
Checkpoint For performance reasons, the Database Engine performs modifications to database pages in memory—in the buffer cache—and does not write these pages to disk after every change. Rather, the Database Engine periodically issues a checkpoint on each database. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.

The Database Engine supports several types of checkpoints: automatic, indirect, manual, and internal.

Backup Log to NUL Backup Transaction Log

 

 

Process

The task that we will be undertaking is loading a staging table.

And, our primary interest is to measure the amount of Transaction Log that is expended.

 

Outline

  1. Prepare Database for Transaction Log Profiling
    • Ensure that recovery mode is set for “full recovery”
    • Suspend any regular transaction level backup
    • Checkpoint
    • Perform a full database backup
  2. Load Data
    • Actual Steps
      • Truncate staging table
      • Load data into staging table
      • Measure Transaction Log Metrics
        • Overall Transaction Log Usage
          • Get overall Transaction Log usage using”dbcc sqlperf(logspace)
        • Get Transaction Log Usage at Operation & Context Level
          • Query fn_dblog to get current database log metrics
          • Group results by Operation and Context Level
    • Cleanup steps
      • Checkpoint database
      • Take Transaction level backup
  3. Repeat loading data for each attempted load option
  4. Cleanup
    • Revert back to original recovery mode
    • Re-enable any regularly scheduled transaction backup

 

Code

DBCC SQLPERF

SQLPERF – Create Tracking Table


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

	, [databaseName]	sysname not null

	, [logsizeMB]		decimal(30, 4) not null

	, [logSpaceUsed%]	decimal(20, 8) not null

	, [logSpaceUsed]
				as (
					([logsizeMB] * [logSpaceUsed%])
					 / 100
				  )

	, [status]	    smallint	   not null

)

SQLPERF – Load Tracking Table

 

DBCC SQLPERF(logspace) gets the log size in MB for all databases in the SQL Instance


insert into @tblSQLPerf
(
	  [databaseName]
	, [logsizeMB]	
	, [logSpaceUsed%]	
	, [status]			
)
exec('dbcc sqlperf(logspace) with no_infomsgs')


 

SQLPERF – Review Tracking Table

Get stored “DBCC SQLPERF(logspace)” data for the specific databases that we are interested in.


--Review results of tracking table
select 
	      [src] = '@tblSQLPerf'
		, [rowNumber]
			= ROW_NUMBER() OVER 
				(
					ORDER BY tblSP.[id] ASC
				)
		, tblSP.databaseName
		, tblSP.logsizeMB
		, tblSP.logSpaceUsed
		, tblSP.[logSpaceUsed%]
		--, tblSP.*

from    @tblSQLPerf tblSP

where   (
			( tblSP.[databaseName]= @dbName)
		)
order by
		tblSP.[id] asc

fn_dblog – Create Tracking Table

We create a tracking table that will house fn_dbLog data.


declare @tblFNDBLog TABLE
(
	  [id]			int not null 
	, [operation]	        sysname not null
	, [context]		sysname not null
	, [count]		bigint     not null
	, [logSize]		bigint     not null
)

fn_dblog – Load Tracking Table

Issue dn_dblog against current database.

We get a journal of all transaction log operations since last checkpoint.


insert into @tblFNDBLog
(
	  [id]
	, [operation]
	, [context]
	, [count]	
	, [logSize]	
)
select 
	          @id
		, [Operation]
		, [Context]
		, [cnt] = count(*)
		, LogSize
                   = sum([log record length]) as LogSize

from fn_dblog (NULL, NULL)

group by
	    [Operation]
	  , [Context]

 

 

fn_dbLog – Review Tracking Table – Count

Get stored “fn_dbLog” data.

Aggregated by Operation and Context.

First dataset that we project is count.

The number of times recorded for each grouping ( Operation and Context).

Data was captured in rows and we have to use pivot to display as columns.

 


select 
		  [src] = 'fn_dblog//Count]'
		, [operation]
		, [context]
		, [0]
		, [1]
		, [2]
		, [3]

from 
(

	SELECT [operation], [context], [id], [count]
	FROM   @tblFNDBLog

) AS SourceTable

PIVOT
(
	AVG([count])
	FOR [id] IN 
		(
			[0], [1], [2], [3]
		)

) AS pvt

order by

	isNull
	(
		  [0]
		, 0
	) 
	+ isNull
	(
		  [1]
		, 0
	) 
	+ isNull
	(
		  [2]
		, 0
	) 
	+ isNull
	(
		  [3]
		, 0
	) 

	desc


 

fn_dbLog – Review Tracking Table – Size

Get stored “fn_dbLog” data.

Aggregated by Operation and Context.

Second dataset that we project is size.

We sum up the size of transaction log uptake per Operation & Context.

Again, data was captured in rows and we have to use pivot to display as columns.

 


select 
		  [src] = 'fn_dblog//logSize]'
		, [operation]
		, [context]
		, [0]
		, [1]
		, [2]
		, [3]

from 
(

	SELECT [operation], [context], [id], [logSize]
	FROM   @tblFNDBLog

) AS SourceTable

PIVOT
(
	sum([logSize])
	FOR [id] IN 
		(
			[0], [1], [2], [3]
		)

) AS pvt

order by

	isNull
	(
		  [0]
		, 0
	) 
	+ isNull
	(
		  [1]
		, 0
	) 
	+ isNull
	(
		  [2]
		, 0
	) 
	+ isNull
	(
		  [3]
		, 0
	) 

	desc


 

Database Backup

Database Backup – Transaction Log

Here we review the transaction log backup metrics.

After each trial run we took a transaction log backup.


SELECT

		 [database]
			= tblBS.database_name  

	   , [TSStart]
			= tblBS.backup_start_date  

	   , [TSEnd]
			= tblBS.backup_finish_date

		, [durationInSec]
			= datediff
				(
					  second
					, tblBS.backup_start_date 
					, tblBS.backup_finish_date
				)

	   , [backupSizeInBytes]
			= tblBS.backup_size  

	   , [backupSizeInMegaBytes]
			= ( tblBS.backup_size / 1E6)

	   , [compressedBackupSizeInBytes]
			= tblBS.compressed_backup_size

	   , [compressedBackupSizeInMegaBytes]
			= (
				tblBS.compressed_backup_size
				/ 1E6
			  )	

		, [compressionRatio]
				= cast
					(
						(
						tblBS.compressed_backup_size
							/ NULLIF(tblBS.backup_size, 0)
						)
						as decimal(10, 4)
					)

	   , tblBMF.logical_device_name  

	   , tblBMF.physical_device_name   

FROM  msdb.dbo.backupset tblBS

left outer join msdb.dbo.backupmediafamily tblBMF

		ON tblBS.media_set_id  = tblBMF.media_set_id 

where  tblBS.[database_name] = db_name()

and    tblBS.[type] = 'L' 

--and    tblBS.backup_start_date > @dateStartedPostFullBackup

order by 
		tblBS.backup_start_date asc

Table Sizes

sp_spaceused – Create tracking table


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

	, [tableName]		sysname not null

	, [numberofRecords]	bigint  not null

	, [reservedKB]		varchar(100) not null
	, [reservedMB]		as 
						cast
						(
							(
								cast
								(
									replace
									(
										ltrim(rtrim([reservedKB]))
										, 'KB'
										, ''
									) 
									as decimal(30, 6)
								)
							) / (1024.00)
								as decimal(30, 6)
						)

						
	, [dataKB]			varchar(100) not null
	, [dataMB]		as 
					cast
						(
							(
								cast
								(
									replace
									(
										ltrim(rtrim([dataKB]))
										, 'KB'
										, ''
									) 
									as decimal(30, 6)
								)
							) / (1024.00)
								as decimal(30, 6)
						)

	, [indexKB]			varchar(100) not null
	, [indexMB]		
					as 
					cast
						(
							(
								cast
								(
									replace
									(
										ltrim(rtrim([indexKB]))
										, 'KB'
										, ''
									) 
									as decimal(30, 6)
								)
							) / (1024.00)
								as decimal(30, 6)
						)


	, [unusedKB]		varchar(100) not null
	, [unusedMB]		as 
						cast
						(
							(
								cast
								(
									replace
									(
										ltrim(rtrim([unusedKB]))
										, 'KB'
										, ''
									) 
									as decimal(30, 6)
								)
							) / (1024.00)
								as decimal(30, 6)
						)



)

sp_spaceused – Get table space used

We issued sp_spaceused against each table.


insert into @tblSpaceUsed
(

	  [tableName]
	, [numberofRecords]

	, [reservedKB]		
	, [dataKB]	
	, [indexKB]	
	, [unusedKB]	

)
exec sp_spaceused @objectName

 

Results

SQLPerf

fn_dbLog // Count

Explanation

  1. LOP_INSERT_ROWS & LCX_TEXT_MIX
    • Operation :- LOP_INSERT_ROWS
    • Context :- LCX_TEXT_MIX
    • Explanation
      • Insert Rows into LOB column ( LCX_TEXT_MIX )
      • Data written to Log file
  2. LOP_MODIFY_ROWS & LCX_TEXT_MIX
    • Operation :- LOP_MODIFY_ROWS
    • Context :- LCX_TEXT_MIX
    • Explanation
      • Insert Rows into LOB column ( LCX_TEXT_MIX )
      • Data read from Log file and written to datafile
  3. LOP_SET_FREE_SPACE & LCX_PFS
    • Operation :- LOP_SET_FREE_SPACE
    • Context :- LCX_PFS
    • Explanation
      • LOP_SET_FREE_SPACE
        • Looking for free Space
      • LCK_PFS
        • Page marked as deallocated in PFS
  4. LOP_MODIFY_ROW & LCK_PFS
    • LOP_MODIFY_ROW
      • Modify existing data
    • LCK_PFS
      • Update PFS Pages
  5. LOP_FORMAT_PAGE & LCK_TEXT_MIX
    • LOP_FORMAT_PAGE
      • Data is being loaded into a Heap
      • We are truncating data before loading
      • LOC_FORMAT_PAGE seems to indicate the zeroing out of pages from previous usage and in preparation for re-use
    •  LCX_TEXT_MIX
      • LCX_TEXT_MIX indicates LOB data pages
  6. LOP_MODIFY_ROW & LCK_TEXT_TREE
    • LOP_MODIFY_ROW
      • Modify existing data
    • LCK_TEXT_TREE
      • ???
  7. LOP_INSERT_ROWS & LCX_HEAP
    • LOP_INSERT_ROWS
      • Insert data
    • LCX_HEAP
      • Heap
    • Number of entries
      • Matches number of records inserted into table
  8. LOP_HOBT_DELTA & LCX_NULL
    • LOP_HOBT_DELTA
      • HOBT stands for “Heap or B-Tree
    • LCX_NULL
      • ???
  9. LOP_FORMAT_PAGE & LCX_HEAP
    • LOP_FORMAT_PAGE
      • LOP_FORMAT_PAGE is a type of log record that does a full or partial page format
    • LCX_HEAP
      • Heap is targeted
  10. LOP_INSERT_ROWS & LCX_INDEX_LEAF
    • LOP_INSERT_ROWS
      • Insert data
    • LCX_INDEX_LEAF
      • Index Leaf page
    • Number of entries
      • Depends on how records are inserted
  11. LOP_SET_BITS & LCX_IAM
    • LOP_SET_BITS
      • Set the bit for IAM Pages
    • LCX_IAM
      • SQL Server uses index allocation maps to track which pages have been allocated to which allocation unit. They are stored in special IAM pages. ( Link )
  12. LOP_SET_BITS & LCX_GAM
    • LOP_SET_BITS
      • Set the bit for GAM Pages
    • LCX_GAM
      • Global Allocation Map (GAM) page
  13. LOP_LOCK_XACT,  LCX_NULL
    • LOP_LOCK_XACT
      • Transaction Lock
  14. LOP_COMMIT_XACT,  LCX_NULL
    • LOP_COMMIT_XACT
      • Transaction Commit
  15. LOP_BEGIN_XACT,  LCX_NULL
    • LOP_BEGIN_XACT
      • Transaction Begin
  16. LOP_SET_BITS & LCX_DIFF_MAP
    • LOP_SET_BITS
      • Set the bit
    • LCX_DIFF_MAP
      • Likely Differential Map
      • ???
  17. LOP_FORMAT_PAGE  & LCX_TEXT_TREE
    • LOP_FORMAT_PAGE
      • ??
    • LCX_TEXT_TREE
      • ???
  18. LOP_INSERT_ROWS & LCX_TEXT_TREE
    • LOP_INSERT_ROWS
      • Insert Rows
    • LCX_TEXT_TREE
      • ???
  19. LOP_INSYSXACT & LCX_INDEX_LEAF
    • LOP_INSYSXACT
      • Internal System Table Insert
        • Likely due to table create statement
    • LCX_INDEX_LEAF
      • Index Leaf Operation
  20. LOP_BUF_WRITE  & LCX_NULL
    • LOP_BUF_WRITE
      • Write to Buffer
    • LCX_NULL
      • ???
  21. LOP_INSYSXACT & LCX_INDEX_INTERIOR
    • LOP_INSYSXACT
    • Internal System Table Insert
      • Likely due to table create statement
    • LCX_INDEX_INTERIOR
      • Interior = Non-Leaf Page
  22. LOP_COMPRESSION_INFO  & LCX_INDEX_LEAF
    • LOP_COMPRESSION_INFO
      • Compression Info
    • LCX_INDEX_LEAF
      • Index Leaf
  23. LOP_FORMAT_PAGE & LCX_BULK_OPERATION_PAGE
    • LOP_FORMAT_PAGE
    • LCX_BULK_OPERATION_PAGE
      • Bulk Insert
  24. LOP_MODIFY_HEADER & LCX_BULK_OPERATION_PAGE
    • LOP_MODIFY_HEADER
      • Header Page
    • LCX_BULK_OPERATION_PAGE
      • Bulk Insert
  25. LOP_EXPUNGE_ROWS & LCX_INDEX_LEAF
    • LOP_EXPUNGE_ROWS
      • Ghost Cleanup
    • LCX_INDEX_LEAF
      • Leaf Node

 

Others

  1.  LOP_END_CKPT
    • LOP_END_CKPT
      • CheckPoint – End
  2. LOP_BEGIN_CKPT
    • LOP_BEGIN_CKPT
      • Checkpoint – Begin
  3. LOP_XACT_CKPT
    • LOP_XACT_CKPT
      • Checkpoint – Transaction

 

 

Terminologies

PFS

  1. Paul Randal – Under the covers: GAM, SGAM, and PFS pages
    • PFS stands for Page Free Space, but the PFS page tracks much more than that. As well as GAM intervals, every database file is also split (conceptually) into PFS intervals. A PFS interval is 8088 pages, or about 64MB. A PFS page doesn’t have a bitmap – it has a byte-map, with one byte for each page in the PFS interval (not including itself).
    • Free space is only tracked for pages storing LOB values (i.e. text/image in SQL Server 2000, plus varchar(max)/varbinary(max)/XML and row-overflow data in SQL Server 2005) and heap data pages. This is because these are the only pages that store unordered data and so insertions can occur anywhere there’s space. For indexes, there’s an explicit ordering so there’s no choice in the insertion point.

LCK_PFS

  1. SQL Server: Curious Case of Logging in Online and Offline Index Rebuild in Full Recovery Model
    Link

    • Page that was being locked and context column show LCK_PFS which means for this page just it is marked as de-allocated in PFS page.

 

IAM

  1. sqlity.com
    • Home » Blog » The Index Allocation Map (IAM)
      • The Index Allocation Map (IAM)
        Link

LOB

  1. sqlity.com
    • Home » Blog » LOB Data Pages
      • Page Type 3 – LOB Data Pages explained
        Link

        • sys.dm_db_database_page_allocations

Background Tasks

  1. Ghost Cleanup
    • Tracking
      • SELECT * FROM master.dbo.sysprocesses WHERE cmd LIKE ‘%ghost%’

Summary

Intentionally leaving out any advisory.

Just wanted to point at reviewing Transaction Log contents and size as one way to profile Database Applications.

Transaction Backup Sizes present one option to review overall sizes.

fn_dblog querying through aggregating the Operation and Context columns allows one to follow the trail of SQL Statements.

 

References

  1. Server & Tools Blogs > Data Platform Blogs > SQL Server Database Engine Blog
    • Paul Randal – Under the covers: GAM, SGAM, and PFS pages
      Link
  2. Wiki > TechNet Articles > SQL Server: Curious Case of Logging in Online and Offline Index Rebuild in Full Recovery Model
    • SQL Server: Curious Case of Logging in Online and Offline Index Rebuild in Full Recovery Model
      Link
  3. sqlFascination
    • Andrew Hong
      • A Strange Case of a Very Large LOP_FORMAT_PAGE
        Link
      • Decoding a Simple Update Statement Within the Transaction Log
        Link
      • How Do You Decode A Simple Entry in the Transaction Log? (Part 1)
        Link
  4. Simple Talk
    • Uwe Ricken
      • DELETE Operation in SQL Server HEAPs
        Link
  5. Akash Gautam
    • sqlservergeeks.com
      • SQL Server Recovery Model Deep Dives
        Link
  6.  WikiDBA
    •  Virendra Yaduvanshi
      • Finding user who ran insert on SQL Server
        Link
  7. SqlShack
    • Jefferson Elias
      • How to take advantage of SQL Server Transaction Log
        Link
  8. SQLSkills
    • Paul Randle
      • LOP_FORMAT_PAGE is a type of log record that does a full or partial page format
        Link
      • Tracking page splits using the transaction log
        Link
      • Inside the Storage Engine: Ghost cleanup in depth
        Link
  9. Martin Smith
    • stackoverflow
      • Why does DELETE leave a lingering effect on performance?
        Link
  10. Paul White
    • Paul White & DiamondBeezer
      • SQL Server High Availability and Sharepoint – log file grows when no database activity
        Link
  11. raresql.com
    • SQL Server – How to find Who Deleted What records at What Time
      Link
  12. EfficientSQL
    1. OPTIMIZING TRANSACTION LOG PERFORMANCE
      Link

 

Adam Blai

 

Videos

  1. Adam Blai – Demonology Seminar (Audio)
    • Video
      Link
    • Published on :- 2016-Apr 23
    • Occasion :- Adam Blai speaking at One Faith Experience – April 2016.
    • religiousdemonology.com
      Link
  2. Catholic Demonologist Adam Blai On Demonic Possession
    Video

 

Indepth

Adam Blai – Demonology Seminar (Audio)

  1. Exorcist
    • Only a priest can do exorcist by Canon Law
  2. Participation
    • Over 100 solemn exorcism
  3. Unworthiness
    • I am not holy
    • Do not be afraid to do what God the father has called you to do
    • Do not let Satan trick you into thinking you are not holy, you are not equipped
    • He is going to use if you say Yes
  4. Reasons
    • Do not do it for
      • Excitement
      • Selfish reasons
    • Do it
      • It has to be because you are called
      • Asked to serve
        • By Bishop or elder
  5. Election
    • God allows us to be tempted
      • So that we can exercise free will
      • Grants us an opportunity to love God and be obedient to God
      • Corrective experiences
        • Enter into a deeper relationship with God
        • God allows extraordinary activities so that we can know the limits of our power & grace
  6. Permission
    • The Devil can not do anything unless permitted God
  7. Authority
    • Authority given by Jesus to the Apostles
    • Permission to Bishop
      • Bishop lent to the Priest
    • Oversight
      • Do not want to exercise if patient is
        • Mentally ill
        • Physically ill
  8. Demons
    • Demons are fallen Angels
    • At Creation
      • They had a ministry
      • They had work assignment
  9. Dominic Activity Increases ( Segment 12:### – )
    • New Missions
      • In the History of the Church for missionary activities
      • When Christianity goes into new communities
    • Missionary Country in Reverse
      • In the west, we ar nation in reverse
      • Reject God
      • Give demonic new room
    • Pray
      • Pray for priest
      • help up it
      • put on the altar every day

Error – System.InvalidCastException: Unable to cast COM object of type ‘Microsoft.Office.Interop.Excel.ApplicationClass’ to interface type ‘Microsoft.Office.Interop.Excel._Application’

Introduction

A very good friend email me this afternoon that he his working with our corporate support engineer and getting the message pasted below.

Error

Error Image

Error Message

 

Error Message – Details

errorMessage_details__20170621_0523PM

Error Text

 

System.InvalidCastException: Unable to cast COM object of type ‘Microsoft.Office.Interop.Excel.ApplicationClass’ to interface type ‘c._Application’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{000208D5-0000-0000-C000-000000000046}’ failed due to the following error: Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED)).

How did we get here?

Here are what preceded the error message:

  1. A new laptop with Microsoft Office 2016 installed
  2. Tried to use MS Office 2010 based Access Application
    • Corrupted MS Office Access Database
  3. Uninstalled MS Office 2016
  4. Installed MS Office 2010

 

TroubleShooting

Outline

  1. Ensure that MS Excel is installed and running
  2. Attempt to have Application Save file as Excel File and Read directly from Excel
  3. Review Windows Registry
  4. Review Component Services

 

Steps

Ensure that MS Excel is installed and running

Run MS Excel

 

In Application Save File As Excel, Start Excel, and Load File

The problem we were having is with trying to have application use OLE to pass generated data to Excel.

We thus tried out a different track, which is to run query and save the result set into a file.

Once saved in a file, we launch Excel and loaded the generated file.

Review Windows Registry

Regedit or regedt32

Find – 000208D5-0000-0000-C000-000000000046

 

Find – Microsoft.Office.Interop.Excel

 

Map Microsoft.Office.Interop.Excel, Version =w.x.y.z to Office Version

 

Product Version Version ( RTM / SP[n] ) Version #
 Office 2010
 RTM  14.0.0.0
 Office 2007  
 RTM  12.0.4518.1014
 SP1  12.0.6213.1000
 SP2  12.0.6425.1000
 SP3  12.0.6607.1000
 Office 2003
 RTM  11.0.0.0

 

 

Remediation

Control Panel – Programs and Features

Accessed Programs and Features and please do the following:

  1. Installed Features
    • Make sure that the following features are included
      • .NET Programmability Support
  2. Repair
    • Chose to Repair Microsoft Office 2010

 

Once .Net Programmability feature is installed and Repair performed if need be, please reboot computer.

 

Folder

Folder – C:\Windows\assembly

Please review C:\Windows\assembly and look for Microsoft.Office.Interop.* files

The files you want to look for are:

  1. Assembly
    • Microsoft.Office..Interop.Excel
      • Version :- 14.0.0.0

 

References

Office Product & Interop

  1. Office 2007
    • How to determine which version of a 2007 Office product is installed
      Link
  2. Office 2003
    • Installing and Using the Office 2003 Primary Interop Assemblies
      Link

 

Blog – Support

  1. Microsoft
    • Description of the startup switches for Excel
      Link
  2.  act!
    • Error: “Unable to cast COM object of type ‘Microsoft.Office.Interop.Outlook.ApplicationClass’ to interface type ‘Microsoft.Office.Interop.Outlook._Application’… When Synchronizing Act! and Microsoft® Outlook®
      Link
  3. netdocuments
    • “Library Not Registered” error when using EMS in Outlook
      Link

Q/A

  1. COM related exception
    Link

SQL Server – Files In use – Day 1

Background

One of the many areas that that one needs to keep an eye one when monitoring database engines  is which files are opened, how they are opened ( exclusively, read only), what other processes are competing for them, etc.

Day 1

This is Day 1 and so we will start off with the basic tools.

 

Microsoft

Lineage

Microsoft really did a very job with Resource Monitor.

Prior to Resource Monitor, Task manager was the go to quick tool.

Task Manager

Here is what Task Manager exposes:

unfortunately, it only exposes information at process level.

 

Resource Monitor

Tab – Disk

Here we see the active Disk Activities.

Explanation

  1. We are able to filter by Process
  2. And, we can order by
    • Process Name
    • File name
    • Reads/Writes/Totals

Tab – CPU

Tab – CPU – Original

When we switch over to the CPU Tab, here is what we see

Explanation

We are forced to choose a process.

Tab – CPU – Process = sqlservr.exe

Once we chose sqlservr.exe from the list of processes:

Tab – CPU – Process = sqlservr.exe = Drive C:

Explanation:
  1. Unfortunately, we were getting really bad storage utilization a few months ago, and had to move to System Drive C: till more storage was allocated
  2. Will come back and move the rollover data and log files
Tab – CPU – Process = sqlservr.exe = Drive D & E:

Explanation:
  1. Most of our SQL Server Data and log files are on Drive D & E:
  2. Will come back and segregate them

SysInternals

I real like the straightforwardness of SysInternals tools.

Handle

Here is how to use handle.

Scripts

Look for file handles

Here we ask for a specific process:

  1. -p = sqlservr
  2. type = file
Code

handle -nobanner -p sqlservr | find "File" | more

Output

Look for file handles – File  – Extension [ mdf, ndf, ldf]

Here we ask for files that have have df in their names.

  1. -p = sqlservr
  2. type = file
Code

handle -nobanner -p sqlservr | find "File" | find "df" | more

Output

 

SQL Server

Sql Server Management Studio ( SSMS )

You can also use SSMS Activity Monitor.

But, to me it is a big hammer to what one really needs.

 

Dedicated

Can’t go anywhere else but to SysInternals, Mark Russinovich & Bryce Cogswell.

To me they made it cool to want to look into things, Windows Internals anyone.

SQL Server – Extended Event – Metadata – Get Current Target File

Background

For some optimization work that we are doing, I will like to know the name of the Extended Events’ Engine currently targeted file.

 

Code

Guide

As always we know that Microsoft exposes a lot of metadata via Dynamic Management Views.

The specific ones that we will use are:

  1. sys.dm_xe_sessions
    • Currently executing Extended Event Sessions
  2. sys.dm_xe_session_targets
    • Targets for currently executing sessions

SQL



SELECT
		  [extendedEvent]
			= tblXES.[name]

		, [executionCount]
			= tblXEST.execution_count

		, [executionDuration]
			= tblXEST.execution_duration_ms

		, [targetData]
			= tblXEST.target_data

		, [fileCurrent]
			= cast(tblXEST.target_data as xml).value('(//File/@name)[1]','nvarchar(255)')

FROM sys.dm_xe_sessions AS tblXES

INNER JOIN sys.dm_xe_session_targets AS tblXEST

    ON tblXES.[address] = tblXEST.[event_session_address]

where 
	(

		( tblXEST.target_name = 'event_file' )

	)


Output

Sample Output