February 3, 2010 at 9:53 am
hello there, im not familiar with sql so please help me
our server is windows server 2008 standard edition, 4gb ram, 2x250gb hdd raid
with sql server 2005
the problem is,
after 3 to 5 days the server is getting slow or worse user cant connect at all,
physical memory is stack at 95% , the sqlserver.exe process in task manager goes up to 2,000 something
then the server become slow after the sqlserver.exe process in task manager is decreasing until it reaches to 400 going down
connecting to the system is very slow, normal operation after we restart the server
try the max and min option but to no avail
max memory=2000
min memory=48
please help me, every 3 days we have to restart the server
thank you
February 3, 2010 at 12:36 pm
What else is on this server, it sounds like SQL is being forced to page out to virtual memory. What edition of SQL Server? 32 or 64-bit?
CEWII
February 3, 2010 at 7:06 pm
sql server 2005 64 bit , standard edition
thanks for the reply
pls help me
February 3, 2010 at 7:23 pm
There are various things to look here though I will first try to see whats taking the memory so lets try this....
Run below statement, it will give you exactly what queries are running for long and which are the blocked ones...keep it running and keep an eye on difftime and blocked columns, and attack the ones where difftime is more than 60 sec and open tran is more than ZERO...these are the ones usually slowing down the system or creating blocking for others...
if you see blockers sessions are not getting released then kill them manually and try to find out whats causing this...
select
SUBSTRING(qt.text, (b.stmt_start/2)+1,
((CASE b.stmt_end
WHEN -1 THEN DATALENGTH(qt.text)
WHEN 0 THEN DATALENGTH(qt.text)
ELSE b.stmt_end
END - b.stmt_start)/2) + 1) AS QueryExecuted,
getdate(),datediff(s,last_batch,getdate()) as difftime,
last_batch,*
from sys.sysprocesses b
outer apply sys.dm_exec_sql_text(b.sql_handle) as qt
where open_tran>0
or blocked >0
order by b.last_batch
Share your findings, to ascertain further...
Note: this statement will run only with Sql 2k5 with SP2 and onwards...
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
February 3, 2010 at 7:55 pm
I'm going with Elliot on this one. It sounds like a classic memory leak either caused by an app, a CLR (whatever you want to call it), or perhaps some improper usage of sp_OA* stored procedures.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2010 at 11:02 pm
the bigger in difftime is 16160, if i upgrade to 4gb is it possible solve my problem?
February 3, 2010 at 11:17 pm
carloparcon (2/3/2010)
the bigger in difftime is 16160, if i upgrade to 4gb is it possible solve my problem?
More than likely not. As Jeff and Elliott said - it seems that there is an app or process that is going crazy in your system / possible memory leak. Your best bet would be to find what is causing the problem. Upgraded hardware only delays finding the problem.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2010 at 6:20 am
ok but what if i reformat the whole thing?
February 4, 2010 at 8:57 am
carloparcon (2/4/2010)
ok but what if i reformat the whole thing?
could you clarify?
Reformat, to me, means that you will erase the hard drive.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2010 at 9:18 am
I'm going back to my original question that has yet to be answered, "What else is on this server?"
Also, do you use SQLCLR or sp_OA at ALL?
As others have stated (and agreed) it feels like a memory leak, we need to find where.
CEWII
February 4, 2010 at 1:01 pm
CirquedeSQLeil (2/3/2010)
carloparcon (2/3/2010)
the bigger in difftime is 16160, if i upgrade to 4gb is it possible solve my problem?More than likely not. As Jeff and Elliott said - it seems that there is an app or process that is going crazy in your system / possible memory leak. Your best bet would be to find what is causing the problem. Upgraded hardware only delays finding the problem.
Agree with Jason here, 16160 is crazy, anything more than 100 is bad, can you post the output of queryresult column and actuallt if you can format the out of this query and post it somewhere in html table link so that can be analysed...
Meanwhile keep killing these big diff sessions to have system running fine, but make sure its not an activity which gets into a bigg rollback...
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
February 4, 2010 at 6:15 pm
yes reformat everything
February 4, 2010 at 6:17 pm
server 2008 standard edition 64 bit, sql server standard 64bit, kaspersky enterprise for windows server, thats it
im quite new in sql environment,
what do you mean sp_oa?clr? im sorry, could you explain it to me
February 4, 2010 at 6:20 pm
to prakash, i cant kill these process coz its being use by our Front desk cashier, you know were in a hotel industry
February 4, 2010 at 6:21 pm
to prakash, i'll give you the query results later, thanks
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply