1

I'm maintaining a legacy server app that generate DMO files from SQL Server views.

Sometimes the server crashes because SQL Server consumes all cpu resources.

Using the SQL Server monitor I see that the problem is in SQLDMO connections that are consuming all cpu time and blocking the server.

I don't understand the reason of that because the dmo connection is with TRANSACTION LEVEL READ UNCOMMITTED and these SQLs never finish, during weeks. The only solution is to shutdown the server.

Roman R.
  • 68,205
  • 6
  • 94
  • 158
  • What does "generate DMO files" mean? Are you talking about the SQL 2000 management API, Distributed Management Objects? What version of SQL Server do you have? Have you used SQL Profiler or other tools to see what the connections are actually doing? Have you debugged the client code to make sure that connections are being closed? – Pondlife Feb 17 '12 at 12:23

1 Answers1

0

I would suggest looking into the code why these connections are not closed. I'm guessing there's no proper closing at the end or something along those lines.

If that is not an option, you could consider running a scheduled job that kills off these specific jobs every so often if they ran for longer than say, 24 hours.

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92