Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Are sleeping transactions are really sleeping


Are sleeping transactions are really sleeping

Author
Message
MichaelJasson
MichaelJasson
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 191
My server has gone down and is very slow at this moment. One log file has increased more than 76GB and I have found following error when I try to shrink this file. This is because of some offending process that is also using the resources which I need to shrink the file.

"Lock request timeout period exceeded."

I have also run sp_who2. I have found that many of the transactions are sleeping. What does sleeping means? If transaction is in sleeping should I kill this. Long back I remember that I killed one spid where the status was sleeping and this started getting rolled back for 5 hours and then I have to restart the m\c. Any heads up!!

-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
David Sorauer
David Sorauer
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 553
Don't kill any dormant processes (only as a last resort). There is a reason SQL doesn't clean them up itself...

It looks to me that one your queries could be missing a COMMIT statement...this often causes a large transaction log (if the cause isn't otherwise obvious).
MichaelJasson
MichaelJasson
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 191
So the status is sleeping when the process is waiting for resources for long... Is there any time period which switch this from running to sleeping status, David.

-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
MANU-J.
MANU-J.
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1712 Visits: 8766
Try by running dbcc opentran() under that database and see the oldest transaction. Check what spid it has and whts it doing in background(might be a old left out connection due to open mgmt. studio). See if you can kill it as it might be holding your log.


MJ
marty streetman
marty streetman
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 87
if you have a small group with query analyzer access, have them shut down their analyzer windows one at a time and you'll likely see one say something about 'uncommitted transactions' on the way out. that'll force it to commit. had to do that yesterday with a db saying its in transition. couldnt run sp_who or anything. went down there and had both contractors shut down all their windows and exit management studio, problem solved.

if that doesnt work, dbcc opentran, and you can see what it's doing with sp_lock(spid). it'll probably sit there looking the same forever. kill (spid) will kill it.

still no guarantee that it's not gonna rollback for awhile if you dont know what it was doing.
luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 659
Hello marty streetman,

I don't think that your statement is correct in your first paragraph. This can never happen that sql query analyzer or management studio is causing open transaction issues...

-LK
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search