I have scheduled DTS packages that truncate tables on one database and then re-load data to those tables by pulling the data in from a different database. While this DTS job normally takes about 30 minutes to run, we’ve noticed that occasionally the job can take hours to run. For instance the last 3 nights the job has taken over 7 hours to complete.
I found blocked processes on the database where the data was trying to load. I believe this blocking is happening because of Access databases that are used to connect to this database for Ad Hoc queries. These connections may even be from the day before. I’ve even noticed that when Access is closed down on the user end, the connection still seems to be active on the SQL Server.
Besides scheduling the SQL Server service to stop and restart itself every night before these packages run, is there anything else I can do? Is there a way to automatically kill these processes another way? Maybe there is a way to fix this problem without killing the processes? Access is such a nightmare with blocking tables.
Thanks for your help.