Solved

SQL Server users lockup every hour

Posted on 2004-04-07
3
251 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

735 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