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

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

 

Guillaume Fourrat – VSS backup of AlwaysOn Secondaries

Background

Catching up a bit on other people’s posting and wanted to share this one from Guillaume Fourrat.

Guillaume titles it “VSS backup of AlwaysOn Secondaries” and it is here.

 

VSS backup of AlwaysOn Secondaries

 

Hi Everyone,

Today I’m going to highlight one of the changes brought by SQL Server 2012 SP2, which is the way we handle VSS Backup requests on AlwaysOn Secondary Databases.

Until now, any request for a FULL database backup (VSS_BT_FULL) thru VSS against a DB that is an AlwaysOn secondary was failing by design. Our VSS Writer SQLWriter would return FAILED_AT_PREPARE_SNAPSHOT (0x800423f4 – VSS_E_WRITERERROR_NONRETRYABLE).

A copy-only VSS backup (VSS_BT_COPY) would work.

The rationale being the following: a FULL backup is actually updating the target DB (reset of differential bitmap mainly), which is not possible when the DB is read only. Furthermore, because of the failover possibilities introduced by AlwaysOn, the favored option was to use Native SQL Servers backup that could rely on SQL Server variable backup location (http://msdn.microsoft.com/en-us/library/hh245119.aspx) if needed, and be ‘alwayson –aware’.

So that could be the end of the story: against an AlwaysOn Secondary DB, either use Copy_only VSS backups or use native backups.

But of course that wouldn’t make for a very interesting blog post…

Enters HyperV…

Consider the following scenario:

Large Windows HyperV Servers, hosting many HyperV virtual Servers, some of them SQL Servers in Always On architecture.

In short: a Private Cloud.

In this context, the IT usually takes care of the infrastructure at host level, and lets users deal with whatever happens within the VMs. One of the key tasks of IT is to manage backups (eg. for disaster recovery at datacenter level, or to provide restore of single VMs).

And the mainstream way to do that is to take VSS backups of the Host Disk Volumes. Microsoft System Center DPM will do exactly that.

But VSS backups are all about taking backups that are consistent: in ‘standalone’ SQL Server context you may already know all the logic SQLWriter implements to make sure that IO against the Databases that are backed up are frozen during the snapshot operation. So, back to our HyperV context, collecting a point-in-time image of a VHD without bothering with what happens within the VM would be defeating that very purpose right?

So what happens is the following: the VSS backup is propagated to Guest VMs thru HyperV integration services. That propagation hardcodes the backup type to VSS_BT_FULL, and therefore all guest VMs are initiating a VSS backup/snapshot in their own context. The purpose is to make sure that all applications are quiesced within all running VMs at the time we take the snapshot at the host level. This will enable us to generate a consistent backup of running VMs.

But let’s now put this in the context where one of the VMs is running an AlwaysOn secondary DB: you guessed it, it’s not going to work:

clip_image002

The important thing to know here is that the error returned by SQLWriter in VM3 will actually bubble up all the way to the initial VSS backup command at Host level, and will make it fail as a whole.

So we ended up in a situation where the IT infrastructure folks would see their Host backups failing from time to time for an unknown reason, depending on whether one or more of the VM present on the Host Drive being backup up had a secondary AlwaysOn DB! It could be that the AlwaysOn AG spans different HyperV hosts and therefore that the presence of a Secondary DB on a given Host is not something static over time.

Because of the complexity of the whole call chain, and because infrastructure IT operators may not have any visibility (or understanding) of the VM content, you can imagine what kind of troubleshooting challenges this would offer… And even when the situation is understood, well, what do we do? If host level backup must be somehow manually synchronized to the applicative state of Guest VMs, the Private Cloud scenario becomes quite more complicated all of a sudden.

This is the reason why SQL Server 2012 SP2 ships a code change for SQLWriter that will implement the following:

clip_image004

As you can see, SQLWriter now detects this specific situation and changes the backup type to VSS_BT_COPYONLY. This will only happen for VSS_BT_FULL backups against AlwaysOn secondary Dbs. VSS_BT_FULL backups against primary DB will happen without change.

In this case, the VSS backup will now successfully complete in VM3 and the host-level backup success state will no longer be tied to guest VM’s AlwaysOn activity. Private Cloud scenario unlocked!

Important note: the fact that VSS backup of AlwaysOn secondaries now works does not make it the preferred solution to backup SQL Server AlwaysOn architectures. The main purpose of the SP2 change is to avoid a situation where a single SQL Server in a VM fails a complete host-level backup operation that encompassing dozens of VMs.

The resulting backup for the VM hosting SQL should be considered a Disaster Recovery one, where AlwaysOn will be removed at once at restore time, not as a way to rebuild a subset of the nodes for an AlwaysOn Availability group. And for regular databases within the VM, that backup is as good any regular VSS one.

Finally, SQL Server 2012 SP2 only contains a partial fix for this issue. Servers running case-sensitive sort orders will require SQL Server 2012 SP2 Cumulative Update 2.

HTH,

Guillaume Fourrat
SQL Server Escalation Engineer
Microsoft France

Commentary

I really like the post for the reasons listed below:

  1. It is not something about a stupid query that can not be written a different way as it is embedded in the code or generated via an ORM Tool ( Entity Framework, Hibernate )
    • That is, it can be changed and optimized
    • Furthermore, we are not handicapped by what the developer thinks is fanciful and should be upheld by all means
  2. It affects a whole lot of parties
    • 3rd Party tools vendors that utilize VSS to backup SQL Server databases
    • Corporations that do not know or care what VSS is or does, they do not have a DBA and could care less that sql server datafiles can not just be backed up like other files
  3. The Application logs an error
    • The application actually logs the error and it is the hope that support staff will capture & review the errors periodically
  4. MSFT can and has provided a fix
    • MSFT has provided a fix and that means they are aware and tracking it
    • The patch will be brought into the main line code and all future versions will benefit going forward
    • If it breaks in subsequent version, we have every right to open a ticket thru Corporate Support or as a Connect Item
  5. And, above all else this is an actual technical problem
    • It is not a process problem
    • It is not a people problem
    • It is not one handicapped by long institutional or inter-department slight or grievance

Anti Virus – Free and Inexpensive AV for Windows Server

Background

These days the top tier AntiVirus tools are free for Desktop computers.

 

Server

As Vendors have to make money somehow they usually charge a bit for server products.

And, more for corporate deploy and management.

Here are some products that can be run on servers dating back to Windows 2003.

Vendor Product OS Link Price
Avast  
 Avast For Business Endpoint Security Microsoft Windows XP/Vista/7/8/8.1/10 (32/64 bit)
Windows Server 2003, 2008, 2012/R2, 2016
Product
Link
 $31.99/Year
Comodo  
 Comodo Cloud Antivirus XP (32bit), Win7/Win8/Win8.1/Win10 (32 bit & 64 bit) Product
Link
 Free
 Immunet  
 Immunet Windows XP, Windows Vista, Windows 7, Windows 8,
Windows 10, Windows 2003, Windows 2008, Windows 2012
 Link  Free
 Panda
 Panda Free AntiVirus  Link  Free

 

 

References

Vendors

  1. Avast
    • Avast For Business Endpoint Security – PC and server
      • Security Features
        Link
      • System Requirements
        Link
  2. Comodo
    • Introduction to Comodo Cloud Antivirus
      Link
    • System Requirements
      Link
    • Download
      Link
  3.  Panda
    • Operating System
      • What are the minimum installation requirements for Panda Security for Desktops?
        Link
    • Cloud Cleaner
      Link

 

Blogs

  1. Solvps
    • Top 3 Free Antivirus Compatible with Windows Server or VPS (Updated 2017)
      Link