Avatar of SteveSext
SteveSext
 asked on

Windows Server 2008 32 bit and SQL server 2005 optimisation

Hi
I have a Windows Server 2008 32 bit server running SQL server. SQL server regularly crashes and needs to be restarted. i.e. Sql replication jobs fail, timeouts are experinced by users. Once restarted, it all works fine again.
Does anyone know of any good SQL optimisation tricks that need to be added to optimise performance. In winodws 2003, I used /3gb /pae but I bellieve this has changed in windows server 2008.
Has anyone got any tips on this?
thanks
Microsoft SQL Server 2005Windows Server 2008

Avatar of undefined
Last Comment
SteveSext

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
lnkevin

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
mastoo

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
arnold

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SteveSext

ASKER
There is 4GB physical RAM installed. SQL becomes unresponsive and client applications start getting timeout errors. No errors in teh SQL error log, the only error in the windows event log:
the connection has been lost with Microsoft Distributed Transaction Coordinator (MSDTC). Recovery of any in-doubt distributed transactions involving Microsoft Distributed Coordinator (MSDTC) will begin once teh connection is re-established.
Error 8562.
arnold

Are there other event logs leading up to this one that shed light why MSDTC becomes unresponsive?
Is this a single SQL server or is it part of a cluster?
SteveSext

ASKER
the only other event is "string message: INFORMATIONAL : CSO::Maintain_SSTATE_UP session Failed Error = 0x80004005 RpcStatus = 164", Event id 4156
it is not a cluster
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
arnold

I have not seen this type of error. you might want to follow the suggestion from mastoo to see whether you have an issue.
Is this a web application that uses the DB or is it a client application running on each workstation?
Anthony Perkins

I may have missed it, but you are not running anything else (God forbid IIS) on the same server as MS SQL Server are you?
SteveSext

ASKER
no not running iis on this machine
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Anthony Perkins

So it is running standalone on this box?
SteveSext

ASKER
yes, it is a sql server, windows server 2008 32 bit and sql server standard 2005
Anthony Perkins

If MS SQL Server is all that is installed on that box, then I honestly have no idea what is going on.  We have servers with as little as 4GB with hundreds of databases that do not have that problem.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SteveSext

ASKER
USERVA seems to work