April 4, 2011 at 2:31 am
This may be a hard one to explain so please bear with me.
We have two instances on our SQL 2005 setup, DB1 and DB2 to name them. Each instance have the exact same databases / tables. When data is updated in DB2 it also shows when we run it in DB1, it is updated in both instances at the same time.
DB2 shows that it has a SQL service, but DB1 does not.
I am not sure as to who set this up, but the suspect has now left our employ so we are unable to confirm what has happened.
We are running this on a vritual machine and have had to update the memero from 2gb to 8gb and yet the system is between 98% and 100% most of the day.
Any ideas?
April 4, 2011 at 3:04 am
We have two instances on our SQL 2005 setup, DB1 and DB2 to name them. Each instance have the exact same databases / tables. When data is updated in DB2 it also shows when we run it in DB1, it is updated in both instances at the same time.
What does this? Replication? Triggers?
DB2 shows that it has a SQL service, but DB1 does not.
Where does it "show" a SQL service? Management Studio?
We are running this on a vritual machine and have had to update the memero from 2gb to 8gb and yet the system is between 98% and 100% most of the day.
100% of what? Memory?
What is exactly your issue? Bad performance?
-- Gianluca Sartori
April 4, 2011 at 3:25 am
Gianluca Sartori (4/4/2011)
We have two instances on our SQL 2005 setup, DB1 and DB2 to name them. Each instance have the exact same databases / tables. When data is updated in DB2 it also shows when we run it in DB1, it is updated in both instances at the same time.
What does this? Replication? Triggers?
No replication that I can see
DB2 shows that it has a SQL service, but DB1 does not.
Where does it "show" a SQL service? Management Studio?
No, Computer management
We are running this on a vritual machine and have had to update the memero from 2gb to 8gb and yet the system is between 98% and 100% most of the day.
100% of what? Memory?
CPU Usage and Physical Memory
Performance is not good but for me it is understanding what that secodn version of the DBases is doing.
What is exactly your issue? Bad performance?
April 4, 2011 at 3:34 am
OK, let's start from the obvious: if there's a SQL Server you can connect to on DB1, there must be an instance of SQL Server running. I don't know why it doesn't show up in computer management, but I wouldn't check there. Try opening SSMS instead and gather some information on the instance.
How did you check if there's a replication in place?
On the performance side, which instance is suffering from bad performance? DB1 or DB2?
-- Gianluca Sartori
April 4, 2011 at 4:01 am
Is DB1 a mirror of DB2? Go into SSMS -> connect to DB1 & 2, navigate out to Server Objects->Endpoints->Mirroring endpoints.
The other possibility is that you have log shipping configured, and that is configured to be almost instantaneous. Try increasing the delay in log shipping if that is the case.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
April 4, 2011 at 4:12 am
Gianluca Sartori (4/4/2011)
OK, let's start from the obvious: if there's a SQL Server you can connect to on DB1, there must be an instance of SQL Server running. I don't know why it doesn't show up in computer management, but I wouldn't check there. Try opening SSMS instead and gather some information on the instance.How did you check if there's a replication in place?
I looked at the Local Publications and Subscriptions
On the performance side, which instance is suffering from bad performance? DB1 or DB2?
Well thats it, from what I find they are both the same database instance.
April 4, 2011 at 4:15 am
Nakul Vachhrajani (4/4/2011)
Is DB1 a mirror of DB2? Go into SSMS -> connect to DB1 & 2, navigate out to Server Objects->Endpoints->Mirroring endpoints.Mirroring Endpoints show blank
The other possibility is that you have log shipping configured, and that is configured to be almost instantaneous. Try increasing the delay in log shipping if that is the case.
Log shipping on both are also blank
April 4, 2011 at 4:24 am
Well thats it, from what I find they are both the same database instance.
Does DB1 server have a process named "sqlserver.exe"? If not, no SQL Server instance is running.
Open DB1 and DB2 and issue "SELECT @@SERVERNAME". What does it return?
-- Gianluca Sartori
April 4, 2011 at 4:41 am
Gianluca Sartori (4/4/2011)
Well thats it, from what I find they are both the same database instance.
Does DB1 server have a process named "sqlserver.exe"? If not, no SQL Server instance is running.
There are two sqlserver.exe processes, one at 12mb and the other at 2gb
Open DB1 and DB2 and issue "SELECT @@SERVERNAME". What does it return?
Both return the same name
April 4, 2011 at 5:04 am
No wonder you cannot find DB1 as a separate server - both DB1 and DB2 are aliases of each other!
As far as the need for more memory is concerned - something else is wrong with your system. Chances are that even if you increase it to 8GB, you will still have the same issues. This is how you should address the problems:
1. Focus on whether or not high memory usage is an application code issue (are you using too many loops/RBARs in your SQL code?)
2. Is your SQL server & database configuration correct?
3. Then think about the hardware
A bad application coupled with a poorly configured SQL Server will consume as much hardware as you can feed it - and yet be poor on performance.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
April 4, 2011 at 6:11 am
Nakul Vachhrajani (4/4/2011)
No wonder you cannot find DB1 as a separate server - both DB1 and DB2 are aliases of each other!Is there anyway we can get rid of this "alias?"
As far as the need for more memory is concerned - something else is wrong with your system. Chances are that even if you increase it to 8GB, you will still have the same issues. This is how you should address the problems:
1. Focus on whether or not high memory usage is an application code issue (are you using too many loops/RBARs in your SQL code?)
Would running a "profiler" point this out for me?
2. Is your SQL server & database configuration correct?
This I will have to really look at.
3. Then think about the hardware
This is run in a VM environment so hardware problems couold stem from outside the SQL area I would imagine.
A bad application coupled with a poorly configured SQL Server will consume as much hardware as you can feed it - and yet be poor on performance.
April 4, 2011 at 7:55 am
The SQL Server Configuration Manager will help you get rid of the alias.
As far as pointing out code issues, yes, Profiler is one of the tools that you would use. It looks like you are one of those "accidental" DBAs (or a DBA and developer rolled into one), and hence woud seriously recommend that you read the basics of T-SQL best practices and then look for code patterns which violate them.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
April 4, 2011 at 8:22 am
Nakul Vachhrajani (4/4/2011)
The SQL Server Configuration Manager will help you get rid of the alias.As far as pointing out code issues, yes, Profiler is one of the tools that you would use. It looks like you are one of those "accidental" DBAs (or a DBA and developer rolled into one), and hence woud seriously recommend that you read the basics of T-SQL best practices and then look for code patterns which violate them.
Your right, I am certainly accidental. Thanks for your help, I will plod on as best I can
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply