Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    3

    Question Unanswered: 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?

    Thanks

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    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.

    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.

  3. #3
    Join Date
    Jul 2003
    Posts
    3
    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

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If you run the following command in Query Analyzer:

    sp_dboption pubs

    Does the output include AutoClose?

  5. #5
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •