SQL Server – Identify Lock TimeOuts via Extended Events ( from converted Trace File )

 

This is the 3rd in a series of posts on Identifying Lock Timeouts.

Here are earlier posts:

  1. SQL Server – “Lock Timeouts” – Identifying through SQL Server Profiler
    Link
  2. SQL Server – “Lock Timeouts” – Trigger Timeouts – SSMS – Table Designer
    Link

Extended Events

Extended Events introduced in SQL 2008 is lighter to run on a system compared to client side SQL Server Profiler or Server Side Trace.

Design Tooling

Unfortunately, when first introduced with SQL 2008 tooling for creating events was not included.

SQL 2008-R2 came with tooling for designing new Extended Events ( EE ) and modifying existing ones.

But, unfortunately, in my humble estimation, the tooling did not sufficiently expand on what is available via SQL Server Profiler.

In  a later post, we will touch on some items that can be bettered in the GUI for creating and modifying Extended Events.

 

 

Convert SQL Server Profiler to Extended Event

Jonathan Kehayias

SQLSkills has a very good Stored Procedure ( SP ) that generates a script for extended event from a running trace.

It is available here.

As always I am late as this tool was released back in 2012.

 

Create SP

Once downloaded, please create the SP, sp_SQLskills_ConvertTraceToExtendedEvents.

 

Use SP

To use the SP, please do the following:

  1. Start SQL Server Profiler Trace
  2. Get the Trace Number by running “select * from sys.traces
  3. Generate Extended Event Script – Pass along Trace ID
  4. Capture & Appy Generated Script
  5. Review and amend generated Extended Event
  6. Start Extended Event
  7. View Life Data

 

Get Trace Number
Code

select *

from sys.traces tblST

-- Not Default Trace
where tblST.[is_default] = 0

-- Trace is running
and tblST.[status] = 1

order by
tblST.start_time desc

 

Output

 

Generate Extended Event Script
Code
use master
go

declare	@TraceID INT
declare	@SessionName NVARCHAR(128)
declare	@PrintOutput BIT
declare	@Execute BIT

set		@TraceID = 4
set		@SessionName = 'IdentifyLockTimeOut'
set 	@PrintOutput = 1
set	    @Execute = 0


exec sp_SQLskills_ConvertTraceToExtendedEvents
		  @TraceID = @TraceID
		, @SessionName = @SessionName
		, @PrintOutput = @PrintOutput
		, @Execute = @Execute

 

Output

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'IdentifyLockTimeOut')
	DROP EVENT SESSION [IdentifyLockTimeOut] ON SERVER;
GO
CREATE EVENT SESSION [IdentifyLockTimeOut]
ON SERVER
ADD EVENT sqlserver.lock_cancel(
	ACTION 
	(
			  sqlserver.client_app_name	-- ApplicationName from SQLTrace
			, sqlserver.client_hostname	-- HostName from SQLTrace
			, sqlserver.client_pid	-- ClientProcessID from SQLTrace
			, package0.event_sequence	-- EventSequence from SQLTrace
			, sqlserver.is_system	-- IsSystem from SQLTrace
			, sqlserver.nt_username	-- NTDomainName from SQLTrace
			, sqlserver.request_id	-- RequestID from SQLTrace
			, sqlserver.server_instance_name	-- ServerName from SQLTrace
			, sqlserver.server_principal_name	-- LoginName from SQLTrace
			, sqlserver.server_principal_sid	-- LoginSid from SQLTrace
			, sqlserver.session_id	-- SPID from SQLTrace
			, sqlserver.session_resource_group_id	-- GroupID from SQLTrace
			, sqlserver.session_server_principal_name	-- SessionLoginName from SQLTrace
			, sqlserver.transaction_id	-- TransactionID from SQLTrace
			-- IntegerData2 not implemented in XE for this event
	)
	WHERE 
	(
			duration >= 30000000
	)
),
ADD EVENT sqlserver.rpc_completed(
	ACTION 
	(
			  sqlserver.client_app_name	-- ApplicationName from SQLTrace
			, sqlserver.client_hostname	-- HostName from SQLTrace
			, sqlserver.client_pid	-- ClientProcessID from SQLTrace
			, sqlserver.database_id	-- DatabaseID from SQLTrace
			, sqlserver.database_name	-- DatabaseName from SQLTrace
			, package0.event_sequence	-- EventSequence from SQLTrace
			, sqlserver.is_system	-- IsSystem from SQLTrace
			, sqlserver.request_id	-- RequestID from SQLTrace
			, sqlserver.server_instance_name	-- ServerName from SQLTrace
			, sqlserver.server_principal_name	-- LoginName from SQLTrace
			, sqlserver.session_id	-- SPID from SQLTrace
			, sqlserver.session_resource_group_id	-- GroupID from SQLTrace
			, sqlserver.session_server_principal_name	-- SessionLoginName from SQLTrace
			, sqlserver.transaction_id	-- TransactionID from SQLTrace
			-- IntegerData not implemented in XE for this event
			-- BinaryData not implemented in XE for this event
	)
	WHERE 
	(
			duration >= 30000000
	)
),
ADD EVENT sqlserver.sql_batch_completed(
	ACTION 
	(
			  sqlserver.client_app_name	-- ApplicationName from SQLTrace
			, sqlserver.client_hostname	-- HostName from SQLTrace
			, sqlserver.client_pid	-- ClientProcessID from SQLTrace
			, sqlserver.database_id	-- DatabaseID from SQLTrace
			, sqlserver.database_name	-- DatabaseName from SQLTrace
			, package0.event_sequence	-- EventSequence from SQLTrace
			, sqlserver.is_system	-- IsSystem from SQLTrace
			, sqlserver.nt_username	-- NTDomainName from SQLTrace
			, sqlserver.request_id	-- RequestID from SQLTrace
			, sqlserver.server_instance_name	-- ServerName from SQLTrace
			, sqlserver.server_principal_name	-- LoginName from SQLTrace
			, sqlserver.server_principal_sid	-- LoginSid from SQLTrace
			, sqlserver.session_id	-- SPID from SQLTrace
			, sqlserver.session_resource_group_id	-- GroupID from SQLTrace
			, sqlserver.session_server_principal_name	-- SessionLoginName from SQLTrace
			, sqlserver.transaction_id	-- TransactionID from SQLTrace
	)
	WHERE 
	(
			duration >= 30000000
	)
),
ADD EVENT sqlserver.sql_statement_completed(
	ACTION 
	(
			  sqlserver.client_app_name	-- ApplicationName from SQLTrace
			, sqlserver.client_hostname	-- HostName from SQLTrace
			, sqlserver.client_pid	-- ClientProcessID from SQLTrace
			, sqlserver.database_id	-- DatabaseID from SQLTrace
			, sqlserver.database_name	-- DatabaseName from SQLTrace
			, package0.event_sequence	-- EventSequence from SQLTrace
			, sqlserver.is_system	-- IsSystem from SQLTrace
			, sqlserver.nt_username	-- NTDomainName from SQLTrace
			, sqlserver.request_id	-- RequestID from SQLTrace
			, sqlserver.server_instance_name	-- ServerName from SQLTrace
			, sqlserver.server_principal_name	-- LoginName from SQLTrace
			, sqlserver.server_principal_sid	-- LoginSid from SQLTrace
			, sqlserver.session_id	-- SPID from SQLTrace
			, sqlserver.session_resource_group_id	-- GroupID from SQLTrace
			, sqlserver.session_server_principal_name	-- SessionLoginName from SQLTrace
			, sqlserver.transaction_id	-- TransactionID from SQLTrace
	)
	WHERE 
	(
			duration >= 30000000
	)
),
ADD EVENT sqlserver.xml_deadlock_report(
	ACTION 
	(
			  sqlserver.server_instance_name	-- ServerName from SQLTrace
			, sqlserver.server_principal_name	-- LoginName from SQLTrace
			, sqlserver.session_id	-- SPID from SQLTrace
	)
)
ADD TARGET package0.ring_buffer


Review and amend generated Extended Event

Once Extended Event is created, view and amend it through GUI

Object Explorer

Here is the Object Explorer view, please select the Session, view and amend the targeted Session

 

Live Data

Once the EE is reviewed, please select the EE, start it and select the “Watch Live Data“.

We created a new transaction, added data to the table, and tried to modify the same table.

A short while later, we were able to trigger lock timeout.

Thankfully, our new EE captured same and here is what is captured

 

Live Data – lock-cancel ( Image )

 

Live Data – lock-cancel ( Tabulated )

 

Field Value
 associated_object_id  110623437
 client_app_name  Microsoft SQL Server Management Studio
 client_hostname  DADENIJI
 databse_id  7
 database_name
 duration  89997000
 owner_type  Transaction
 resource_o  110623437
 server_instance_name  DADENIJI\v2014
 server_principal_name dadeniji

 

 

 

Live Data – sql_batch_completed ( Image )

 

Live Data – sql_batch_completed ( Tabulated )

 

Field Value
batch_text  ALTER TABLE dbo.person ADD   gender char(1) NULL
 client_app_name  Microsoft SQL Server Management Studio
 client_hostname  DADENIJI
 databse_id  7
 database_name  DBLab
 duration  89993130
 result  Abort
 row_count  0
 server_instance_name  OBJECT
 server_principal_name dadeniji
 logical_reads  0
 physical_reads  0
 writes  0

 

 

 

Analysis

Processing Time Out

Previous to triggering the error, we increased our timeout by accessing Options\  Designers \ Table and Database Designers.

 

Captured Data

The two events we are tracking offers different dataset.

The “Lock Cancel” captures the following:

  1. The Database ID & Object ID of the Targeted Object
  2. Mode :- SCH_M – Schema Modification
  3. Forensics such as Application Name, Host Name, Database ID

On the other hand, the “SQL Batch Completed” captures the following:

  1. Batch Text :- “ALTER TABLE dbo.person ADD   gender char(1) NULL
  2. Pertinent Data such as Duration, Result – Abort in this case, Rowcount ( 0 )
  3. Forensics such as Application Name, Host Name, Database ID, Database name

 

Dedicated

Can’t go anywhere else but JK as in Jonathan Kehayias.

 

Summary

In later posts will discuss creating Extended Events, Monitoring, and measuring impact on the system.

SQL Server – SSMS – Options – “Override connection string time-out value for table designer updates”

 

Background

This is the third in a series of posts on timeouts

SQL Server Management Studio ( SSMS)

Designers

For those that use the Designer built into SSMS to make structural table changes, the Designer Option panel is a good place to go and make sure that the default settings are what you want them to be.

Screen Shot

Here is what that screen looks:

Initial

Post Changes

Image

Explanation
  1. Here we changed the “Transaction Time-out after:” from the default of 30 seconds to 90 seconds

Misnomer

I think outside of the Database World and specifically SQL Server, it is OK to use the phrase & words, but I think for a tool that targets SQL Server and only that table, a bit more care should be taken.

And, you likely ask why.

Grouping :- Override connection string time-out value for table designer updates
Phrase :- Transaction time-out after

Here are the difficulties that I have with the Group Label “Override connection string time-out value

  1. We all know that the Connection String does not expose command execution nor lock timeout
    • Command Execution can be set in the Connection or Command Object, but not in the actual connection string
    • Because of this understanding, coders have to look into the code and set the timeouts
    • They are not able to do so declaratively in the Web.config and thus not suffer the pain of recompile
  2. There is more than enough misappropriation that Connection Open timeouts and Command Execution timeouts, are one the same

Microsoft – Connect

  1. Item #  :- 3130697
    • Link :- Link
      Opened By: Daniel Adeniji
      Date Opened:- 2017-March-25
      Type :- Suggestion
      Status :- Active

SQL Server – “Lock Timeouts” – Trigger Timeouts – SSMS – Table Designer

Background

In our initial post we spoke about experiencing an Outage.

Our Clients in the Development side of the house explained they have being logging “Lock Timeouts“.

We wanted to see what tools we can use to detect similar occurrences on the DB.  And, we cited SQL Server Profiler as one of those tools.

In this post, we will attempt to simulate time outs due to expired Lock requests and see how well SQL Server Profiler performs.

 

Lab

Outline

  1. Start SQL Server Profiler and set to capture event
  2. Create a bare minimum table
  3. Add data
    • Start Transaction
    • Add a couple of records
    • Temporarily leave Transaction Uncommitted
  4. In another session, return to Table designer & initiate changes
    • Add one or two new columns, attempting to make design changes

Steps

SQL Server Profiler

Here are the events that we said we will capture:

Image

Image – Events Selection

Image – Edit Filter

 

Tabulation

Tabulation – Events Selection
Event Category Event
 Locks  Lock:Cancel
 Stored Procedures  RPC:Completed
 TSQL  SQL:Batch Completed

 

 

Tabulation – Filter
Column Value
 Duration  30000

 

 

Create a Bare Minimum Table

Image

Explanation

In the screen above, we have created a table and added a couple of columns ( personID and personName).

 

Attempt to add data

Script


set nocount on
go
set XACT_ABORT on
go

use [DBLab]
go

declare @transactionComplete bit

set @transactionComplete = 0

begin tran

	insert into [dbo].[person]
	default values;


 

Table Designer

Table Designer – Adding new column – dateAdded

Image

 

Table Designer – Execution Timeout Expired

Image

Textual

'person' table
- Unable to modify table.
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

 

SQL Server Profiler

Image

Lock:Cancel

Tabulated
Event Category Event Columns Values
 Lock  
 Lock:Cancel
 TextData
 Application  Microsoft SQL Server Management Studio
 NTUserName  dadeniji
 Duration  29999
 StartTime  2017-03-25 16:23:18.190
 EndTime  2017-03-25 16:23:18.190
 Mode  2-SCH-M
 ObjectID 110623437
 ObjectID2  110623437
 OwnerID  1-TRANSACTION
 DatabaseID  7
 DatabaseName  DBLAB
 Hostname  DADENIJI
 IntegerData2  0-LOCK

 

 

Explanation
  • You want to pay attention to ObjectID and Database ID
    • Those two will lead you to Database, Schema, and Object name
  • Mode
    • Will lead you to desired Lock
      • In our case, 2-SCH-M or Schema Modification Lock
  • And, of course Application, Host, and Username, and TextData
    • Determine causation and forensics

 

SQL: Batch Completed

Tabulated
Event Category Event Columns Values
 T-SQL  
SQL: Batch Completed
 TextData  ALTER TABLE dbo.person ADD dateAdded null
 Application  Microsoft SQL Server Management Studio
 NTUserName  dadeniji
 Duration  29999
 StartTime  2017-03-25 16:22:48.190
 EndTime  2017-03-25 16:23:18.190

 

 

Explanation
  • You want to pay attention to ObjectID and Database ID
    • Those two will lead you to Database, Schema, and Object name
  • Mode
    • Will lead you to desired Lock
      • In our case, 2-SCH-M or Schema Modification Lock
  • And, of course Application, Host, and Username, and TextData
    • Determine causation and forensics

 

 

Dedicated

Dedicated to MSFT’s SQL Server Team.

 

 

SQL Server – “Lock Timeouts” – Identifying through SQL Server Profiler

Preface

We experienced an outage this last Monday.

One of the errors logged in the WebSphere log file is the one pasted below:

Lock request time out period exceeded.

TroubleShooting

SQL Server Profiler

One of the most formidable tool for SQL Server TroubleShooting is SQL Server Profiler.

It allows the DBA to monitor several activities.

 

SQL Server Profiler – Take Care

Because it is an expensive to run it, I will suggest the following:

  1. Carefully choose which events you will like tracked
  2. Convert from Client Tracking to Server Tracing

 

SQL Server Profiler – Events

Here are the events to track on…

Tabulated

Events File Version
 Locks  
 Lock: Cancel
 Lock: Timeout
 Lock:Timeout ( timeout > 0)
 Stored Procedures
 RPC:Completed
 TSQL  
 SQL: BatchCompleted

 

Image

SQL Server Profiler – Filter

To augment the events we need to place filters and thus ensure we are not overloaded.

Tabulated

Events File Version Specific
 Duration  
 Greater or equal to
 30000  ( Please note value is in milliseconds, and so we have it set for 30 seconds )
 Exclude rows that do not contain values

 

 

Image

 

 

Dedicated

Duckworth & Borris Callens

How to find timed out statements in SQL 2005 profiler
Link

 

References

  1. How to find timed out statements in SQL 2005 profiler
    Link
  2. Transcender
    • Application Deadlock
      You are the database administrator for a banking company. You manage all the SQL Server 2008 R2 databases of the company. The company stores customer-related data in the database named Cust01. This database is accessed by most users in the company for different purposes. The users daily perform insert and updates to the database through a .NET application.
      Eric, a user in the database, complains that his transaction has frozen and that he is not able to perform any operation in the database. You find out that the problem is due to a deadlock. You want to find out the user who is the other participant in the deadlock.
      Link

 

Summary

In follow-up posts, we will take things to the Lab and do things that will trigger timeouts.

SQL Server 2005 – Express Edition – Manual Updates

Background

