September 19, 2007 at 5:35 am
My users are trying to use a program (created by another ex-employee - source code unavailable!!) and getting a Timeout error, so I decided to try and investigate.
I'm no database administrator, so I have no idea what's going on here, but there are a couple of entries (as mentioned in the title) under the Locks/Process ID view in SQL Server Manager.
When I run the sp_who stored proc, SPID 9 states AUTOSHRINK as the cmd and SPID 59 states KILLED/ROLLBACK
When I try to look at the contents of one of the tables in the database, I eventually get a timeout...this table only has 88 records in it. So I'm making the assumtion that these SPIDs are locking the table.
Amd I correct? If so, is there anyway to remove them so I can free the table/database?
I appreciate all replies.
Thx
Billy
September 19, 2007 at 5:47 am
Apologies...this belongs in the 7,2000 forum (which I thought I had selected....
September 20, 2007 at 12:07 am
No worries. I would disable autoshrink (autoclose too) - just not worth it, causes more problems than it was ever worth. If you need to shrink the database do it via maintenance plan that runs after hours/over the weekend.
Joe
September 20, 2007 at 5:58 am
When was the SQL Server last restarted? It sounds to me like the SPID in a Closed/RollBack state may be "hung" and the SQL Server needs to be restarted. Running sp_who2, based on sp_who but with a lot more information, can help you determine if you have a "hung" process as it has a LastBatch column which shows the time of the last batch run by that SPID. If it is many hours/days old you porbably have a problem there.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 20, 2007 at 6:03 am
I've posted over in the SQL 2000 forum.
Basically it looks like a hung proc. I've restared SQL and everything is hunky dory.
Thx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply