We are having sql server 7. But recently when we executing a batch of SQL in that some updates were there & those updates causes triggers needs to be fired. But batch gets teminated in between. We checked the error logs & found that Few other processes are running continously like SPid 14 etc which are opening & closing the datafiles for pubs, & northwind database. Why? Is this a server settings problem Or Virus?
For deadlock is nested triggeres becomes problem after 3-4 level deep on multiple tables?
There are a number of things which can make deadlocks more frequent.
One is long running transactions. Triggers cause the transaction to extend for the length of the trigger and also make the actual update less likely to be efficient so can cause the problem - nested triggers even more so.
>> opening & closing the datafiles
Which version do you have? It sounds like these databases are set to autoclose which is a bit odd - I would have said maybe it's a checkpoint but spid 14 doesn't sound like a system spid. Do you have anything monitoring databases or maybe backup software.