A job that ran successfully (2-3hrs) for the last six months.
BUT, a couple of weeks ago it started failing to complete in a timely fashion. Requiring crashing to access the database.
1st – what did you or one of your DBA do to the system in the last 30 days? If you roll back to a stable setup on your test machine does the problem go away.
You have three problems -
A – whatever that program was it required exlcusive control of the database. BAD programming. SQL Server is designed to service multiple requests by default. A program that directly ties up the database is always a scary concept. Even backup can get around other users.
B – Since this problem is now a couple of weeks old, how good are your backups and can you replicate this problem on your test system. (You do have a test system as weel as the productions server!)
C – In reverse order of probability the problem will be found to be:
A patch to the operating system.
A patch to SQL Server.
A patch or update to some other application on the server
A change to the program in question.