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 machinename\instancename
• Check your connection string as well

<connectionStrings>

<add name=”SampleConnectionString” connectionString=”Data Source=machinename\instancename;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 Server\MSSQL.x\MSSQL\Binn), 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

Problem

Agent message code 4502. View or function ’sys.dm_exec_sessions’ has more column names specified than columns defined.

Scenario

Our replication on our clustered database just started to fail. We were getting the following error:

Agent message code 4502. View or function ’sys.dm_exec_sessions’ has more column names specified than columns defined.

Ok, this is a weird message. How can sys.dm_exec_sessions have more columns that specified?

We checked the definition:

EXEC sp_helptext 'sys.dm_exec_sessions'

And this is what we got:

CREATE VIEW sys.dm_exec_sessions AS

 SELECT *

 FROM OpenRowset(TABLE SYSSESSIONS)

Ok, we can see how this can be a problem – because of the star (*). Any column change can spell disaster. However, we weren’t very sure why this would cause any problems, because this is not any regular user view. It’s a built in SQL Server DMV.

So we checked out a few things:

SELECT 

 SERVERPROPERTY('ResourceVersion'),

 SERVERPROPERTY('ProductVersion'),

 SERVERPROPERTY('ProductLevel')    -- Service Pack

It turns out the installed Service Packs are not the same in all the nodes.

Resolution

Make sure all nodes in the cluster have the same service pack. Sometimes it’s easy to miss because you have to failover to install the SP.

Problem:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ‘SQLVM\SQL02′. (Replication.Utilities)

Scenario:

1. We configured database mirroring on 2 SQL Server instances.

2. We changed the computer name from SQLVM to SQL2K5.

3. We tried launching the Database Mirroring monitor, but we got the following error:

SQL Server replication requires the actual server name to make a connection 

to the server. Connections through a server alias, IP address, or any other 

alternate name are not supported. Specify the actual server name, 'SQLVM\SQL02'. 

(Replication.Utilities)

If you just look at the error, it sounds a bit misleading because we don’t have replication configured in either of the servers.

Resolution:

-- 1. drop old server/instance name

EXEC sp_dropserver [sqlvm\sql01] 

GO

-- 2. add newserver/instance name

-- local meaning we're adding the local machine

EXEC sp_addserver [mysql2k5\sql01], local 

GO

-- 3. Restart SQL Server service

Problem:

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database <your db name> cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."

Scenario:

Sometimes you need to have "exclusive" access to the database, let’s say,

• when you want to force a restore, or
• when you want to alter the database and an exclusive lock is required

To get this exclusive access, you may need to set the database to SINGLE_USER mode:

USE [master]

GO

ALTER DATABASE [DBName]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

However, what if you accidentally lock yourself out of the database?

What if for some reason there is some other connection or user that is using the database, you will most probably get this error :

"Msg 5064, Level 16, State 1, Line 1

Changes to the state or options of database <your db name> cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed."

How do you get yourself back in?

Resolution:

1. Determine which connection is actually locking you out

-- look for the connection that uses your db and note the spid

EXEC sp_who

2. Kill the connection

KILL <spid>

3. Change the database back to MULTI_USER

ALTER DATABASE [DBName]

SET MULTI_USER;

GO

That should do it.

Error:

Attribute-centric column ‘@Title’ must not come after a  non-attribute-centric sibling in XML hierarchy in FOR XML PATH.

Reason:

You are having this issue if you have attribute- and element-centric columns in your FOR XML PATH query that are interleaved, as such:

SELECT

    EmployeeID       AS '@EmployeeID',            -- attribute

    FirstName        AS 'Details/FirstName',      -- nested element

    LastName         AS 'Details/LastName',       -- nested element

    Title            AS '@Title'                  -- attribute

FROM

    dbo.Employee

FOR XML PATH('Employee')

Resolution:

Order of attribute-centric columns in your FOR XML PATH query matters … if you have nested elements.

To resolve the error above, you must specify all attributes first, then specify the nested elements

SELECT

    EmployeeID       AS '@EmployeeID',            -- attribute

    Title            AS '@Title',                 -- attribute

    FirstName        AS 'Details/FirstName',      -- nested element

    LastName         AS 'Details/LastName'        -- nested element

FROM

    dbo.Employee

FOR XML PATH('Employee')

/*

Sample Output:

<Employee EmployeeID="1" Title="Production Technician - WC60">

  <Details>

    <FirstName>Guy</FirstName>

    <LastName>Gilbert</LastName>

  </Details>

</Employee>

*/