SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Sql 2008 r2 "freeze" unexpected
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

apsogos
Starting Member

Greece
12 Posts

Posted - 03/15/2013 :  08:21:54  Show Profile  Reply with Quote
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


Edited by - apsogos on 03/15/2013 08:27:14

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2011 Posts

Posted - 03/16/2013 :  15:32:19  Show Profile  Visit jackv's Homepage  Reply with Quote
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

Greece
12 Posts

Posted - 03/19/2013 :  08:47:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2011 Posts

Posted - 03/20/2013 :  03:06:16  Show Profile  Visit jackv's Homepage  Reply with Quote
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

Greece
12 Posts

Posted - 03/20/2013 :  04:53:34  Show Profile  Reply with Quote
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

Greece
12 Posts

Posted - 03/20/2013 :  09:37:28  Show Profile  Reply with Quote
[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

Greece
12 Posts

Posted - 03/20/2013 :  10:15:08  Show Profile  Reply with Quote
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!

Edited by - apsogos on 03/20/2013 10:20:32
Go to Top of Page

apsogos
Starting Member

Greece
12 Posts

Posted - 03/20/2013 :  10:21:28  Show Profile  Reply with Quote
[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

Greece
12 Posts

Posted - 05/12/2013 :  10:38:32  Show Profile  Reply with Quote
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

Edited by - apsogos on 05/12/2013 10:48:35
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2011 Posts

Posted - 05/13/2013 :  01:54:39  Show Profile  Visit jackv's Homepage  Reply with Quote
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

Greece
12 Posts

Posted - 05/13/2013 :  06:11:17  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
403 Posts

Posted - 05/13/2013 :  10:24:31  Show Profile  Reply with Quote
- 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
Flowing Fount of Yak Knowledge

United Kingdom
2011 Posts

Posted - 05/13/2013 :  11:23:37  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/13/2013 :  11:23:54  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/

Edited by - Transact Charlie on 05/13/2013 11:24:37
Go to Top of Page

apsogos
Starting Member

Greece
12 Posts

Posted - 05/13/2013 :  11:53:08  Show Profile  Reply with Quote
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


Edited by - apsogos on 05/13/2013 12:05:38
Go to Top of Page

apsogos
Starting Member

Greece
12 Posts

Posted - 05/13/2013 :  12:50:52  Show Profile  Reply with Quote
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 - 05/14/2013 :  04:12:12  Show Profile  Reply with Quote
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

Greece
12 Posts

Posted - 05/14/2013 :  05:14:12  Show Profile  Reply with Quote
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?


Edited by - apsogos on 05/14/2013 05:34:46
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/14/2013 :  08:33:58  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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

Greece
12 Posts

Posted - 05/14/2013 :  12:16:18  Show Profile  Reply with Quote
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

USA
36674 Posts

Posted - 05/14/2013 :  15:39:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000