Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Sql 2008 r2 "freeze" unexpected

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.S
This situation is not happening everyday for 100% maybe it happends 10 times maybe not at all.
But it lately it does

Please help me!

Sorry for my bad english

John

Specs
server a) 2 xeon 3.2 hyperthreading 2 gb ram - windows 2003 datacenter ed. 32 bit
b) 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
Go to Top of Page

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 :(
Go to Top of Page

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 delays

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

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 delays

Jack 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 ?
Go to Top of Page

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?

Go to Top of Page

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.....

ps

the 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!
Go to Top of Page

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]

Go to Top of Page

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
Go to Top of Page

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 switch

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

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 switch

Jack Vamvas
--------------------
http://www.sqlserver-dba.com



what else should i use?
The server is only for sql - erp using purpopse
Go to Top of Page

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 think

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

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.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

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 run

EXEC 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=167134

Try it next time you get a hang -- you may be able to see what's going on.

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

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 perfomance

i had run dbcc checkdb and no erros

Go to Top of Page

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 run

EXEC 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=167134

Try it next time you get a hang -- you may be able to see what's going on.

Transact Charlie
Msg 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?
Go to Top of Page

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.
Go to Top of Page

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?

Go to Top of Page

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 Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

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 Charlie
Msg 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 '.'.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-05-14 : 15:39:22
Your tempdb data files need to be the same size.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
    Next Page

- Advertisement -