Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server users lockup every hour

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

963 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