As promised a couple of posts back, over this last weekend we successfully patched an old SQL Server Express v2005 RTM Edition  to v2005 SP4.

In our initial post, we spoke of the difficulty we experienced trying to patch v2005 using Windows Update.

In this post we skipped having Automatic Updates apply the patch and downloaded the patch and manually applied it.

We stumbled badly but thankfully discovered workarounds through Google.

 

TroubleShooting

Event Viewer

Error 1260 – Windows cannot open this program because it has been prevented by a software restriction policy

Error Image

EventID_11260_MicrosoftSQLServerExpressEdition_Error_1260_0237PM (Brushed Up)

 

Textual

  1. Source :- MsiInstaller
  2. Type :- Error
  3. Event ID :- 11260
  4. Product :- Microsoft SQL Server Express Edition – Error 1260.  Windows cannot open this program because it has been prevented by a software restriction policy.

 

Remediation

Outline
  1. Get Application’s GUID
  2. Add it to the exception list
ScreenShot

ScreenShot – Before Adding Application GUID

WLRegistry_20170319_0243PM

ScreenShot – Before Adding Application GUID { 2AFFDD7* }

WLRegistry_20170319_0244PM

SQL Server – Error Log

Error: 17207, Severity: 16, State: 1 – FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file

Error Text

2017-03-19 14:02:52.13 spid5s      Starting up database ‘master’.
2017-03-19 14:02:52.18 spid5s      Error: 17207, Severity: 16, State: 1.
2017-03-19 14:02:52.18 spid5s      FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file ‘E:\Program Files\Microsoft SQL Server\v2005\SQLExpress\MSSQL.1\MSSQL\DATA\master.mdf’. Diagnose and correct the operating system error, and retry the operation.
2017-03-19 14:02:52.59 spid5s      Recovery is writing a checkpoint in database ‘master’ (1). This is an informational message only. No user action is required.

2017-03-19 14:02:52.82 spid5s      Starting up database ‘mssqlsystemresource’.
2017-03-19 14:02:53.13 spid7s      Starting up database ‘model’.

2017-03-19 14:02:53.18 spid7s      Error: 17207, Severity: 16, State: 1.
2017-03-19 14:02:53.18 spid7s      FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file ‘E:\Program Files\Microsoft SQL Server\v2005\SQLExpress\MSSQL.1\MSSQL\DATA\model.mdf’. Diagnose and correct the operating system error, and retry the operation.

2017-03-19 14:02:54.16 spid7s      Clearing tempdb database.
2017-03-19 14:02:54.99 spid7s      Error: 17207, Severity: 16, State: 1.
2017-03-19 14:02:54.99 spid7s      FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file ‘E:\Program Files\Microsoft SQL Server\v2005\SQLExpress\MSSQL.1\MSSQL\DATA\tempdb.mdf’. Diagnose and correct the operating system error, and retry the operation.
2017-03-19 14:02:55.97 spid7s      Starting up database ‘tempdb’.
2017-03-19 14:02:55.99 spid7s      Error: 17207, Severity: 16, State: 1.
2017-03-19 14:02:55.99 spid7s      FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file ‘E:\Program Files\Microsoft SQL Server\v2005\SQLExpress\MSSQL.1\MSSQL\DATA\tempdb.mdf’. Diagnose and correct the operating system error, and retry the operation.
2017-03-19 14:02:56.18 spid5s      Recovery is complete. This is an informational message only. No user action is required.

Explanation

It is likely another application is interfering with the database’s engine access to the database files.

It could be Anti-Virus, OS File System drivers and filters or diagnostic tools.

In our case it could have been the fact that we were running SysInternal’s process monitor and chosen to focus in on the folder where how SQL Server data files are kept.

 

SQL Server Install – Summary.txt

Exit Code Returned: 1260

Error Text

**********************************************************************************
Product Installation Status
Product                   : Express Database Services (V2005SQLEXPRESS)
Product Version (Previous): 1399
Product Version (Final)   :
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9Express_Hotfix_KB2463332_SQLEXPR.EXE
SQL Express Features      : SQL_Data_Files,SQL_Engine,SQL_SharedTools
Error Number              : 1260
Error Description         : Unable to install Windows Installer MSI file
———————————————————————————-

**********************************************************************************
Summary
One or more products failed to install, see above for details
Exit Code Returned: 1260

 

Remediation

Remediation – Change SQL Server Service Account to Domain Account

