I just have to blog this little gem. Every now and then I would be running a PowerShell script, I’d get an error but I wouldn’t get anything useful from the default error message besides the line number.
Read the rest of this entry »

I was busily preparing my new VMs all night (shiny new VMs!!). Planning to get 2 Windows 2008 R2 Enterprise boxes out as my new playgrounds, one as a Domain Controller, another one as my new Denali CTP3 box.

When I was starting to install Denali, not long after launching the install, I got this weird issue just as I was clicking next on the Product Key window:

SQL Server Setup has encountered the following error:
The handle is invalid. (Exception from HRESULT: 0×0070006 (E_HANDLE))

At first I was thinking maybe my ISO was corrupt, or my VM was wonky! Good thing I stumbled across this old connect post about sql2k8 rtm (dev x64) failed install. Quoting the last answer:

This is a MSDN version so the product key is pidded but it is not enabled as read-only. So, when you’re presented with the product key dialog, you can muck it up.

To reproduce, simply select the “Specify a free edition” radio button without changing the shown PID, then reselect the “Enter the product key” radio button (again without changing the PID) and continue with the installation.

So to recap, the solution is to select the “Enter the Product Key” choice (yup, even if it’s blank). That’s all it took, worked like magic.

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:
http://support.microsoft.com/kb/911305/en-us

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.

Quote from KB:

Good luck!

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:
http://www.jadota.com/2009/01/the-credentials-you-provided-during-sql-server-2008-install-are-invalid/

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

DECLARE @dividend INT
DECLARE @divisor INT
SET @dividend = 1
SET @divisor = 0
SELECT @dividend/@divisor
/* 
Error:
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.
Read the rest of this entry »

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

Read the rest of this entry »

SQL Server 2005 Error:
“A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) ”

Things to check:
1. Make sure your database engine is configured to accept remote connections
• Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
• Click on Surface Area Configuration for Services and Connections
• Select the instance that is having a problem > Database Engine > Remote Connections
• Enable local and remote connections
• Restart instance

2. Check the SQL Server service account
• If you are not using a domain account as a service account (for example if you are using NETWORK SERVICE), you may want to switch this first before proceeding

3. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings in your ASweb P.NET application
• Usually the format needed to specify the database server is machinenameinstancename
• Check your connection string as well

<connectionStrings>

<add name=”SampleConnectionString” connectionString=”Data Source=machinenameinstancename;Initial Catalog=AdventureWorks;Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=60;Connect Timeout=30″ providerName=”System.Data.SqlClient”/>

</connectionStrings>

4.You may need to create an exception on the firewall for the SQL Server instance and port you are using
• Start > Run > Firewall.cpl
• Click on exceptions tab
• Add the sqlservr.exe (typically located in C:Program Files (x86)Microsoft SQL ServerMSSQL.xMSSQLBinn), and port (default is 1433)
• Check your connection string as well

5. If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings

6. Check SQLBrowser; check that it is running. You may also need to create an exception in your firewall for SQLBrowser.

7. Check that you have connectivity to the SQL Server. Note what you are using to connect: machine name, domain name or IP address? Use this when checking connectivity. For example if you are using myserver
• Start > Run > cmd
•netstat -ano| findstr 1433
•telnet myserver 1433
•ping -a myserver

Check what ports are IP addresses are being returned.

Alternative:
If you still can’t get any connection, you may want to create a SQL account on the server, a corresponding SQL user on the database in question, and just use this username/password combo in your web application.

Problem

Actually the error is (note spelling mistake on fashion):

"Connection to target machine could not be made in a timely fasion"

Scenario

One of our DBAs was testing the SQL cluster failover. After he failed over from Node 1 to Node 2, he can no longer bring up SQL Server Configuration Manager (SSCM). Whenever he tried, he will wait for about 30-40 seconds before he gets an error message : Connection to target machine could not be made in a timely fasion

Resolution

This could have been caused by a whole host of things.

In our case, all we had to do was to restart WMI service:

1. Start > Run > type services.msc and press Enter
2. Scroll down to Windows Management Instrumentation, right click, restart