April 15, 2009 at 9:57 am
Hello,
I've tried looking up information on this issue, but I haven't found anything specific to it.
We have this recurring situation:
Server:
Microsoft SQL Server 2000 - 8.00.2055 (Intel X86) Dec 16 2008 19:46:53 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Database, Optimization Plan:
Optimizations job runs starting at 4:00 am.
Tasks:
Reorganize data and index pages - checked
Change free space per page percentage to - selected, set to 10%
Runs: Sunday, Wednesday, Friday
sp_spaceused result:
database_namedatabase_sizeunallocated space
[mydb]11986.06 MB1183.00 MB
reserveddataindex_sizeunused
11019072 KB5757864 KB217152 KB5044056 KB
Issue:
1. The job starts running at its 4:00 am scheduled time.
2. Somewhere, someone has left a query window open from the last query that they ran.
3. We get notified that an application dependent on that db server is hanging.
4. I check for DB blocking and find that the SPID for the query window in item 2 above is blocking the SPID for the DBCC command in item 1 above.
The blocking SPID almost always has a LastBatch date a day or two in the past, and its Command column says AWAITING COMMAND.
It seems to me that for some reason the SQL considers the open window an active connection and forces the DBCC command to wait. The query itself completed days ago and it is only the window that is sitting there, still open. But only when I kill the old SPID (and any similar SPIDs that may also have gotten into the chain) does the DBCC command complete, finishing the Optimizations job and freeing up the hanging applications.
Does anyone know why an open query window would cause blocking of DBCC?
What is the best practice for dealing with this issue? I was asked if there is a way to force connections to time out Is that possible in SQL 2000? If it is, is it advisable? And how does one set that up? Or is there a way to kill these particular SPIDs right before the Optimizations job runs, or at the very least a way to notify myself that the blocking has started, so I can go in and check before the calls start coming in?
Thanks in advance for any help. Let me know if any more information is needed to help define the problem.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 16, 2009 at 10:43 am
Why these things happen, I am not sure, but I have seen something similar to this on our SQL 2000 servers as well. More than likely the session (currently inactive) used a resource that the DBCC needed, and just never let it go. A rare thing but it can happen.
In terms of how you can get around this in the future, you could certainly right a quick script that run just before the DBCC that looked for any connection that was "AWAITING COMMAND" and then kill that process yourself. Finding those should be simple enough
select spid from sysprocesses
where cmd = 'AWAITING COMMAND'
and DateDiff(mi, last_batch, getdate()) > 60
would get you all of the spids that had a last_batch older than an hour. You could create a kill string based on that spid and execute the string.
However, this is the sort of thing that I would tend to want to try and determine what sort of actions the user was doing that held on to the resource and see if I could prevent that sort of thing from ever happening again. Obviously that is a far superior method than running a bunch of kill commands before every DBCC run. But the kill commands will work too. 😉
April 16, 2009 at 10:58 am
Mike (4/16/2009)
Why these things happen, I am not sure, but I have seen something similar to this on our SQL 2000 servers as well. More than likely the session (currently inactive) used a resource that the DBCC needed, and just never let it go. A rare thing but it can happen.In terms of how you can get around this in the future, you could certainly right a quick script that run just before the DBCC that looked for any connection that was "AWAITING COMMAND" and then kill that process yourself. Finding those should be simple enough
select spid from sysprocesseswhere cmd = 'AWAITING COMMAND'
and DateDiff(mi, last_batch, getdate()) > 60
would get you all of the spids that had a last_batch older than an hour. You could create a kill string based on that spid and execute the string.
However, this is the sort of thing that I would tend to want to try and determine what sort of actions the user was doing that held on to the resource and see if I could prevent that sort of thing from ever happening again. Obviously that is a far superior method than running a bunch of kill commands before every DBCC run. But the kill commands will work too. 😉
Thanks for your reply. It seems that the user in question had left a query window open on the workstation. Even though the query had been done for days, the open window still blocked DBCC when the time came for the optimization job to run.
I think we may work out some way of killing those open connections right before the job runs.
Thanks again for your help.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 20, 2009 at 7:25 am
Just my 2 cents....From time to time, when I run a query via QA, the transaction remains open and I will sometimes block other processes until I close my widnow, which then asks if I want to commit "open transactions". Very sporadic and I cannot figure out a consistent pattern to determine a resolution. I'm running SQL2000, sp4. I make it a point to close my windows (luckily, it's only me) each day before I leave so I don't run into those kinds of issues. I'd sure like to get it resolved though.
-- You can't be late until you show up.
May 21, 2009 at 4:58 am
hello everyone,
I trying to close the open session by one of web application, I believe that the developer forgot to close the connection object, can any one suggest me how to close / kill the spid which are opened by the appication.
regards
Ritesh
May 21, 2009 at 6:21 am
You can use sp_who or sp_who2 to determine the thread and then issue kill spid. I'd be hesitant because if the thread does have an open transaction, the data will not be committed to the database but will be rolled back. Not sure if that is acceptable for your application. IMO - get the developer to correct the code.
-- You can't be late until you show up.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply