May 7, 2009 at 10:35 pm
Dear Experts,
Iam facing issues on server memory utilization and many other.
If i check the activity monitor i can see many number of users (ex:80 user processes) in sleeping status for long time and they even dont release or end the process. When i check the server memory and connections i used to kill the processes which are in slleping status for long time to resolve the error count for our application.
Is there anyway to disconnect the sleeping status users by settings are any other options.
When the user process is in sleeping status for 2 mins, it must be disconnected and the memory should be released. This is my intention to make instead doing manually of killing the processes..
Please let me know how this is possible...
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 8, 2009 at 6:38 am
If you are very much sure that you want to kill those spids which are in sleeping state and where waittime is more then 2 mins, you can automate this process using a job and schedule it as per your requirement.
Create the following stored proc and call it in the SQL job.
create procedure KillIdleSpids
as
--Collecting the Spid information to a temp table from sysprocesses
select waittime,spid into #temp from sysprocesses where waittime>=120000 --(2 mins in milli seconds)
and status like 'sleeping'
-- declare a cursor to fetch the spid from the temp table
declare @spid int(10),@cmd char(50)
DECLARE Spid_Cursor CURSOR FOR
SELECT spid
FROM #temp
OPEN Spid_Cursor
FETCH NEXT FROM Spid_Cursor into @spid
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd= 'Kill' + convert( char(4),@spid)
print @cmd -- Kill the spid
FETCH NEXT FROM Spid_Cursor into @spid
END
CLOSE Spid_Cursor
DEALLOCATE Spid_Cursor
Hope this should help you.
-Rajini
May 8, 2009 at 6:46 am
Oh,,.................great
[font="Comic Sans MS"]+++BLADE+++[/font]:cool:
May 11, 2009 at 12:02 am
Great Rajini....
I tried with Local machine and DEV env now trying to TEST that in UAT...
i Can set that time to 5 mins right ???
Thats what am testing now....
I will let u know if i face any issues.....
Its a great thing and thanks much for ur HELP.... 🙂
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 11, 2009 at 12:38 am
Dear Rajini,
Thanks fro your inputs, but am facing the below error with the script provided by you...
Msg 2716, Level 16, State 1, Line 19
Column, parameter, or variable #1: Cannot specify a column width on data type int.
I have changed that to only int (where it is int(10)) in the script.
How ever i tried this but i can see the sleeping connections still exsting in the Activity Monitor... view processes...
On that day i tried on mt local DEV machine it was ok that time, since i have monitored that only for 10 mins.
is this script should be re-run everytime....(i mean the procedure should be re-run) or what ???
i have not created a job. but executed SP manually (KillIdleSpids). The processes of SLEEPING status remains same....
Please let me know if anything else to be clarified.... Help me in this ASAP...
As from today am seeing SLEEPING status IDs of 155 out 163...
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 11, 2009 at 12:41 am
select waittime,spid from sysprocesses where waittime >= 120000 --(2 mins in milli seconds)
and status like 'sleeping'
If i run the above query i can see no results. As i can see 158 sleeping status IDs in current activity monitor...
Please help...
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 11, 2009 at 3:56 am
Hi,
lets go one by one:
>> I Can set that time to 5 mins right ??? - Yes, 5 mins should be resonable time to schedule the job.
>> is this script should be re-run everytime - I ahve done few modification to the below code. You have to just include this in a job and schedule the job to run every 5 mins. This should not throw you any error.
select waittime,spid into #temp from sysprocesses where waittime>=120000 --(2 mins in milli seconds)
and status like 'sleeping'
-- declare a cursor to fetch the spid from the temp table
declare @spid int(10),@cmd char(50)
DECLARE Spid_Cursor CURSOR FOR
SELECT spid
FROM #temp
OPEN Spid_Cursor
FETCH NEXT FROM Spid_Cursor into @spid
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd= 'Kill' + convert( char(4),@spid)
print @cmd -- Kill the spid
FETCH NEXT FROM Spid_Cursor into @spid
END
CLOSE Spid_Cursor
DEALLOCATE Spid_Cursor
Drop table #temp - drop the temp table
>> If i run the above query i can see no results. As i can see 158 sleeping status IDs in current activity monitor - if the spids are having less than 2 mins of waitime i.e 120000 millisecs and in sleeping state, then also we wont see any result for the above query, becuase it does not satisfy the condition for waittimer.
Also there can also be a possibility that the spids are just hanging on the server withour any waittime ,however where the last batch could be older than two days or more.
spid Waittime last_batch Status
80 2009-05-07 12:03:45.823 sleeping
100 2009-05-07 12:03:45.823 sleeping
160 2009-05-07 12:03:45.823 sleeping
180 2009-05-07 12:03:45.823 sleeping
if you see the above results, though the waitime is '0' the spid is lying there doing nothing for more than a week. So if you want to identify these as well and remove it then I can modify the scritp accordingly..
Please let me know your inputs on these.
-Rajini
May 11, 2009 at 4:23 am
Thanks Rajini... for the reply..
Yes i agree with your things.. I want to kill the processes for long time hanging.
Main intention is to kill the process which simply kept idle for long time,
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 11, 2009 at 7:58 am
Also you have to keep a point in mind that, all spids less than 50 are system spids running in backgroud. Which are idle for most of the time, but would be active and needed in the backgroud. So always better not to meddle with these. So let us concentrate on those Spids which are greater then 50. Use the below query and see if it works. I would prefer putting this code in a job and scheduling it every 5 mins.
Use this query:
select waittime,spid,dbid,[status],last_batch from sysprocesses
where waittime>=120000 and spid > 50 --(2 mins in milli seconds)
or datediff(day,last_batch,getdate()) >= 4
and [status] like '%sleeping%'
-- declare a cursor to fetch the spid from the temp table
declare @spid int(10),@cmd char(50)
DECLARE Spid_Cursor CURSOR FOR
SELECT spid
FROM #temp
OPEN Spid_Cursor
FETCH NEXT FROM Spid_Cursor into @spid
WHILE @@FETCH_STATUS = 0
BEGIN
if @spid> 50
begin
set @cmd= 'Kill' + convert( char(4),@spid)
print @cmd -- Kill the spid
end
FETCH NEXT FROM Spid_Cursor into @spid
END
CLOSE Spid_Cursor
DEALLOCATE Spid_Cursor
Drop table #temp - drop the temp table
let me know if you run into any issue.
-Rajini
May 11, 2009 at 10:11 am
SQL-KV (5/7/2009)
Dear Experts,Iam facing issues on server memory utilization and many other.
If i check the activity monitor i can see many number of users (ex:80 user processes) in sleeping status for long time and they even dont release or end the process. When i check the server memory and connections i used to kill the processes which are in slleping status for long time to resolve the error count for our application.
Is there anyway to disconnect the sleeping status users by settings are any other options.
When the user process is in sleeping status for 2 mins, it must be disconnected and the memory should be released. This is my intention to make instead doing manually of killing the processes..
Please let me know how this is possible...
Personally, I'd recommend that instead of putting a hacked up bandaid like a SQL Agent job to kill connections in place, that you fix the application problem that is leaving these "orphaned" connections to the database open. Ideally, your application would be using thread pooling to keep its connection count down. What language is the application written in? The problem is not with SQL Server, and trying to fix it there is only a short term hack at best.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
May 11, 2009 at 10:30 pm
Thanks for the reply,
Actually we are facing the issues like : TECH_MESSAGES like,
1. Object reference not set to an instance of an object.
2. Save Profile Failed Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
3. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
4. The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar.
5. The operation has timed out
As per the above errors Server is not responding for completing the process.
My aplication is in .NET and we are facing these timedout errors in 50+ TO 70+ NUMBER daily....
And my server CPU Utilization is reaching 100% many times... At that point of tno connections will be made right ????
Machine is :
64 BIT SQL Server 2005 on WIN 2003 64BIT ;
12 GB RAM - of which SQL Server is using 11.1 GB at any point of time (as 10GB - SQL and 2GB - CPU)-
Still am unable to understand 64 Bit will support more than 500+ connections (i hope so). If i can reduce (fix) these errors then am going to be a good DBA in my Org. Please put your inputs to keep these issues fixed.....
Can any one has any ideas .. please let me know if anything else to be provided for clarification....
Fast help is Appreciated........
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 12, 2009 at 7:58 am
Help coming.
Don't kill sleeping SPIDs if they are more then 2000-3000... You have enough memory and sleeping SPIDs are not always bad by themselves. Read on it in this forum or other placed. Sleeping users usually comes from connection pooling (good) or because they are just not using the connection.
Improve your application. 90%+ of all issues are App related. I do very forcefully believe this is an App problem looking at the errors you are getting.
Look into indexing away your table scans.
Reduce number of cartesian joins.
You machine seems more then adequate for a mere 80 users even if they were NEVER in a sleeping state. Are your env. OLAP or OLTP based?
May 12, 2009 at 11:12 pm
Thanks Hans,
But still am not able to fix these issues or errors.
Its OLAP machine. Where we configured for Log Shipping process for 8 databases.
Can anyone provide me the exact or prior solution how to fix these errors. Any changes to be done at server level or application level.
Help required fast....!!!!
Cheers,
- Win
"Dont Judge a Book by its Cover"
May 13, 2009 at 1:17 am
Have you actually looked into anything of the above? Indexing for example? Eliminating Full Scans?
May 13, 2009 at 1:31 am
I agree with Jonathan, you are resolving problem on the wrong place 🙁
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply