| Author |
Topic |
|
apsogos
Starting Member
12 Posts |
 Posted - 2013-03-15 : 08:21:54
|
| Hello ,I am new to this forum and happy to find you.At my company we use an erp program that has the base on sql 2008 r2.Lately many times when a user tries to save something to sql the program stops responding.The sql service seems to work fine....and when i open the program for a second time to the same pc or from another pc , i can read data but when i try to save something, the program stops responding until a) restart the sql service or b)change the server sql network card speed eg from 1.0 gb to 100 mb and the other way.Of course (after this) on the program, pop up "sql error" but when i press a refresh button inside the program 2 times (so it rebuilds the connection) it work fine till it happends again.the network seems to work fine....i have copy the sql base to another machine 3 days now but the problem happends again.P.SThis situation is not happening everyday for 100% maybe it happends 10 times maybe not at all.But it lately it doesPlease help me!Sorry for my bad english JohnSpecsserver a) 2 xeon 3.2 hyperthreading 2 gb ram - windows 2003 datacenter ed. 32 bitb) windows 7 32 bit, Amd X2 3.2 ghz , 4gb ram (3usable)sql 2008 r2 32 bit |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-03-16 : 15:32:19
|
| What is the error? have you monitored on the sql server , for example have you checked for blocking , or other types of delays that may be occur. Also, have you checked application logs , to see whether there is a timeout ?Jack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
apsogos
Starting Member
12 Posts |
Posted - 2013-03-19 : 08:47:05
|
quote:
Originally posted by jackv
What is the error? have you monitored on the sql server , for example have you checked for blocking , or other types of delays that may be occur. Also, have you checked application logs , to see whether there is a timeout ?Jack Vamvas--------------------http://www.sqlserver-dba.com
there is no error ,neither at event log :( |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-03-20 : 03:06:16
|
| have you monitored the SQL Server to check for any blocking ? or other types of issues that may be causing delaysJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
apsogos
Starting Member
12 Posts |
Posted - 2013-03-20 : 04:53:34
|
quote:
Originally posted by jackv
have you monitored the SQL Server to check for any blocking ? or other types of issues that may be causing delaysJack Vamvas--------------------http://www.sqlserver-dba.com
i found out the log file status is not right....[URL=http://imageshack.us/photo/my-images/833/slqphoto.jpg/] [/URL]how can i fix this?is there a change my problem come from this situation ? |
|
|
|
apsogos
Starting Member
12 Posts |
Posted - 2013-03-20 : 09:37:28
|
[URL=http://imageshack.us/photo/my-images/259/activitymonitor.jpg/] [/URL]this my activity monitor ...where and what should i check? |
|
|
|
apsogos
Starting Member
12 Posts |
Posted - 2013-03-20 : 10:15:08
|
| i repeat when this happends....i set another speed on my server network card eg from 10 half duplex to 100 full,or eg from 1gb to 100 full...and after that the erp aplication works again.....psthe machine that runs the sql now is another one , the sql ran on another machine 5 days ago and i changed it.the symptoms were the same! |
|
|
|
apsogos
Starting Member
12 Posts |
Posted - 2013-03-20 : 10:21:28
|
[URL=http://imageshack.us/photo/my-images/23/46623809.jpg/] [/URL][URL=http://imageshack.us/photo/my-images/547/19044797.jpg/] [/URL] |
|
|
|
apsogos
Starting Member
12 Posts |
Posted - 2013-05-12 : 10:38:32
|
| I have replace all the network switches and most of the cables...Probably the problem might not be a network problem at last.There were days that the problem did not appear but finally it happend again.One of the scsi disks of the server is off ..I removed it a week ago and I expect to receive the new one next week.The server is with 4gb ram windows 2003 dataserver ...sql 2008 r2 sp2 and the hardwares is proliant 360 g4 with 2 xeon 3.0ghz dual core.I recently enabled the /pae switch for the OS to see all the 4gb of ram and set the virtual ram to 6100 something mb min and 18000 something mb for max...I understand all this made the system use too much page file.So I removed the /pae switch and finally use the /3gb with virtual memory 4096 min and 4096 max.I ve updated the database statistics too.Let me know what you thing |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-05-13 : 01:54:39
|
| As you have only 4gb memory - setting the /3gb switch may starve your applications of memory. Be carefull with this switchJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
apsogos
Starting Member
12 Posts |
Posted - 2013-05-13 : 06:11:17
|
quote:
Originally posted by jackv
As you have only 4gb memory - setting the /3gb switch may starve your applications of memory. Be carefull with this switchJack Vamvas--------------------http://www.sqlserver-dba.com
what else should i use?The server is only for sql - erp using purpopse |
|
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2013-05-13 : 10:24:31
|
| - did you run DBCC CheckDB for view if it's not an issue with DB- it's recovery Full ? backup of DB and of Log at what interval ?- autogrow by procent , try to change to an fix number like 100MB or whatever you thinkCe-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-05-13 : 11:23:37
|
| a couple of other options are :1) run a trace and see what queries are running at the time?2) have you interogated the default trace - which tracks all sorts of events - read this FAQ on how to get started on the default trace. For example - you can view if there is a heavy memory spike, log auto grows etc - http://www.sqlserver-dba.com/2012/04/sql-server-default-trace-faq.htmlJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2013-05-13 : 11:23:54
|
I think I know what's happening.When you change the network speeds what's happening is that connections are dropped. Therefore the machine that was trying to 'save' has it's connection terminated and goes on with it's life.I imagine what is happening is that the system is encountering locking / blocking issues and the is constantly waiting for the connection to finish.Next time this happens I suggest that you log onto the sql server instance with management studio and runEXEC sp_who2 Look for blocked jobs -- you'll probably see some - you can then start debugging...Many people have posted job execution status scripts -- here's mine:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=167134Try it next time you get a hang -- you may be able to see what's going on.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
apsogos
Starting Member
12 Posts |
Posted - 2013-05-13 : 11:53:08
|
I want to thank all of you....I am sending you some picture for a start to answer some questions:[URL=http://imageshack.us/photo/my-images/607/56040013.jpg/] [/URL][URL=http://imageshack.us/photo/my-images/259/56996623.jpg/] [/URL][URL=http://imageshack.us/photo/my-images/585/44204249.jpg/] [/URL]The thing with 4 tempdb files , i did this weekend...i red somewhere that it helps perfomancei had run dbcc checkdb and no erros |
|
|
|
apsogos
Starting Member
12 Posts |
Posted - 2013-05-13 : 12:50:52
|
quote:
Originally posted by Transact Charlie
I think I know what's happening.When you change the network speeds what's happening is that connections are dropped. Therefore the machine that was trying to 'save' has it's connection terminated and goes on with it's life.I imagine what is happening is that the system is encountering locking / blocking issues and the is constantly waiting for the connection to finish.Next time this happens I suggest that you log onto the sql server instance with management studio and runEXEC sp_who2 Look for blocked jobs -- you'll probably see some - you can then start debugging...Many people have posted job execution status scripts -- here's mine:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=167134Try it next time you get a hang -- you may be able to see what's going on.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/
On the images i had sent above i saw some blocked ids ....correct if i am wrong.If i had the activity monitor on again.If this is my problem , how can i fix this permanently? |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-05-14 : 04:12:12
|
| There are certain session id's that are blocking your queries, kill the sessions that were blocking other sessions with kill sesionid command(e.g. kill 53).. However, transaction log can she shrunk to the minimum using DBCC Shrinkfile and also it won't effect in any way for your posted problem. |
|
|
|
apsogos
Starting Member
12 Posts |
Posted - 2013-05-14 : 05:14:12
|
quote:
Originally posted by srimami
There are certain session id's that are blocking your queries, kill the sessions that were blocking other sessions with kill sesionid command(e.g. kill 53).. However, transaction log can she shrunk to the minimum using DBCC Shrinkfile and also it won't effect in any way for your posted problem.
Good morning,a)lets say that next time i will find the block session I ll kill them. The problem will resolved permanately?(if no how i will resolve the problem permanately?b)I have enabled the simple recovery model so the log file is only 57 mb and i dont think that is increacing...so why you suggest me to shrink the log file? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2013-05-14 : 08:33:58
|
It's very hard to say for sure. It could be something as mundane as missing indexes, or poorly performing inserts / updates.did you try running the script I posted?If you do you should be able to see what processes are blocked by which other processes. Also you should be able to copy out the sql text of those jobs and see what they are trying to do.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
apsogos
Starting Member
12 Posts |
Posted - 2013-05-14 : 12:16:18
|
quote:
Originally posted by Transact Charlie
It's very hard to say for sure. It could be something as mundane as missing indexes, or poorly performing inserts / updates.did you try running the script I posted?If you do you should be able to see what processes are blocked by which other processes. Also you should be able to copy out the sql text of those jobs and see what they are trying to do.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/
i tried to run your script but on sql and error appears:incorrect syntax near '.'. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Next Page
|
Sql 2008 r2 "freeze" unexpected