Recently I had worked on a mirroring issue. While initializing mirroring, an error message as shown in the below screenshot is encountered.

Here’s how this issue was fixed

• Check if the recovery model of the database on the Prinicipal Server was set to FULL. <– Yes, it was.
• Take the full database backup of the database on the Prinicpal server
• Take the transaction log backup of the database on the Principal Server
• Restore the full database backup on the mirrored instance WITH NORECOVERY
• Restore the transaction log backup on the mirrored instance WITH NORECOVERY

Configure Mirroring again.

I was trying to setup SQL Server 2008 on my lab machine running on Windows Server 2008 R2. When the setup was installing the prerequisites, the following error message popped up.

This is a very simple error message, which a DBA would come across often. However the next screen which popped up after this message was interesting.

Since Windows Server 2008 R2 has the .Net framework 3.5 already available as a feature, all we need to do is just enable that feature. There is no need to download the installer and install it separately. As the error message states, just enable it using the “Role Management Tool”. Role Management tool can be found under Server Manager –> Features –> Add features as seen in the following screenshot.

That’s all. Now the .Net framework 3.5.1 is installed and ready to use! After this the SQL Server setup completed successfully.

Yesterday on my local instance, SQL Server service failed to start. The SQL Server error log had the following entries in it.

2012-12-31 12:31:26.58 Server      Error: 17190, Severity: 16, State: 1.
2012-12-31 12:31:26.58 Server      Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893788.
2012-12-31 12:31:26.58 Server      Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
2012-12-31 12:31:26.58 spid7s      Informational: No full-text supported languages found.
2012-12-31 12:31:26.58 Server      Error: 17182, Severity: 16, State: 1.
2012-12-31 12:31:26.58 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

2012-12-31 12:31:26.58 Server      Error: 17182, Severity: 16, State: 1.
2012-12-31 12:31:26.58 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

2012-12-31 12:31:26.58 Server      Error: 17826, Severity: 18, State: 3.
2012-12-31 12:31:26.58 Server      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.
2012-12-31 12:31:26.58 Server      Error: 17120, Severity: 16, State: 1.
2012-12-31 12:31:26.58 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Before getting too much into the error details, I started to analyse what had changed since the last service/server restart. Here is what I had done the day before, to troubleshoot some other issue.

• Removed the Server from the domain and made it part of a workgroup
• After completing the desired tasks, I had made the server part of the domain again.

That’s all. The SQL Server service was configured to start using a domain account. 

When I changed the SQL Server service account from the domain account to a local (built-in) account, the service started normally.

However, if I changed the service to start using a domain account the same error message would re-appear.

The following portion of the error message caught my attention.

TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property

SQL Server service is trying to intialize SSL support but it cannot find the requisite information. Like  I mentioned earlier, the only thing that had changed on this instance was that the server was re-added to the domain. Did the permissions of the SQL Server service account have changed? No. I verified that the permissions were intact. Did something go wrong with the local profile of the Service account? Here is what I saw under the User Profiles (Computer Properties –> Advanced –> User Profiles)

The SQL Server service account had *two* profiles on this computer. One had the status of “Backup” whereas the other one had the status of “Temporary”. This was unusual because for any user who logs on to a computer, one local profile is created. The profile related files are created under C:\Users directory (on Windows 2003 server and below it is C:\Documents and Settings). In this case there was only one folder created but under User Profiles it was showing two. Since this seems to be an issue, I deleted the profile which had the status of “Backup”. After this In logged onto the system using the SQL Server service account to ensure that the there are no visible errors during the profile creation.

After this the SQL Server service started normally! Perfect way to end the year 2012 and I am welcoming the year 2013 with this blog post!

I am starting off this week’s Reading List with an article related to Hotfix Service Model. This article has good information on how Microsoft SQL Server team uses the Incremental Model to deliver hotfixes to SQL Server.

A new hotfix was recently released to fix the issue of series of messages while restarting SQL Server 2005 through SSMS. This issue happens when SQL Server 2005 and Biz Talk Server 2006 are installed on the same server. Read more about this here.

Next is an interesting article by Kalen Delaney (b | t) on The Pros and Cons of Parameter Sniffing.

Are you using Database Mirroring and the transaction log is growing huge during Index maintenance? If yes, Kimberly L. Tripp (b | t) and Paul Randal (b | t) discuss about a different approach to index maintenance on mirrored database in this article.

That’s all for this week. Happy learning!

Related posts:

1. Reading list for the week – 17/10/11
2. Reading list for the week – 03/10/11
3. Reading list for the week – 10/10/11

In this week’s edition, I have got quite a few good links lined up.

The big new first. SQL Server code-named “Denali” gets an Official Name. In the recently concluded PASS Summit 2011 it was announced that the next version of SQL Server will be called SQL Server 2012. Read more about this in this article.

Another big announcement that was made last week is that Microsoft SQL Server ODBC Driver  for Linux will be available along with SQL Server 2012! This is for sure a big step in the right direction.

We know that the Analysis Service cannot be added as a cluster instance. Amit Banerjee (b | t) explains how to add an Analysis Service as a failover cluster instance using the command line setup. This article is a very interesting read.

On clicking Fragmentation tab under the properties of an index in SSMS, the response time used to be very slow. This was because SSMS used to check the fragmentation of all the objects referenced by sys.dm_db_index_physical_stats DMV instead of the selected index. This hotfix resolves that bug.

Tibor Karaszi (b | t) in his article Who owns your jobs talks about the relationship between Active Directory users/groups and SQL Server Agent Job ownership.

Happy learning!

Related posts:

1. Reading list for the week – 03/10/11
2. Reading list for the week – 10/10/11
3. Reading list for the week – 19/09/11

Here are the top items in my Reading List for this week.

1. Itzik Ben-Gan (b | t) in his post Denali T-SQL at a Glance – New and Enhanced Functions talks about the new and improved functions in SQL Server Denali.
2. If you read SQL Server MVP Deep Dives, you would be thrilled to know that SQL Server MVP Deep Dives – Volume 2 is now published!. Read more about on Greg Low’s blog post.
3. Michael Otey (b) presents his view on why the PC is not going away yet in his article Windows 8 and Windows Server 8 Dispel the Myth of the Post-PC Era.
4. A simple, yet informative article by Linchi Shea (b) explains about Multi-victim deadlocks
5. On the other big news SQL Server 2008 SP3 is released. Read more about it here.

Happy learning!

Related posts:

1. Reading list for the week – 03/10/11
2. Reading list for the week – 26/09/11
3. Reading list for the week – 19/09/11

In this edition of Reading list for the week, I am presenting you with some of the Microsoft KB articles which were updated recently.

• How to use Kerberos authentication in SQL Server – In this article we learn how Kerberos authentication works and how we can configure SQL Server to use it.
• SQL Server databases can be configured to grow and shrink automatically. The questions like “whether to configure Auto Grow/Shrink?”, “what will be performance impacts” are answered in details in Considerations for the “autogrow” and “autoshrink” settings in SQL Server article. This one is a must read for every DBA.
• SQL Server Denali introduces a new feature called Product Update. This feature ensures that all the latest updates are applied to SQL Server instance at the time of installation. The Product Update feature can also make use of the Windows Update to get the latest updates for SQL Server. This article explains how to troubleshoot issues with Product Update when it is dependent on Windows Update.

Alejandro Pelc (b) posts writes about an approach to present deadlock information in a more easy to read format in his article Catching Deadlock Information in SQL Logs.

Happy learning!

Related posts:

1. Reading list for the week – 26/09/11
2. Reading list for the week – 19/09/11
3. SQL Service Pack installation through Automatic Updates

This week I have quite a few very good articles listed in the Reading list for the week. The list begins with a post from the master himself, Paul Randal (b | t). In How does DBCC CHECKDB WITH ESTIMATEONLY work? post Paul explains how DBCC CHECKDB consumes space on tempdb and how one can use WITH ESTIMATEONLY to forecast the space requirement on tempdb for CHECKDB to complete.

Paul White (b | t) explains in detail which statistics are used to compile an Execution Plan in his post How to Find the Statistics Used to Compile an Execution Plan.

