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

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… Continue reading

Getting information from Oracle into SQL Server using SSIS

I needed to populate a SQL Server database with information from an Oracle database, but found when I created a linked server and ran the INSERT statement manually, I was getting errors regarding truncation and datatypes.  I had already run this successfully using the IMPORT data wizard (and had my application owner confirm the data) so was a bit confused.  SSMS wasn’t reporting which row or column was problematic.  Overall, my goal was to run a nightly job to run… Continue reading

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

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… Continue reading

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

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… Continue reading

Get information about your databases

Let’s begin with the catalog view ‘sys.databases’. It has lots of useful information: — find the name, owner, create date and recovery mode of all user databases SELECT name, SUSER_SNAME(owner_sid), — SUSER_SNAME() returns the login when passed a ‘sid’ create_date, recovery_model_desc FROM sys.databases WHERE database_id > 4;– find the name, owner, create date and recovery mode of all user databases SELECT name, SUSER_SNAME(owner_sid), — SUSER_SNAME() returns the login when passed a ‘sid’ create_date, recovery_model_desc FROM sys.databases WHERE database_id > 4;… Continue reading

Working with SQL Server logins

SQL Server ‘logins’ are what’s known as a server level ‘principal’. From BOL, “Principals are entities that can request SQL Server resources.” There are two types of logins, one where the credentials are stored in SQL Server and one where the credentials come from Windows, either locally or through Active Directory. So let’s begin with a list of our logins. Not surprising, the catalog view ‘sys.server_principals’ works nicely: — List logins on the instance SELECT name, sid, type_desc, create_date, default_database_name… Continue reading