Deadlock & probably other problem

Hi,

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?

ThanksThere 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.

Try running this sp
http://www.nigelrivett.net/sp_nrSpidByStatus.html
it will tell you what the last statement executed by a spid is.
You could also use the profiler to see what is happenning but that will also slow down the system.|||Hi
Thanks For Reply.
For Triggers i reduced the transactions time & batch size.

For opening closing datafile the content in log files were like this

2003-07-08 08:39:08.00 spid33 Starting up database 'Northwind'.
2003-07-08 08:39:08.00 spid33 Opening file d:\SQLData\DATA\northwnd.mdf.
2003-07-08 08:39:08.12 spid33 Opening file d:\SQLData\DATA\northwnd.ldf.
2003-07-08 08:39:08.39 spid33 Closing file d:\SQLData\DATA\northwnd.mdf.
2003-07-08 08:39:08.43 spid33 Closing file d:\SQLData\DATA\northwnd.ldf.
2003-07-08 08:39:08.45 spid33 Starting up database 'pubs'.
2003-07-08 08:39:08.45 spid33 Opening file d:\SQLData\DATA\pubs.mdf.
2003-07-08 08:39:08.46 spid33 Opening file d:\SQLData\DATA\pubs_log.ldf.
2003-07-08 08:39:08.62 spid33 Closing file d:\SQLData\DATA\pubs.mdf.
2003-07-08 08:39:08.65 spid33 Closing file d:\SQLData\DATA\pubs_log.ldf.

This happening continously. In this case the SPID is 33.

Thanks|||If you run the following command in Query Analyzer:

sp_dboption pubs

Does the output include AutoClose?|||Just set the databases to not autoclose and it should get rid of that.

Use profiler to log any accesses to those databases and you will see why it's happenning.

Sounds like there is some monitoring going on somewhere - maybe someone keeps clicking on things in enterprise manager or has a gui which tries to get info from the databases.