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

Hadith 34: Whoever Sees An Evil

Background

You realize that the pastor is really in the Spirit and has a spirit of boldness about him, when he is preaching the word and you bring out your iPhone and start drifting in and out.

He was in Isaiah 46 and landed in Matthews.

I went along and landed in John and Hadith 34, as well.

 

Whoever Sees An Evil

Link

Another one of my favourite hadiths because it teaches us to get up and do something:

On the authority of Abu Sa’eed al-Khudree (may Allah be pleased with him) who said: I heard the Messenger of Allah (peace and blessings of Allah be upon him) say, “Whosoever of you sees an evil, let him change it with his hand; and if he is not able to do so, then [let him change it] with his tongue; and if he is not able to do so, then with his heart — and that is the weakest of faith.” [Muslim]

This is an important hadith because it shows that Muslims should not be innocent bystanders – we are obliged to do something to stop wrongdoing. It also outlines to us the order in which we must do something.

If you see something wrong, the first point of call is to physically stop it (“…change it with his hand…”). For example, the Prophet (peace be upon him) said: “The parable of myself and the people is that of a man who lit a fire. When it illuminated its surroundings, the moths and other creatures which are attracted to light began to fall into it. He began to pull them out of the fire, but they overwhelmed him and continued to fall into it. I am the one pulling you away from the fire, but you keep going headlong into it.” [Sahîh al-Bukhârî (6002)].

It is not enough for us to keep ourselves away from wrongdoing, we must help others as well, and stop them from falling into wrongdoing, and sins which could lead to the Hellfire. This part of the hadith also refers to stopping people from doing wrong to others or to yourself. Nowadays, there are many people who will not raise a finger to stop anti-social behaviour – this is not the characteristic of the righteous Muslim. If we have the ability to stop a crime or offence, then we must, no matter how small the action.

If you are not physically able to change things or stop wrongdoings, then you must speak out against it, or advise against it, or advise a better way, or even call for help (“…[let him change it] with his tongue…”). We shouldn’t remain silent when we have the ability to speak up. You can warn someone against doing wrong, advise them to change their ways. Communication is not a pointless action – it can lead to bigger things. Campaigning can help but people do need to be focused on campaigns that actual achieve something, rather than those that hit brick walls.

Finally, if you are powerless to act on any of the two methods above, then you must oppose it in your heart. As Muslims, we do not accept wrongdoings nor resign ourselves to living out lives where there is no opposition to wrongdoings. We do not lie and let the wrongdoers roll over us, step over us and spread their mischief to others. Wrong is wrong and the moment you let go of this in your heart, then you lose sight of what is good and what is wrong. You stop seeing wrong as wrong and good as good – the lines become blurred and what is wrong becomes allowable. If you hate the wrongdoing in your heart, then perhaps an opportunity will come in the future, where you can speak out or physically stop the wrong. As Allah Says in the Qur’an:

“Verily, with hardship there is relief” (Qur’an 94:6)

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

SQL Server – Partial Database Restores – Benefits & Impact

Lineage

In our last couple of posts we focused our lenses on partial database restores.

Those posts are here:

  1. SQL Server – Database Restore – “Defunct State”
    here
  2. SQL Server – Database Restore – Skipping File Groups
    here

 

Current

In this post we will look a bit more at the benefits and costs of restoring just a portion of database.

 

Reviews

GUI

Database

File Groups

databaseProperties-FileGroups (cropped-up)

 

Files

databaseProperties-Files

 

 

Tables

dbo.ten95B

tableCount-Table1

 

Storage
  1. Table Name :- dbo.ten95B
  2. File Group :- PRIMARY
  3. Text filegroup :- fgLOB
  4. Data Space
    • 1370 MB
    • or 1.4 GB
  5. Row Count :- 4800

 

Query

Database

Database – Files

Let us query the sys.database_files system table to get the name, type, state, and size of the individual files that are in our current database.

SQL

select 
		  tblSDF.[file_id]
		, tblSDF.[name]
		, tblSDF.[physical_name]
		, tblSDF.[type_desc]
		, tblSDF.[state_desc]
		, tblSDF.size
--		, tblSDF.*
from   sys.database_files tblSDF

Image

Database – Show File Stats

