SQL Server Troubleshooting

This article will discuss and talk through some of the options available to you, if you have gone through the installation process, which has been described in some detail in previous articles, and you find yourself unable to connect to your new instance of SQL Server.

The first and one of the most important points to note and remember when performing SQL Server troubleshooting, is to find out and identify the problem first and then apply the solution. It may be as simple as starting the service, it may be more complicated, but you need to know what has to be fixed, altered, changed or even reinstalled. When you have this, you a have place to start from…

If your installation has failed, the first place I would recommend for SQL Server troubleshooting is looking at the SQLSTP.log file. This file will contain a list of error messages that were encountered during the installation. These errors could relate to any problem encountered during the installation process.

An example Microsoft provides is "if IIS (Internet Information Service) is installed on your server and the service is running then you will receive a message during setup asking you to shut down all applications that use ODBC before setup can proceed." some other services that if started can cause problems include:

• Exchange Server
• SNA Server
• Disk Keeper
• SNMP Service
• Anti-virus software
• Backup products

In a previous article on upgrading SQL Server I have mentioned what services should be running and recommend closing down all others.

The SQLSTP output file is generated by the scripts that run during the setup and records the DBCC (Database Consistency Checker) error messages and can give you important information on your troubleshooting.

You can also view log information in the SQL Server and SQL Server Agent error logs and Windows application log to allow you to view error messages received during the installation of SQL Server. Each time that the SQL Server and SQL Server Agent services start, a new error log is opened. Application events are appended to the Windows application log.

For SQL Server troubleshoouting, the following is a list of logs that can be viewed and includes a description of what they do, where they can be found and how you go about viewing them:

Sqlstp.log the log records all SQL Server Setup actions it can be found in either C:Winnt or C:Windows depending on your underlying operating system and it can be viewed with any text editor.

The Windows application log provides information about application-related events in Windows. There is no specific location of this log but it can be viewed using the Windows Event Viewer.

The SQL Server Error Log is used to records the step-by-step process of starting SQL Server, the log file can be found in C:ProgramFilesMicrosoft SQLServerMSSQLLog and it can be viewed in SQL Server Enterprise Manager or any text editor such as notepad etc.

The SQL Server Agent Error Log is slightly different, it records errors incurred by the SQL Server Agent service, and it can found in the same folder as the SQL Server Error Log (C:ProgramFilesMicrosoft SQLServerMSSQLLog) and can also be view in SQL Server Enterprise Manager or any valid text editor.

I would also recommend that you check the network connections, if your local client (the Enterprise Manager or Query Analyser on the server) can connect but your networked client cannot then check the following:

• Ensure that your client workstation can connect or see the server. You could try to ping the server from the client. If you get a reply then this is not the issue.
• Client and Server network Libraries are the same.
• Ensure the default client network libraries are appropriate for your network.

Microsoft have identified several common problems that can be encountered when installing SQL Server or why the service will suddenly stop have been identified and some of these are listed here with possible solutions:

The SQL Server service does not start up. A possible cause of this is that the Windows operating system cannot access a domain controller to validate the domain user logon account. You will need to re-establish access to a domain controller or reconfigure SQL Server service to use the Local System account.

For more on SQL Server troubleshooting, you can also get the following error message "Error 1069: The service did not start due to a logon failure." This error is raised if the password for the domain user logon account has changed or has expired. You will need to specify a valid password for the SQL Server service.

One SQL Server troubleshooting issue is that the SQL Server service does not start. This problem can also be caused by SQL Server entries in the Windows registry that are corrupted. To fix this problem, rerun Setup, and under advanced options, click Registry Rebuild to rebuild the SQL Server registry entries.

Another troubleshooting issue is that you may find after installation that the SQL Server Agent service does not start. The problem can be caused by the domain user logon account not having appropriate permission to access SQL Server. To solve this problem add the domain user logon account to the Windows 2000 local group Administrators, or to the sysadmin role in SQL Server.

If after installation one of your SQL Server management tools (Enterprise Manager, Query Analyser etc) cannot connect to SQL Server, then it is very probable that the SQL Server service has not been started. Verify that the SQL Server service has started and you should be able to connect.

If you get an error message saying "A connection could not be established to [YourServer]." Then either the client and server network libraries do not match or you do not have permission to administer the computer running SQL Server. To overcome these problems modify the default network library on the client; Add a network library to the server, or both. Or if the problem is caused by you having inappropriate permissions, log on to Windows with an account that has permission to administer the computer running SQL Server, or connect with a SQL Server login account, such as sa.

In conclusion this article has discussed SQL Server troubleshooting after your installation of SQL Server is "complete". I have identified possible problems that you may encounter although I am sure this list is not complete, and I have also looked at possible solutions to these problems.

If all else fails though then you always have the option or re-installing SQL Server. If you do this please make sure you uninstall your misbehaving instance first. There are instructions for this in my article on how to uninstalling SQL Server.

Return to installing the SQL Server database