Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server users lockup every hour

Posted on 2004-04-07
3
Medium Priority
?
255 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 1200 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 800 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

715 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