Let us issue “DBCC ShowFileStats” to gather information about our database files.

SQL
dbcc showfilestats

Image

Table

Let us try to read data from table

 

Query Table for Data – All Columns

dbo.ten95C
SQL

select top (1000) *
from [dbo].[ten95C]

Image
filegroupThatCanNotBeAccessed

 

Textual

Large object (LOB) data for table “dbo.ten95C” resides on an offline filegroup (“fgLOB”) that cannot be accessed

 

Query Table for Data – Specific Columns

On the other hand, we are still able to query for specific columns.

When we restrict our column set to those filegroups that were included in our restore, things are good.

dbo.ten95C
SQL

select top (10)
 
   tblT.[ten95C_id]
 , tblT.[ten95C_view_cnt]
 , tblT.[ten95C_view_last]

from [dbo].[ten95C] tblT

Image

Summary

From a cursory look, Microsoft’s design and implementation of Partial database restore is well thought out and solid.

Its preserves all the niceties of having access to the original database design ( database groups and files), metadata for all concerns.

And, rightfully sacrifices storage of the actual data and thus we gain the benefits of not having to provide and maintain storage for uneeded data.

SQL Server – Database Restore – Skipping File Groups

Background

In our very last post we touched on stumbling on an interesting error message courtesy of attempting to do a partial database restore.

The post is here.

 

Database Restore

Full Database Restore

Sample


RESTORE DATABASE [DBLABIMWithInMemoryFiles_R]
 FROM DISK  = 'Z:\Microsoft\SQLServer\Backup\\DBLABIMWithInMemoryFiles.bak'
 WITH  REPLACE,  STATS = 1, 
	  MOVE 'DBLABIM' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_Base_01.mdf'
	 , MOVE 'DBLABIM_Data_01' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_Data_01.ndf'
	 , MOVE 'DBLABIM_INDEX_01' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_INDEX_01.ndf'
	 , MOVE 'DBLABIM_log' TO 'Z:\Microsoft\SQLServer\Logfiles\DBLABIMWithInMemoryFiles_R_log.ldf'
	 , MOVE 'DBLABIM_FILESTREAM_01' TO 'Z:\Microsoft\SQLServer\FileStream\DBLABIMWithInMemoryFiles_R_FILESTREAM_01'
	 , MOVE 'DBLABIM_InMemory_01' TO 'Z:\Microsoft\SQLServer\FileStream\DBLABIM_InMemory_01'

Partial Database Restore

Sample


RESTORE DATABASE [DBLABIMWithInMemoryFiles_R]
	  filegroup  = 'PRIMARY'
	 , filegroup  = 'DATA'
	 , filegroup  = 'FG_FS_01'
	 , filegroup  = 'FG_MOD_01'

 FROM DISK  = 'Z:\Microsoft\SQLServer\Backup\\DBLABIMWithInMemoryFiles.bak'
 WITH  REPLACE,  STATS = 1, 
	  MOVE 'DBLABIM' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_Base_01.mdf'
	 , MOVE 'DBLABIM_Data_01' TO 'Z:\Microsoft\SQLServer\Datafiles\DBLABIMWithInMemoryFiles_R_Data_01.ndf'
	 , MOVE 'DBLABIM_log' TO 'Z:\Microsoft\SQLServer\Logfiles\DBLABIMWithInMemoryFiles_R_log.ldf'
	 , MOVE 'DBLABIM_FILESTREAM_01' TO 'Z:\Microsoft\SQLServer\FileStream\DBLABIMWithInMemoryFiles_R_FILESTREAM_01'
	 , MOVE 'DBLABIM_InMemory_01' TO 'Z:\Microsoft\SQLServer\FileStream\DBLABIM_InMemory_01'
	 , PARTIAL 

 

Reference

  1. Developer Network
    • Database Features > In-Memory OLTP (In-Memory Optimization)  > Backing Up a Database with Memory-Optimized Tables > Piecemeal Restore of Databases With Memory-Optimized Tables
      Link

 

Summary

When scripting a partial database restore, please do the following:

  1. Explicitly list the file groups that you are targeting
    • Where does this clause go
      • Right underneath the name of the target database
      • And, before the with clause
  2. Add the partial clause