Long running process

[Jim Zhong]

Hi all,

I have 3 three scheduled job: one runs onece a day, one runs once per hour, and another runs every 17 minutes. It is a NetIQ application. I just scheduled SQL Server maintianace job last night which ran at 2:00Am and 4:00Am. This morning, I came in office and found all my jobs were still running; and they were all blocked by the first 3 jobs. I had to kill all of them. In this afternoon, I kicked off one of my many DTS packages which runs usually about 40 minutes, but it failed. I tried several times but no luck. I suspected one of user tables corrupted or one of stored procedures corrupted. After I recycle the server, and dropped the table and the stored procedure, and recreated them, the package went fine. The store procedure involves many updates and inserts.

The question I have is: is it possible to cause this problem because I killed the unfinished jobs (especially the sql maintanace job)?

NOTE: the sql maintanace job does not include the backup of database and transaction log.

[Jun]

You are right. It is possible to cause this problem because you killed the unfinished jobs. The reason is that when you are running a long process, this process locks some objects, and then suddenly you kill that process without "Rollback". So this killed process is in the middle of nowhere, your next process might waiting for the objects to be released from the killed process. If you go to your EM under "Management/Current Activity/ Locks Objects ", you might notice some locks there. This may not involve any corrupted objects. The simple thing to do (if you can) is to Shotdown your Server and re-start it. All locked objects will be released.

Hope this helps to answer your question!

Jun


------------
Jim Zhong at 1/10/01 9:29:50 PM


Hi all,

I have 3 three scheduled job: one runs onece a day, one runs once per hour, and another runs every 17 minutes. It is a NetIQ application. I just scheduled SQL Server maintianace job last night which ran at 2:00Am and 4:00Am. This morning, I came in office and found all my jobs were still running; and they were all blocked by the first 3 jobs. I had to kill all of them. In this afternoon, I kicked off one of my many DTS packages which runs usually about 40 minutes, but it failed. I tried several times but no luck. I suspected one of user tables corrupted or one of stored procedures corrupted. After I recycle the server, and dropped the table and the stored procedure, and recreated them, the package went fine. The store procedure involves many updates and inserts.

The question I have is: is it possible to cause this problem because I killed the unfinished jobs (especially the sql maintanace job)?

NOTE: the sql maintanace job does not include the backup of database and transaction log.