Database locks, Why? What could cause?

Archived Topics
1

Ok I have a problem and I was not sure if to post here or in the database section so if I post it wrong sorry.

For about the past 8 months we have had our project running and for the most part it has been running flawless. There has been occasion issues here and there but overall has been running well. The issues all started about 3 weeks ago. We have a view that is brought over from another SQL server, this view is paramount to the proper operation of our project. What happened was an update or something was done on this remote server and it caused the database to become corrurpt which caused the view we use to also not function.This up and down of the remote server happened for about a week. Then it seemed to get fixed. This remote server also has the latest MS-SQL 2005 patches and updates installed. Now back to the server our IA software and SQL instance are located on. What recently started happening is a table that is used for assigning employees to work centers all of sudden started going into deadlock. And it causes the connection between FPMI and MS SQL to fault out. Why did this start to happen all of sudden? I mean none of the queries were changed, none of the project was changed ( I choose to CMA, when the issues first started happening with the remote server I made the choice not to do or change anything untill the system re-stabilized ), the IT dept says the updates and patches were not put on the IA server, only the remote server, so as far as I know the SQL install is the same as it had been. We are actually running fewer work centers than we did in the past, so I am looking any and all ideas of what can be checked to find out what occured and why this table is going into lock and why it takes down the entire connection between FPMI and MSSQL,then just a few seconds later the connection re-establishes, and from that point since the table is in deadlock the connection is like a roller coaster it goes up and down and keeps repeating untill you shut down and restart the SQL Service.

Now once you have shut down and restarted the SQL service, the system will run fine again for about 10 to 15 hours then it will deadlock again and the whole process needs to be done again. And this is getting to be a pain, has been going on for the entire week. Any ideas welcome. Thank you.

2

You’ll really need to get help from your IT department on troubleshooting. Start by going through the logs in the the Event Viewer and Enterprise Manager. As an interim fix, you can easily write a batch file that stops and restarts the services and have it run periodically as a scheduled task.

3

Thanks Nathan for the reply. Although it does not answer my bigger question. Which is why would when one table locks, does FPMI drop connection to the entire database instance? maybe I dont know enough about SQL but it just does not make sence to me I mean if a table locks thats fine just dont allow updates and such to that table but drop the entire gateway connection. Are the commands from FPMI sent in order one at a time kind of sequentially or are they dumped in the SQL more like parallel and it is upto SQL and its transaction manager to schedule them.