10 common SQL Server problems and solutions

By Joshua Hoskins, MCSE, MCDBA

1) Users complain that queries take longer than usual. –

If your normal queries take longer than usual, it could be due to resource contention from locking. This will usually occur if new processes have been added to your system, or if the load on your system has recently increased.
You should begin troubleshoot this problem using Query Analyzer and the SP_WHO2 command. The results from this command will contain a BlkBy field. If SPIDs are being blocked, the command output will show the offending SPID number. You may need to follow a large chain of blocks to find the head. Once there, you can use the dbcc inputbuffer command to see the SQL statement that the SPID is running. This will point you toward the problem's cause.

2) Users receive Out of Space errors. –

If users receive messages that a database is out of space, there are two possible answers. First, the physical drive which stores the database or its transaction log has run out of space. If this is the case, examine that disk for any file(s) that are rapidly growing or any other problems. It's also possible that the database or the transaction log file have a set maximum file size. If this is the case, increase this limit to allow database operation. Users may also receive errors that tempdb is full. To resolve this issue, restart the MSSQL service--restarting SQL will recreate tempdb from scratch. You should also determine if tempdb has a maximum size and increase that size if necessary.

3) Users report permissions denied errors after you grant them rights to a stored procedure. –

Even if you give users rights to execute a stored procedure, you still need to give the appropriate rights on the objects referenced by the stored procedure. For example, if you have a stored procedure that performs a select from the customers table, you must give the user select rights on the customer table.

4) Clients drop their sessions without disconnecting from the SQL Server causing block chains. –

Occasionally, your clients may lose connectivity to the SQL server. This can cause their SPID to hold a lock on a resource while waiting on the timeout--this seems especially prevalent in clients running older versions of Microsoft Access. To resolve the problem, use the commands SP_WHO2 and KILL to delete the orphaned process from the server. You can also shorten your clients' time out value, causing the server to more quickly kill orphaned processes.

5) A new query slows the entire system to a crawl. –

On a multi-CPU system or one with hyper-threading, task manager or performance monitor may show 100 percent utilization on one processor, while the others are nearly idle. If this is the case, the currently-running procedure's execution plan is incorrect. Change the degree of parallelism for this query. To do this, use the MaxDop command (maximum degree of parallelism) within the query to specify the degree of parallelism. You may need to repeatedly change this value to find the option that provides the best performance without causing system-wide problems.

6) A formally solid scheduled job begins failing. –

If a user who is listed as the owner of a SQL Agent job is terminated or has a name change, the SQL job will fail. You must reset the owner name on the job to a currently available user name.

7) A database goes suspect. –

If a database is in suspect mode, it generally indicates corruption within the database. If the database is not corrupt, the database's physical files may no longer exist where the master database references them. This can be caused by a physical disk's drive letter being changed or no longer available. To fix this problem, detach the suspect database, then attach it selecting the file(s) new location.

8) You can connect to a SQL instance locally, but can't connect over the network. –

If you cannot connect to your SQL server over the network, the SQL's default port (1433) may have been changed. You will have to reference the new port (or use named pipes) to connect. Also, some versions of MSDE do not allow network connections by default. To correct this, you will have to use a DISABLENETWORKPROTOCOLS=1 switch on setup to allow network connections.

9) You migrate a database to a new server, but the users in the database cannot connect to it. –

If you move a database from one server to another, the database users and their privileges will migrate with the database, but the new SQL system will not house them as SQL users. To migrate the users, you can use the Data Transformation Service (DTS) or a script available in MSKB article Q246133.

10) Users are reporting errors about deadlocking. –

If users are receiving error messages stating that they have been chosen as deadlock victims (or a similar error depending upon your error reporting scheme), your system is suffering from deadlocking. This is caused when two or more queries each locking resources the other query needs to complete. The SQL Server error log will report when it has resolved a deadlocking situation, and that should be the first place you check if you believe you have this problem. You can also turn on trace flag -t1204, as it will give you more information in your log. The first thing you will need to determine is the contention level on your server. Are these two queries specifically having problems running simultaneously? If so, it may be as simple as scheduling them to run at differing times. If they are commonly run queries, the queries may need to be optimized to limit this behavior.