We all come across bugs in SQL Server. SQL community is the major contributor in reporting these bugs. Aaron Bertrand (b |t), who is an expert in filing the bugs related to SQL Server talks about the art of filing bugs in his post Want your bug fixed? File a good bug!

Laerte Poltronieri Junior explains how to store the Event Log entries in SQL Server by using Powershell, in his post Storing Windows Event Viewer Output in a SQL Server table with PowerShell

Cumulative update package 16 for SQL Server 2008 Service Pack 1 was released recently. More details about it here.

Happy learning!

Related posts:

1. Reading list for the week – 19/09/11
2. Does tempdb runs out of space during DBCC CHECKDB?
3. DBCC CHECKDB Error | MSSQL_DBCC: Operating system error 112(There is not enough space on the disk.) encountered.

Yet another post related to SQL Server patching, eh? Yes. I feel I have a lot more lined up because in the next couple of months we will be applying Service Packs on close to 200 SQL Server instances.

Last Sunday, Service Pack 4 was supposed to be applied on one of the SQL Server 2005 instance. All components of SQL Server were successfully patched except for SQL Server Reporting Services. The following error was logged in the Summary.txt file.

Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\RS9_Hotfix_KB2463332_sqlrun_rs.msp.log
Error Number : 29528
Error Description : MSP Error: 29528 The setup has encountered an unexpected error while Setting reporting service and share point exclusion path. The error is: Fatal error during installation.

The RS9_Hotfix_KB2463332_sqlrun_rs.msp.log file was 6MB in size and quickly scrolling through it did not provide much useful information. The Event Logs also did not have much information apart from the message indicating the Service Pack installation failure. Instead of scrolling through the log file which had 61037 lines (yes, you read it right), I took a step back and analyzed the prerequisites.

Since it is SQL Server 2005, the Reporting Service depends on Internet Information Services (IIS). The Reporting Service Configuration Manager makes use of IIS to create the Virtual Directories along with the database hosted in SQL Server, to render the reports. My plan was to check IIS for any issues and then move on to look at the Reporting Services configuration.

Hence I opened up the Service Manager (services.msc). Surprise! Here is what I saw.

The IIS Admin Service was disabled. Obviously the Service Pack installer was unable to fetch the settings on IIS. Immediately, I enabled the IIS Admin Service and launched the Service Pack installer again and this is what I got.

Nice! Mission accomplished.

Why was the IIS service disabled? It was a test box. Do I need to say anything more?

Related posts:

1. Mising MSI or MSP files while installing SQL Server service packs
2. How to Resolve missing MSI or MSP packages during SQL Server Service Packs, Hotfix or Cumulative Updates
3. SQL Server Reporting Server service does not start | Object reference not set to an instance of an object

I am a great fan of John Sansom’s (b | t) “Something for the Weekend” series. Every Friday John posts some of the very interesting articles related to SQL Server that were posted during a particular week. One can get a weekly digest of all news related to SQL Server and other interesting topics all in one place. That is the reason why his posts are the best way of sharing knowledge and they are quite famous as well.

Reading List

Each one of us spend time reading articles everyday. Even I do. Usually I share the interesting ones on Twitter. While I was reading John’s recent post yesterday, I thought even I should share the articles that I read, on my blog. This acts as a good reference for me to revisit sometime later and you may also find them useful.

Inspired by John’s idea, starting today I am starting a new weekly series called “Reading  list for the week”. Every Monday I will post a Reading List which I plan to read during the week. Here I go with this week’s links.

• An easy approach to open firewall ports in Windows Server 2008 – How do I open the firewall port for SQL Server on Windows Server 2008?
• Troubleshooting connectivity issues in SQL Server - Potential causes of the “SQL Server does not exist or access denied” error message
• Some of the reasons for the Transaction Log becoming full - A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
• Transfer logins between instances - How to transfer logins and passwords between instances of SQL Server

Happy learning!

Related posts:

1. Popular posts of the week
2. The NOLOCK hint and the subsequent blockings
3. View does not list all the columns in the table