Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Unanswered: table gets stuck when trying to update or insert data

    Hello to all Forum users !

    I'm having a problem in one of mine orders table on Microsoft SQL Server 2005.
    The table has been fine for a long time since now, but yesterday it began to get stuck when trying to insert or update data. Even a single update statment gets into a constant loop without any results until stopping the query. No any error messages etc. it just won't finish the query.

    I've managed to solve the problem for a temporary time which seems to be like 10-90 minutes when about 15 users are using the database trought the front-end Microsoft Access. The solution is to run a DBCC CHECKTABLE ('schema.dbo.table', REPAIR_DATA) which allows the database run fine for a small period of time, but anyway it won't cure the problem. I've also made a copy of the table and inserted all the rows into it, but still no results.

    The table has a primary key and the same key is a foreign key on two other tables. Indexes has been built.

    I appreciate any help thank you!

    -emt

  2. #2
    Join Date
    Aug 2009
    Posts
    3
    the problem has been gone now for a while but I still have no idea what is the cause of this problem or what have solved it. On my last post I forgot to mention that the dbcc checktable isn't really finding any errors nor repairing them, it still somehow managed to fix it.

    Anyway the database is now running fine (temporarily).

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Sounds more like a blocking issue than anything else. The blocking can be cause in your case by either a long running transaction involving that table, or by an open transaction that was not committed/rolled back.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Aug 2009
    Posts
    3
    thanks for the reply rdjabarov, it's definetly a blocking issue.

    Problem is still there but it's been few days without problems at all. I'm still waiting to find the source of it and by using sp_who2 'active' to trace the blocking spid and then I can kill that process. I'm currently trying to get more information about the SPID's that are causing this.

    I got this code to help me to get SPID information

    SELECT p.spid
    ,convert(char(12), d.name) db_name
    , program_name
    , convert(char(12), l.name) login_name
    , convert(char(12), hostname) hostname
    , cmd
    , p.status
    , p.blocked
    , login_time
    , last_batch
    , p.spid
    FROM master..sysprocesses p
    JOIN master..sysdatabases d ON p.dbid = d.dbid
    JOIN master..syslogins l ON p.sid = l.sid
    WHERE p.blocked = 0
    AND EXISTS ( SELECT 1
    FROM master..sysprocesses p2
    WHERE p2.blocked = p.spid )
    order by last_batch, program_name

    the problem is that this is not giving me enough information about the process since the front-end users are using the same login_name and host_name. I need to know which SP is causing this..

  5. #5
    Join Date
    Aug 2009
    Posts
    1

    Service Pack?

    Quote Originally Posted by emt
    the problem has been gone now for a while but I still have no idea what is the cause of this problem or what have solved it. On my last post I forgot to mention that the dbcc checktable isn't really finding any errors nor repairing them, it still somehow managed to fix it.

    Anyway the database is now running fine (temporarily).
    emt, which service pack are you using? Have you applied the latest SP3 already?

Posting Permissions

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