Database hang after some time

Question

Post reply

Database hang after some time

azhar.iqbal499

SSCrazy

Points: 2812
More actions
April 19, 2011 at 5:13 am

#235020

At early stage my all procedures run well but after almost haft hour stored procedures which usually take 10 minute, take more than half hour and db become too slow and at later stage did not produce any result.To overcome this situation I restart SQL Service and then situation become normal again. I am using SQL Server 2008 64 bit with window server 2008 64 bit .
Task manager of window held maximum memory and does not release even I kill some processes. Situation was better when we were using window server 2003 32 bit with SQL Server 2008 32 bit . Is There any integration problem of SQL Server 2008 with Window Server 2008?
Thanks in Advance.
Azhar

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 1

1-Check this SP_WHO2 for Processes which those are going to hang as on runnable state or suspended

2-Query

SELECT st.text,sp.* from sys.sysprocesses sp

cross apply sys.dm_exec_sql_text(sp.sql_handle) st

order by sp.cpu desc

TOTAL CPU physical and logical

TOTAL RAM

TOTAL MAXIMUM THREAD

TOTAL SQL SERVER RAM

TOTAL HDD or Using SAN

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog

http://www.aureus-salah.com

My Blog
www.aureus-salah.com

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 2

There are a couple of KB articles related to these symptom's. I had similar symptoms on an AMD cpu cluster and you can see the conversation in the below thread.

http://support.microsoft.com/?id=983460

http://support.microsoft.com/?id=979149

http://www.sqlservercentral.com/Forums/Topic826976-360-1.aspx

azhar.iqbal499 SSCrazy Points: 2812 More actions · Answer 3

Now, I am running MS SQL Server 2008 R2 on window Server 2003 Enterprise R 2 64 bit.

Total RAM= 8GB

MS SQL Server RAM=6.4 GB

Total Hard Disk=408 GB

When I execute the report from application it halt and respond very poorly but when I execute the same query in Query Editor using EXEC Proc Command it respond well and give result in acceptable time.

is there any problem at Application level or DB Problem?

Thanks

Azhar Iqbal

DBA PRSP LAhore

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 4

is your application Server is another than SQL server,is there network medium betweek these servers