A Problem Shared

When SQL Server goes wrong it tends to do so in spectacular fashion. To give you a head start, we look at some common culprits and share some troubleshooting tips. Forewarned is forearmed…

SQL Server has come a mighty long way from when I first started to use it in the days of OS/2. Microsoft’s hard work on the management side of the product should reduce the amount of troubleshooting you need to do. The problem is that when the product does go wrong, it does so in spectacular style, possibly stretching an administrator’s skills to the limit. It is impossible to pre-empt every troubleshooting scenario, but the following are common issues.

Error list
Full transaction log
The SQL Server transaction log keeps a record of changes to a set of data. Maintaining this record preserves the integrity of the database and the ability to manage transactions that fail halfway through processing. For many, the transaction log is one of the key differentiators between a “real” relational database and one of the PC-based alternatives.

The problem is that a transaction log can fill up over time unless it is managed appropriately. As soon as the log becomes full, the database will only permit read-only access if it is online or, if it is offline, will mark the database as RESOURCE PENDING until the administrator sorts it out.

Log truncation (ie reducing its size) is often configured to occur on a regular basis as data is finally committed to disk. This way the log never fills up. Another strategy is to back up the transaction log as part of your data protection strategy, truncating the log once it has been safely written to a backup device. If the transaction log is full, you’ll need to work out why and prevent it happening in the future.

Typical measures for dealing with a full transaction log include:

• Back up the log This will reduce its size. If you are operating in full recovery mode or bulked log recovery mode and the transaction log has not been backed up, this may have prevented its truncation. Regular backups should prevent this happening again.
• Delete other unwanted files on the disk, freeing up disk space for the log to continue growing.
• Increase the size of the log file if sufficient space is available. Most administrators would opt to use the “autogrow” setting for the transaction log, so that the transaction log file will continue to grow in size, assuming there is enough physical disk space available for it.
• Move the log file to another disk with more free space.

If the error 9002 occurred while the database was in recovery mode, once you have sorted out the problem you’ll need to recover the database using ALTER DATABASE database_name SET ONLINE.

Insufficient space in tempdb
Tempdb is a global storage space used by all users connecting to a SQL Server. It is used to store user objects, internal objects and version stores. If tempdb starts to run out of space, performance will be hit as objects are swapped in and out of memory, finally resulting in a server that is barely usable. Various tools are available for monitoring tempdb and typically you’d use them to look for large queries, temporary tables or table variables that are known to consume large amounts of tempdb space. Tempdb disk space problems will manifest in a number of error messages:

• 3967 occurs when the version store is forced to shrink because tempdb is full.
• Either 1101 or 1105 occurs when any session must allocate space in tempdb.
• 3959 occurs when the version store is full. It may appear after a 1105 or 1101 error in the log.
• 3958 or 3966 occurs when a transaction can’t find the version record in tempdb.

Most tempdb space problems occur when large queries are being executed that need to create lots of temporary objects to sort and manipulate data. Clearly the more complex the query, the more sorting and arranging of data that will need to happen.
Various queries can be executed against tempdb to examine what objects have been created and how the space is being used. These are useful diagnostic tools to help prevent tempdb space filling up again. As an aside, if a query takes less than a few minutes to run, it is unlikely to need large resources in tempdb (but it may be performing slowly for a host of other reasons). The problem queries are those that take a long time to execute.

You can increase the size of tempdb using SQL’s admin tools. It may be worth learning about tempdb capacity planning if this is likely to be a problem, especially as versions of SQL Server 2005 onwards require more space in tempdb than previously.

Connection times out
When attempting to connect to a SQL Server database, a timeout message may appear if there is a problem in processing the connection. For example: “Cannot connect to . Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)”.
From the command line, using sqlcmd, these errors may appear:

• Could not open a connection to SQL Server.
• SQL Network Interfaces: Error Locating Server/Instance Specified.
• Sqlcmd: Error: Microsoft SQL Native Client: Client unable to establish connection.
• Sqlcmd: Error: SQL Native Client: Login timeout expired.
• An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections

Figure 1: Typical SQL Server 2005 connection failed dialogue box

Clearly something isn’t right, so what is the likely problem?
‹Server name was typed incorrectly. This is probably “finger trouble” so try again with the correct server name.

• The SQL Server service on the server is not running. Start the instance of SQL Server database engine.
• The TCP/IP port for the database engine instance is blocked by a firewall. This will need changes to the firewall to allow access to the database engine. Clearly you will need to liaise with your security team to ensure only appropriate changes are made.
• The database engine is not listening on port 1433 because it has been changed, or because it is not the default instance, and the SQL Server Browser service is not running. Start the SQL Server Browser service or connect specifying the TCP/IP port number.
• The SQL Server Browser service is running, but UDP port 1434 is blocked by a firewall. Work with your security team to configure the firewall to permit access to the server, or connect specifying the TCP/IP port number.
• The client and server are not configured to use the same network protocol. Confirm that both the server and the client computers have at least one enabled protocol in common.
• The network can’t resolve the server name to an IP address or the network can’t connect using the IP address. You’ll need to call in your network administrator to help with this.

The best strategy to resolve these issues is to start simple and work your way up the problem stack.

Login failed for user X
This error message results from an authentication failure based on the credentials supplied. The normal error message is: “Login failed for user ‘’. (Microsoft SQL Server, Error: 18456)”.

For security reasons the user is only likely to see a basic login failed message rather than the full description reserved for the database administrator, which includes an error state indicating why the login failed.

Reasons for the failure range across the usual issues with passwords and logins. Invalid user ID would generate a state error of 2 or 5 ranging to a state error of 9 (indicating an invalid password) or 12 (indicating a valid login but failed server access). You can see why the state information is hidden from the user; if it weren’t it could help a hacker narrow down their options.

There is little rocket science to troubleshooting these errors as the solution is probably fairly obvious. Troubleshooting SQL Server is just like troubleshooting any other IT system. By taking a methodical and logical approach, based on your knowledge as a database administrator, you are bound to find the solution somewhere.

Of course, we’ve only discussed a few of the main problems you can get with SQL Server – in reality there are many thousands of errors that can occur, each of which can be a challenge. But then, who said being a database administrator was easy?