Solved

SQL Server users lockup every hour

Posted on 2004-04-07
3
253 Views
Last Modified: 2006-11-17
Hi.

I have a peculiar problem, I think, in our SQL server database that runs our accounting and order entry applications. We have about 80 users who are attached to the server through an ODBC link (named pipes) with users running either Win2K or XP clients.

Every once in a while, sometimes weekly, we get a situation that occurs that causes all users to freeze in their application. The Server itself will still be running fine, just the clients using the SQL server applications get locked up. If I simply stop and immediately re-start the SQL server, the clients will unfreeze after restarting the application they were in (an Access based front end with ODBC linkage to the SQL server).

The odd thing is that after I do the restart (using the SQL services using the SQL Server Service Manager), it will freeze our users again almost exactly 1 hour later (about 1 hour and 3-5 minutes). I have watched this happen four successive hours. The only way to fix the problem is to restart the actual Windows 2000 Server. The problem goes away for maybe a week, sometimes several weeks, then resurfaces again with the same scenario. I don't see anything in the Server log or SQL Server log that flags anything, but maybe I am just missing something obvious.

Any ideas? Thanks.
-Ray
0
Comment
Question by:rfmassa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 13

Assisted Solution

by:danblake
danblake earned 300 total points
ID: 10775550
Normally this would be caused by a reoccuring job or process which is locking the system,
have you tried running the following:
sp_who2 -- to identify the user who is causing the 'locking' or processes that were running at that time.
sp_lock -- to identify what kind of locks are being caused.

profiler -- to identify the commands/what is being requested when the application locks up ?

perf monitor -- to identify if the server/system is overloaded and in what area when this occurs ?
0
 

Author Comment

by:rfmassa
ID: 10775706
Thanks. There is nothing that runs on a re-ocurring basis. Users are just creating new invoices or creating new orders in the SQL database. Of course someone could be running a report, but it would never happen on an hourly basis. I will try your suggestions. Thank you!
-Ray
0
 
LVL 7

Accepted Solution

by:
ChrisFretwell earned 200 total points
ID: 10775932
Do you do transaction log dumps on an hourly basis? Does anything appear in the sql error logs? Is there anything in the windows event viewer?

When you restart sql, do you see lots/any rollback/rollforward messages in the sql error log?

Have you had the users disconnect (shut down if they have to) one at a time to see if there is a break even point?

Profiler and perf mon are 2 great suggestions. If you find sp_who2 seems blocked, try this select * from sysprocesses where blocked>0 or waittype > 0x0000  (run from master). This will show you if anyone is blocked or waiting on a system resource.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question