Category Archives: Random errors and weird experiences

A collection of errors and experiences that seem to be out of the “norm” for the everyday SQL Server administrator.

Another “Wait on the Database Engine Recovery handle failed” – SQL Server 2000 to 2008R2 upgrade

  By | Random errors and weird experiences
1 Comment

I was given the task of upgrading our SQL Server 2000 instance to SQL Server 2008R2. I ran through the following steps to make sure the upgrade would be a success:

  • Took a snapshot of the VM
  • Upgraded to SQL Server 2000 service pack 4
  • Installed the updated Windows Installer 4.5
  • Launched the “System Configuration Checker” (found no issues)
  • Ran the “Upgrade Advisor” and addressed all issues

To this point, I figure I’m golden. Running the upgrade, it finishes with the always daunting, ever elusive error:

“Wait on the Database Engine Recovery handle failed. Check the SQL Server error log for potential causes.”

This occurred during the “SqlEngineDBStartConfigAction_upgrade_configc_Cpu32” portion of the upgrade. The error log showed:

Googling took me to a number of sites but this one was the most useful: http://social.msdn.microsoft.com/Forums/en/sqlsetupandupgrade/thread/02e221e5-9a4b-444c-a136-ef3fdbec84e9

Not sure why the answer was marked the way it was, but I had to scroll to the bottom of the post to this reply:

“This issue has been resolved.  It still goes back to the complex password issue.  I had to turn off complex password policy on my domain, run the install repair, and it worked successfully.  It does not help to just make the SA password complex.  There are many blogs out there that point to an account being used in the install script that does not meet the complex password.  I could not find that account in my case, so I had to turn off my policy temporarily.”

I had changed the SA password a few times, made sure it was correct by logging in and actually using that account to perform the service pack 4 installation, and was confident that it met the complexity policy.  According to the above, it’s not the problematic user. Once I disabled the password complexity policy the engine started up without issue.

Some tools that helped me:

To change the ‘sa’ password in SQL Server 2000 (run as a member of ‘sysadmin’, no need for the old password)

EXEC sp_password @new='AG00dlongP@ssword!', @login='sa'

Running the SQL Server .exe from the command line with the -M option gave more information. Change directory to the location of the .exe and issue the following command (see below). It will output messages to a file in that same directory called ‘error.txt’ (useful for debugging as DOS buffer is inadequate):

sqlserver.exe -M > error.txt

PASS Summit 2011 first timer, three days of awesomeness

  By | Random errors and weird experiences
Leave a comment

My first time attending the Professional Association of SQL Server Summit was amazing! Three days of great people, useful information and free stuff! I had to check an extra bag at the airport to bring home my loot.

I arrived at the conference on Tuesday night, too late to attend the ‘first timer’ event. I registered, then headed towards the dimly lit exhibit hall, where a large crowd of participants were gathered. The complexity of the convention center, coupled with the overwhelming feeling that I was going to miss something had me reeling, but I quickly recovered when I ran into a fellow Sacramento SQL Server User Group board member. He was a second year veteran and seeing my anxiety, began leading me through the crowd of fellow SQL Server-ites, showing me where to get food, grab a drink and talk to the pros. The big screens in the exhibit hall showed the magnified images of the “Jeopardy” contestants, SQL Server MVPs who were playing a mock game show using SQL Server, popular culture and everything in between as the questions to the given answers. We eventually found other members of our group; at this point my anxiety had been replaced with sheer excitement.

The night was young and there were some after hours events to attend. Our lil band dispersed, each heading to pursue his own agenda but with promises of regrouping the next day. My itinerary lead me to the SQLServerCentral.com casino night, hosted by the popular website and RedGate software. As I contribute to and frequent that site regularly, I was looking forward to putting faces to the various names that I see everyday. My first encounter was with “the man himself”, Steve Jones. We shook hands and exchanged some pleasantries and I sauntered in to join the festivities. Jeff Moden was accepting his “Exceptional DBA” award (truly earned) and after some “gambling” and the raffle, Steve and I were able to talk about life, professions and the Seattle underground (he told me about the underground tour). Getting to speak with someone I admire really made my night; the bar was set high for the rest of the week.

Wednesday, October 12th: Day 1