Review Account SQL Server is running under.  If it is running under “NT Authority\Network Services”, then change it to use to use a “Domain Account”.

Steps Outline

  1. Launch SQL Server Configuration Manager
  2. On the left panel, access the SQL Server Services Node
  3. On the right panel, review the list of services
  4. In our case, our targeted instance is v2005 SQLExpress
    • We reviewed all of the corresponding services for that instance
    • Currently, they are set to NT AUTHORITY\NETWORK SERVICES
    • We changed them to run under a least privileged domain account

ScreenShot

Sql Server Configuration Manager – List of Services

SQLServerConfigurationManager-NetworkService-20170319-0119PM (Brushed Up)

 

Sql Server Configuration Manager – Changing Service Account from Network Service to ….

changeServiceAccount_20170319_0110PM (Brushed Up)

 

Sql Server Configuration Manager – Changed Service Account from Network Service to Domain Account

SQLServerConfigurationManager-ChangedToDomainAccount-Services-20170319-0113PM (Brushed Up)

 

 

Summary

As always took the long way home.

Here are all the things we did:

  1. Whitelisted SQL Server v2005 SP4
    • Captured App GUID through Event Viewer or summary.txt ( SQL Server Log File )
  2. Experienced “Error: 17207, Severity: 16, State: 1 – FCB::RemoveAlternateStreams: Operating system error 6(The handle is invalid.) occurred while creating or opening file”
    • Stopped SysInternal’s Process Monitor
  3. Ran into Error Code 1260
    • Changed Service Account from “NT Authority\Network Services” to Domain Account
    • Ensured that the Service Account has full permissions on all folders that SQL Server has data and log files

Windows Update – Patching SQL Server 2005 Express

Background

This is the second in a series of posts in which I will try to journal some of the steps we took to address a busy hard drive.

In the previous post we spoke about how we configured svchost.exe to run on separate processes and thus we are better positioned to identify which specific services are driving I/O.

Inclusive in the services identified are Windows Management and Windows Updates.

 

Windows Update

Once we saw that Windows Update was one of the main drivers we tried to see which updates was causing the problem.

To launch the Automatic Updates we clicked on the icon on the right bottom panel.

 

Microsoft SQL Server 2005 Express Edition Service Pack 4 ( KB2463322 ) AND Microsoft SQL Server 2005 Express Edition Toolkit Service Pack 4 ( KB2463322 )

Screen Shot

Outline

The screens that we encountered are:

  1. How do you want to install updates
  2. Choose Updates to install
  3. The updates are being installed
  4. Some updates could not be installed

How do you want to install the updates

Image
Initial Screen

HowDoYouWantToInstallUpdates_20170319_0230AM

 

Post Selection Screen

HowDoYouWantToInstallUpdates_20170319_0231AM

Textual

We chose “Custom Install (Advanced )” to gain better insight as to what is being installed are have more control as to which ones we can opt out of.

Choose Updates to install

Image

ChooseUpdatesToInstall_20170318_0232AM

 

Textual

Here are the updates

  1. Microsoft SQL Server 2005 Express Edition Service Pack 4 (KB2463332)
  2. Microsoft SQL Server 2005 Express Edition Toolkit Service Pack 4 (KB2463332)

 

The updates are being installed

Image

TheUpdatesAreBeingInstalled_20170319_0235AM

 

Textual

Here are the first of two updates are being applied

 

The updates are being installed ( Post First Patch )

Image

TheUpdatesAreBeingInstalled_20170319_0237AM

 

Textual

Unfortunately, the first patch failed.

And, the second patch is now being applied.

 

Some updates could not be installed.

Image

SomeUpdatesCouldNotBeInstalled_20170319_0238AM

 

Textual

Here both updates failed.

 

TroubleShooting

The troubleshooting steps available to us are the following:

  1. Check Event Viewer
  2. Check Windows Update log files
    • c:\windows\WindowsUpdate.log

 

c:\windows\WindowsUpdate.log

Here are some sample entries from c:\windows\windowsUpdate.log

Entries – 2017-03-19 3:54

 

Follow Up

Opted Out

Opted out SP4

ScreenShot

Choose updates to install
Image

ChooseUpdatesToInstall

 

Textual

Unchecked both packages.

 

Hide Updates
Image

DontNotofyNeAboutTheseUpdatesAgain

 

Textual

