VM on VM: Enable Hyper-V role on Windows Server 2012 inside VMWare

If you ever tried to install Windows Server 2012/R2 on VMWare Workstation, and enable the Hyper-V role, you would have probably encountered the following error:

Hyper-V cannot be installed: A hypervisor is already running

The trick to allowing this Hyper-V on VMware is in two settings:

Continue reading

Connection Error 233 : No process at the other end of the pipe

Just had an interesting error the other day.

Got this error when trying to connect one of our servers that has just been patched:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

Typically, to troubleshoot this, you go to SQL Server Configuration Manager (SSCM) and:
1. ensure Shared Memory protocol is enabled
2. ensure Named Pipes protocol is enabled
3. ensure TCP/IP is enabled, and s ahead of the Named Pipes in the settings

Check out Pinal Dave’s excellent post on this.

All of these are set in our server.

Next up, I checked the log files. Logs are typically located in a folder similar to this (note you will need to change the folder MSSQL.1 with the appropriate instance name’s folder name):
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG

When I checked the logs however, I got yet another interesting error:

Could not connect because the maximum number of ‘1’ user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: ]

It’s an interesting error because:
1. We just restarted the server and nobody is able to access it yet
2. DAC is disabled

Continue reading

Troubleshooting Replication Error: A required privilege is not held by the client

Troubleshooting Replication Error: A required privilege is not held by the client

If you ever you encounter this replication issue, the resolution might be fairly simple, however it will require that you toggle your SQL Service accounts.

Have a look at this KB:

The KB suggests the service account might have changed using a tool other than the Configuration Manager (technically a no-no because the SSCM, in addition to changing the service accounts, also performs updates to associated settings like windows registry entries). Try toggling the service accounts in Configuration Manager (for ex, from Administrator, to something Local, to administrator again) and that should flush the old credentials.

Continue reading

Invalid Credentials Error During SQL Server 2008/R2 Install

In case you get the following errors:

The credentials you provided for the SQL Server Agent service are invalid. To continue, provide a valid account and password for the SQL Server Agent service.

The specified credentials for the SQL Server service are not valid. To continue, provide a valid account and password for the SQL Server service.

Here’s the simple solution:

Troubleshooting a “Doomed” SQL Server Transaction

Recently we encountered an unusual error message:

System.Exception: sprocname_Exception: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

It seems we’ve hit a corner case. This apparently happens when you have a TRY/CATCH exception handling mixed with old style exception handling (see Alexander Kuznetsov’s article), which may lead to an “uncommittable” transaction.

What’s an “uncommittable” transaction? In our case, we had a stored procedure that inserts records to a table that has an insert trigger. By default in the stored proc, XACT_ABORT if OFF, but in the trigger we turned it on. If an error is thrown in the trigger and caught by the CATCH block, this transaction is technically uncommittable hence we get the error.

So to fix, either:
– Use Remus Rusanu’s template for error handling in stored procedures

We went with Remus Rusanu‘s template. Problem fixed. Thanks Remus!

Handling Division By Zero Scenarios in T-SQL

Sometimes it is inevitable to encounter scenarios that will give division by zero errors

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

DECLARE @dividend INT
DECLARE @divisor INT

SET @dividend = 1
SET @divisor = 0

SELECT @dividend/@divisor

Msg 8134, Level 16, State 1, Line 7
Divide by zero error encountered.


What you can do is you can code around it, so your users and your app do not get this error.
Continue reading

A More Effective Selective Index Rebuild/Reorganize Strategy

This is a follow up post to : Why SQL Fragmentation Remains High

As mentioned in this previous post, our dilemma was we were rebuilding every index on a nightly basis, and :

  1. the process proves to be very resource intensive
  2. we don’t see the benefit. The fragmentation for some of the indexes remain high.

After some digging, this is what we found:

Rebuild the index only if there will be at least 1000 pages affected. If < 1000 pages will be affected, fragmentation will not really be affected and potentially would remain high.

This is the script that we now use to selectively identify which indexes to rebuild, which indexes to reorganize, and which indexes to leave alone. This script uses the following criteria in determining which action to take:

  • current fragmentation %
  • number of pages used by the index

The current logic is:

  • reorganize index : if fragmentation is > 10 % but < 30% and number of pages > 1000
  • rebuild index : if fragmentation is > 30 and number of pages > 1000

Continue reading