10:15 – Thomas Grohser presented on SQL Server I/O basics. I took pages of notes, reinforcing some of the things I already knew and absorbing the points that I was fuzzy on. Forgot to fill out an eval =(

13:30 – Victor Isakov filled us with information on indexing. It was meant to span two sessions, but after the first hour and a half, I opted to find another topic.

15:00 – Andy Warren gave tips on how to build a Professional Development Plan. This one really hit home as I’m always looking for the “next adventure”; here I was given practical advice on how to game plan to get to my next level.

16:45 – Aaron Betrand ended the day with an outline of new features in SQL Server 2012 (“Denali”).

Thursday, October 13th: Day 2

10:15 – Klaus Aschenbrenner gave a very practical session on troubleshooting performance problems with SQL Server 2008. He outlined an easy to follow recipe which I really appreciated learning.

Lunch – Women in Technology – The WIT group fed us and treated us to a discussion on various topics. Awesome time and great food.

13:30 – Victor Isakov again, presenting on Trace Flags that every SQL Server DBA should know. The information was useful, but I wish I would have gone to the Thomas LaRock presentation. I’ll make sure to watch that one on the DVD

15:00 – Tim Ford showed his “Periodic Table of Dynamic Management Objects”, truly entertaining for a science nerd like myself. I’m hoping I can find the poster online.

17:00 – Maciej Pilecki gave an in depth presentation on execution plans. Very, very useful information.

Friday, October 14th: Day 3

10:15 – Brad McGehee taught us the inner workings of the Transaction Log, giving us tons of useful best practice and tips to managing one of the most important aspects of SQL Server.

13:00 – Il-Sung Lee outlined some security changes in SQL Server 2012, as well as reviewing the changes that SQL Server 2008 gave us. My organization is heavy into security so I was glad to attend.

14:30 – Brian Mitchell and Dandy Weyn presented on SQL Server Parallel Data Warehouse. I can now go back and argue with my Oracle friend on the virtues of PDW vs Exadata.

16:15 – Thomas LaRock showed SQL Server memory management.  I unfortunately left early to catch my plane

 

All in all an amazing experience.  I was able to meet some of the big names in the SQL Server community: Andy Warren, Jorge Segarra, Steve Jones, Brian Davis and learn tons about my chosen profession.  Everyone was super friendly, sharing hand shakes and experiences as well as a business card now and then.  We ate, drank and built stronger relationships.  I’m truly looking forward to next year’s event.

SQL Server upgrade to 2008R2 and “Wait on the Database Engine Recovery handle failed”

  By | Random errors and weird experiences
Leave a comment

Recently, I was tasked with doing an in place upgrade of SQL Server from 2005 to 2008R2.  My environment was virtualized so I had the luxury of snapshot’ing the server before I performed the upgrade.  This was fortunate, as it took me three tries to be successful.

My first attempt failed with the following, non-descript message:

“Wait on the Database Engine Recovery handle failed.  Check the SQL Server error log for potential causes.”

Put that phrase into Google and you’ll get lots of hits.  One of ’em mentioned that there could be problems when renaming the ‘sa’ account, for security purposes:

http://support.microsoft.com/kb/968829

I was on to something, as I had disabled the ‘sa’ login from connecting remotely.  I reverted to the snapshot, enabled the ‘sa’ account and ran the upgrade again.  FAIL!  With the same error message.

My next thought was possibly a problem with the account that was running the SQL Server service, reference this forum post:

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-setup/9997/SQL-Server-2008-Installation-problem

Red herring, as I always use the SQL Server Configuration Manager to set the owner of my services.  But, I set the accounts back to LOCAL SYSTEM or LOCAL SERVICE and ran the upgrade again.  FAIL!  With the same error message.

All this time I have been looking in the error logs for the smoking gun, but only seeing these:

1&2. Event ID 17182: TDSSNIClient initialization failed with error 0x7e, status code 0x3a.

3. Event ID 17826: Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

4. Event ID 17120: SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems

These related to the deprecated protocol VIA, which I knew should not be enabled.  I passed over them each time thinking that the messages were in response to the disabling of the service.

After reverting to the snapshot, I went into SQL Server Configuration Manager.  The VIA protocol was enabled…

It appears that the previous two upgrades were successful, as on my third attempt when I received the “Wait on the Database Engine Recovery…” error and the failed upgrade message, I went into SQL Server Configuration Manager and tried to start the service manually.  It failed.  I then disabled the VIA protocol and the SQL Server service started up without issue.  I queried the database engine to see it’s version:

select @@version

and it returned

Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 

Not wanting to take any chances, I reverted to the snapshot,  disabled the VIA protocol, enabled the ‘sa’ account, left the service owners as my domain accounts and re-ran the upgrade.  It completed successfully.

Create a linked server to an Oracle database (and not lock up the instance)

  By | Random errors and weird experiences, SQL Server w/o the GUI, Working with Oracle
6 Comments

I’ve created numerous linked servers to Oracle from SQL Server and what should be a straightforward process using SSMS has caused me some headaches.  For whatever reason, when using the GUI, I’ve actually locked up the entire SQL Server instance and had to restart the service when trying to create a linked server to Oracle.  This is not good when working in production. The following will outline the do’s and don’ts for this process.

To begin: download, install and configure the Oracle client to communicate with your Oracle database.  This link can assist you with that process.

Once the Oracle client is installed, here’s the steps that can cause potential problems (skip to code section for the correct solution):

Using SQL Server 2005 Management Studio, I expanded ‘Server Objects‘, right clicked ‘Linked Servers‘ and chose ‘New Linked Server‘.  I entered a name in the ‘Linked Server‘ text box then went to ‘Provider‘ (after choosing the ‘Other Data Source‘ radio button).  The drop down showed me a list of the drivers installed; to connect to Oracle we have two choices: the Microsoft driver, called ‘Microsoft OLE DB Provider for Oracle‘ and the Oracle driver, ‘Oracle Provider for OLE DB‘.  Here’s where I ran into problems: if I chose the Oracle driver, about half the time the drop down would freeze and eventually the instance would become unresponsive.  I’d have to restart the SQL Server service.

Here’s the code section, showing how to create a linked server using T-SQL:

-- Create the linked server
EXEC master.dbo.sp_addlinkedserver
@server = N'LinkToOracle', -- name of the Linked Server
@srvproduct=N'Oracle',
@provider=N'MSDAORA', -- Microsoft driver
@datasrc=N'myOracleDatabase' -- TNS name of the Oracle database
go
 
-- Add the user to connect to the Oracle database
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'LinkToOracle', -- name of the Linked Server
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'myOracleUser', -- Oracle database user
@rmtpassword='########' -- Enter the correct password here
go

The above code will create a linked server called ‘LinkToOracle’ using the Microsoft OLE DB driver, then using an Oracle database user to authenticate.  To use the Oracle OLE DB driver, replace the ‘@provider’ line in the top call to the sp_addlinkedserver stored procedure with:

@provider=N'OraOLEDB.Oracle'

To test connectivity, open a query window and do a select from a table that the Oracle user has access to.  The following assumes an ‘Employee’ table in the ‘HumanResources’ schema exists in the Oracle database:

SELECT count(*) FROM LinkToOracle..HumanResources.Employee

Notice the double dots between ‘LinkToOracle‘ and ‘HumanResources‘.  This denotes use of the default catalog of the linked server.

To send the query to the Oracle database for processing, which would allow you to write PL/SQL, use the OPENQUERY command:

SELECT * 
FROM OPENQUERY(LinkToOracle, 'select count(*) from HumanResources.Employees')

SQL Server service won’t start after Reporting Services and Analysis Services install

  By | Random errors and weird experiences
Leave a comment

I received an error after trying to start the SQL Server service for my 2005 default instance:

 

“WMI Provider Error [call to WMI Provider returned error code: 0x800742a2]”

 

In the event viewer, I got:

 

“initerrlog: Could not open error log file ‘D:\sqlserverdata\MSSQL.1\MSSQL\LOG\ERRORLOG’. Operating system error = 5(Access is denied.).”

 

A search of the web brought me a few different hits for solutions, one being a Microsoft KB:

http://support.microsoft.com/kb/955496

Unfortunately, this wasn’t exactly my problem, but it was similar.

 

My scenario:

SQL Server 2005 Enterprise edition install (database engine only), as the default instance.  I used Windows domain accounts as the various owners of the services.

Few months later, SQL Server 2008 R2 install (database engine only) as a named instance.  I used the same Windows domain accounts as owners of the services for the 2005 install (bad idea apparently).

Another few months later, I needed Reporting and Analysis services.  Installed those two options into the 2008 R2 named instance.

Analysis and solution:

When I attempted to start my 2005 service, I received the above errors.  Looking at the ACL for the default instance folders, it showed that the domain account I had used to own the SQL Server and SQL Server agent services were no longer in the list.  There were two “orphaned” (for lack of a better term) SIDs in the list.  So it appears that the second error was correct; I simply added my domain account to the top level folder of the SQL Server data folder (for me it was d:\MSSQL.1), those permissions were propogated down the tree and I was able to start the service.

See http://msdn.microsoft.com/en-us/library/ms143547(v=SQL.90).aspx for information concerning the default directories.