Solved

SQL Server users lockup every hour

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now