MS SQL Update Error

I have a database on a MSSQL Server 7 running on a Windows NT machine. This database works the last 4years with no problem. The database file is now 3 GB with 45 users. The last two months every single day an update error on a specific Table occurs. I cannot update a record in this table, and after this error occurs more than 100 records or more seemed to be locked. While I cannot edit the previous records I can create a new one with no problem. I have this problem once every sigle working day (ant the currious thing is that is occured from 8 to 10 in the morning every singke working day) . The table seems to work again after some hours or after one single day, or after I stop and start again the sql server service. Some times I have to wait some hours in order to manage to edit records in this table again. (I edit the records by using a program created with Microsoft Access and ODBC. When the error occurs I cannot edit the records neither by Access not by SQL Server Query Analyser).
This error doesn't occur when entering a new record, and might occur when only one user works with the specific Table. This table has 75 fields and about 70000 records. This table is the master of a table which has more than 200 fields and more than 750000 records. In the detail table I have no problems.
Similar problems not in this degree exist on a second database installation with about 25 users with the same table but running under a Windows 2000 Server machine.

Well when the problem appearead again I tried to edit the locked records by using Access ODBC, Access (adp project), Delphi with ADO-OLEDB and Delphi with native SQL drivers. The result was the same. A Timeout expired message appear. In the application log file of the MS SQLserver machine every time the the update error appears I get the following message: Could not Set Up Listen on Connection '1433'. Operating system Error 10049.

Any ideas??

Best regards,
Manolis PerrakisCan you supply the wording of the update error?

Also, since it tends to happen from 8-10 most mornings, you can check for blocking problems by running sp_who2 in Query Analyzer. If there are numbers in the "BlkBy" column, you have problems. Hopefully you will be able to figure out the blocker by the program- or host-name in the output of sp_who2.|||I don't have a solution for you. But I have experienced more than my share of timeouts and there's one thing I've learned: it's never what it says it is. But it seems to happen more the larger the number of elements there are in an index, and less on more normal databases. And we suspect it may be related to our use of EIDE drives instead of SCSI.

And I strongly suspect it has something to do with resources. For example: If you execute an update query causing SQL Server to execute a series of subqueries, and one of them hits a resource ceiling, one of the others with time out waiting for a thread to finish. The thread never finishes, because it's waiting for resources to be freed before it can continue. This happens all the time on our 1 GHZ server with 1GB of RAM.

I suggest you check all your indexes and make sure they're not clustered. Also, make sure you're not using too many connections if you can avoid it. And if you have too many tables that aren't normalized, try to fix that as well. Finally, you can do what I'm planning to do: change to MySql server or something else. IMNSHO, Sql Server isn't as robust as it could be.

I hope this helps.|||One other thing...Port 1433 is where SQL Server listens for connection requests. You're not opening the database everytime you do an update, are you?|||Thanks for the suggestions. My server is a Compaq ML350 with 3 SCSII hards disks and a Raid Controller, and about 600 MB RAM. I can see from the task manager that it uses all the resources. I might increase the RAM memory and in the next months I might get a new server.

Please tell me if there is a serious problem the fact that I also store in this server about 10000 photos with sizes about 2 GB, that are displayed as product pictures in the database program I've created.

I also have to mention that I use a lot of subqueries in the MSAccess program I use right now.

As far the connections issues, since I now use MSAccess for the clients, each table that is connected (attached) to the MSSQL Server holds internally its connection properies (datasource name, database name, username etc..), and since that I have about 200 tables attached from the SQL Server, this might create a connection problem.

I'm currently moving to Delphi 7, which has a global connection for all the tables of the same server. In this version the subqueries are replaced with optimised queries, and client datasets will be used in order to decrease the server load.

Best Regards,
Manolis Perrakis|||The data I store in image fields is usually quite a bit less than yours, but I don't think that's a problem. But I'm pretty sure the RAM is. You can improve things somewhat by making sure your logfile is on a different disk drive than the database, if you haven't done so already.

Try to get away from holding tables open where you can. Also, if you have a lot of indices on the master table, or a high number of values in the master index, I know from experience that can cause problems. Those problems can be alleviated by adding more RAM, further normalizing your database to reduce the number of keys (if that's possible), or looking at ways to reduce the amount of index storage--making sure you're not using clustered indexes, making sure that you're not using full-text indexing if you don't need it.

But I'm also wondering whether it's the bound controls that are the problem. At 8:00 is when everyone comes into work and launches the app, right? And by 10:00, app use drops down to a manageable level? Tables linked to app forms are almost always in Dynamic mode and I'm assuming that's the case here. If you were to modify your app so that the most common screens were read (releasing the recordset and holding the data in memory), and perform updates as that data was written (creating a new recordset for writing), you'd probably reduce the load on Sql Server significantly.

Not that any of this presents a panacea. I've spent the majority of my bugchasing over the past couple of years on problems with SQL Server/ADO. If I had any time at all, I would switch like a Tareyton smoker. :) But I think this problem can be solved.