Here we are opting out of this release not just for now, but for always.

 

Summary

A number of years ago all the cool kids wore a tee shirt that read “Got tired of patch Tuesday, went Googling”.

It is not that bad for us, we will just go the manual install path.

svchost – High IO on MS Windows 2003

 

What is svchost.exe?

Wikipedia
Link

svchost.exe (Service Host, or SvcHost) is a system process that hosts multiple Windows services in the Windows NT family of operating systems.
Svchost is essential in the implementation of so-called shared service processes, where a number of services can share a process in order to reduce resource consumption

 

Issue

Hard-drive stays busy.

Indicator

Task Manager

Image

Explanation

  1. Top IO Usage
    • svchost.exe
      • Process ID is 920
      • User name is System

SysInternals

Explanation

  1. Top IO Usage
    • svchost.exe
      • Process ID is 920
      • User name is System

TroubleShooting

Process Management

Tasklist

List all services running under svchost.exe

Tasklist – List all processes running under svchost.exe

Script

tasklist /svc /fi "imagename eq svchost.exe"

Output

Explanation
  1. We are focused on PID = 920

Process Explorer

Tasklist – Dig deeper into process svchost.exe = 920

Services

Own Process

In a nice Server Fault QA post, Peter Mortensen suggested that one could separate out the services into their own process and thus gain clearer understanding of each service resource uptake.

To do one will have to change the service configuration.

Here is the specific QA:

How to find memory usage of individual Windows services?
Link

Run as distinct Process

Syntax

SC Config Servicename Type= own

Run as shared Process

Syntax

SC Config Servicename Type= share;

Run as distinct Process

Sample Code

rem  1. "Automatic Updates"
SC Config wuauserv Type= own

rem  2. "COM+ Event System"
SC Config EventSystem Type= own

rem  3. "Computer Browser"
SC Config Browser Type= own

rem  4. "Cryptographic Services"
SC Config CryptSvc Type= own

rem  5. "Distributed Link Tracking"
SC Config TrkWks Type= own

rem  6. "Help and Support"
SC Config helpsvc Type= own

rem  7. "Logical Disk Manager"
SC Config dmserver Type= own

rem  8. "Network Connections"
SC Config Netman Type= own

rem  9. "Network Location Awareness"
SC Config NLA Type= own

rem 10. "Remote Access Connection Manager"
SC Config RasMan Type= own

rem 11. "Secondary Logon"
SC Config seclogon Type= own

rem 12. "Server"
SC Config lanmanserver Type= own

rem 13. "Shell Hardware Detection"
SC Config ShellHWDetection Type= own

rem 14. "System Event Notification"
SC Config SENS Type= own

rem 15. "System Restore Service"
SC Config srservice Type= own

rem 16. "Task Scheduler"
SC Config Schedule Type= own

rem 17. "Telephony"
SC Config TapiSrv Type= own

rem 18. "Terminal Services"
SC Config TermService Type= own

rem 19. "Themes"
SC Config Themes Type= own

rem 20. "Windows Audio"
SC Config AudioSrv Type= own

rem 21. "Windows Firewall/Internet Connection Sharing (ICS)"
SC Config SharedAccess Type= own

rem 22. "Windows Management Instrumentation"
SC Config winmgmt Type= own

rem 23. "Wireless Configuration"
SC Config WZCSVC Type= own

rem 24. "Workstation"
SC Config lanmanworkstation Type= own

rem End.

 

Remediation

Once we ran the code to start all the aforementioned svchost.exe services in their own process space, restarted the machine.

SysInternals – Process Explorer

Took the SysInternal’s Process Explorer, arranged based on IO, and noticed that WMI is the culprit.

Images

svchost.exe – Services

Here are the services that are using our cited svchost.exe process.

Services

Took to Control Panel, services applet to stop that service and see if it things slow down.

Dependent Services

Reviewed Dependent Services

And, I really will rather than not stop the local system firewall service.  And, start to wonder why so busy anyways.

But, all that will wait another post as it is Saturday and I have errands to run.

Dedicated

Dedicated to Peter as in Mortensen.

 

References

  1. How to find memory usage of individual Windows services?
    Link
  2. How do I discover which process is making my hard drive go crazy? (need disk io equivalent of task manager’s cpu % column)
    Link
  3. YongRhee ( MSFT )
    • How to troubleshoot Service Host (svchost.exe) related problems?
      Link