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.

Five reasons you need to trust your staff

By Scott Lowe

Takeaway: Trust your staff and avoid micromanagement to help your staff grow, help the organization better meet its goals, and help your own career.

Micromanagement. The word is generally construed as a negative management trait to be avoided at all costs. For hands-on technical people who have come up through the ranks, it can be a tough trait to shed. But, there are five really good reasons you should embrace your inner CIO and let your staff do their jobs. Here they are:

1. You'll inspire confidence.

Trust begets trust. Do you trust your staff to do their jobs and do them correctly? If not, you have a situation that needs to be resolved. Your issue may be that:

• Your staff truly doesn't have the skills to get the job done. If this is the case, you need to train your staff or add people with the correct skills.
• You may not have the self-confidence or experience to lead an experienced staff. This can manifest itself in a need to interfere or micromanage.

Obviously, there is a difference between general management and micromanagement, and even the best leaders can sometimes devolve into micromanagement when stress is high. But when you have confidence in your staff, they will know it. No one wants to be micromanaged; people want to work for those who value their contributions and who treat them like professionals.

There is also a difference between micromanagement and rolling up your sleeves and working alongside your staff when necessary.

2. You'll get more done.

Every minute that you spend too closely monitoring someone's efforts is a minute that you aren't spending on strategic IT issues that can help propel the organization to new heights. Further, when you're riding someone, that person's productivity also suffers. As a result, both of you are doing less.

Instead of sitting down with your staff and explaining to them exactly how they should do their jobs, consider a different approach to managing projects. When the need arises for a new project and you've decided to assign the project to one of your staff, provide them with an assignment that consists of:

• A project explanation
• General guidance and expected outcomes
• Deadlines
• A project communications plan that provides you with updates in an agreed-upon manner

From there, expect the person to provide you with regular reports on progress and to come to you when there is an exception of some kind or a need for clarification. There's no need to constantly go to the person's office and ask for updates as long as the person is providing you with information at the agreed-upon intervals and milestones are being met.

This frees you up to work on your own projects and keeps your staff focused on their work and meeting your expectations. Sticking to the agreed-upon communications plan also works to inspire confidence from your staff since they know that you trust them to be professionals.

3. You'll breed new leaders.

If you're micromanaging people all the time and doing their jobs, they're not getting the opportunity to grow. When you let them do their jobs and hold them accountable to outcomes and expectations, you're helping them work better on their own. By not jumping in and attempting to solve all the problems they may encounter, you force them to seek solutions and answers. Obviously, don't be cruel. If someone is truly at a roadblock that only you can clear, do it. Watching people suffer isn't really fun.

4. Your staff will stick around.

When people have opportunities and can see possibilities, they'll stay. If you're the kind of leader who inspires confidence and trust, people will want to work for you. On the other hand, if you're the kind of CIO who has to push your DBA out of his chair and take over the keyboard to write a query for him, you'll probably see some resentment that will eventually take the form of staff departures, morale issues, and complaints about their horrible boss.

5. You will get promoted.

Many CIOs have come up through the IT ranks and have significant difficulty letting go. I say that from experience. However, I've been fortunate in that if I do happen to step too far (hey, I'm only human!) they respectfully tell me. I like to believe that they trust my intentions and have enough respect for me to let me grow as a leader as well.

CIOs who have worked their way up the technical-side of the ladder sometimes tend to focus on the technology at the expense of overall company goals. If you're a CIO who has come up through the ranks and you're spending most of your time doing the jobs of your staff members, you're not focusing on being a CIO and will lose the respect of your executive peers.

On the other hand, if you're able to move beyond your technical roots and can help propel the business, your opportunities are endless.

Summary

I try to avoid words like "always" and "never" because I absolutely, completely, definitely do not believe in absolutes. In some circumstances, you'll need to break the micromanagement rules for a perfectly valid purpose. But that should be the exception, not the norm.

Trust your staff and avoid micromanagement to help your staff grow, help the organization better meet its goals, and help your own career.