February 19, 2007 at 5:43 pm
This is not strictly SQL related, but I'm hoping some of you will be Windows gurus as well as SQL gurus...
Our ERP, which is the main resident on our network, runs through three servers:
Server1: Citrix (serves all ERP clients)
Server2: "Utility" server, which acts as a go-between from clients to the SQL Server, using MSMQ
Server3: SQL Server
We regularly get deadlocks and more seriously we've had some General Ledger transactions just "go missing" without being posted. All these servers are quite well specified and our network only serves up to 10 clients at once. The servers communicate over a dedicated D-Link gigabit switch. All servers run Win2003 SP1. The SQL Server runs SQL2000 SP4.
I realise this is a bit of a "needle in a haystack" issue but I'm convinced it's a hardware/SQL/OS problem (i.e. it's not a bug in our ERP). Hopefully someone can lend a hand here, and I realise it's not strictly SQL Server.
Can someone please lend some advice on how I can setup Performance Monitor, SQL Profiler and perhaps other tools to record SQL/network/CPU activity on these three servers, specifically looking for indicators that would cause transactions not to be posted to SQL (either dropped at the SQL server or not sent from the Utility server) and causes of deadlocks (which are shown to the client as SQL Server error messages)? The logs will need to be recorded for up to a few months at a time so hopefully won't be too prolific. If you can point me to some good resources on the matter I'd be most appreciative.
Thanks in advance,
Sam
February 20, 2007 at 5:59 am
I'm sorry but experience tells me that the statement ( it's not a bug in our ERP ) is missguided, especially as you're getting deadlocks which are an application issue. I'd suggest that your application does not handle deadlocks correctly which is why you're losing data.
Deadlocks are generally caused by poor coding. There are trace flags which will enable you to store in the sql log details of the deadlocks. msdn site has a series of procs and code you can use to monitor waits and bloking - I have modified routines I use. Profiler and/or configured alerts will also help. - you can alert for errors and capture within profiler.
I'd seriously suggest you look at the application, it's highly unlikely to be what you suggest!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply