Unable to access SQL Server
-
I'm unable to access one of my SQL Server instances. It won't let me connect as admin via SSMS
When I loogged into the Server it appears that SQL is maxing out the CPU as below:
In Windows Event Viewer one of the errors looks like this:
Should I just re-start the SQL Server and set a limit to maximum memory it is allowed to use?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
You should always have a max server memory setting to ensure that you don't starve the OS. There are varying opinions on how much you should leave. How much do you have total on the box?
IF you have the Dedicated Admin Connection enabled you could connect via that and change the setting without a restart of the instance. If you don't have that enabled, I would recommend doing that as well.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
There's 8GB memory. The SQL Server instance is configured to use the max (it still has the default value). But is task manager accurate enough about memory usage? It doesn't look like its using all the 8GB.
I will change it to 7gb when I get a chance. Not tried DAC but will try if my login attempts via SSMS keep failing.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
To get a TRUE view into how much memory SQL Server is using I would recommend reading this post - http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx
For a server with 8 GB of memory I would leave at least 2 GB of memory for the OS and any other things running on the box that are consuming memory outside of SQL Server. If this isn't a dedicated SQL box then I would leave even more memory.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Can't even use DAC. I get a timeout error message!
Time to re-start the SQL Server instance?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Yes, if you don't have DAC configured and no access to SQL Server then you have to restart the SQL Server service, unfortunately.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Attempting to re-start using SQL Server Configuration Manager but taking a very long time. Can I just kill the sqlservr.exe from task manager and start the service via SQL Server Configuration Manager?
Hope I don't have to re-start the entire server!
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
No, let it stop, don't kill the process. It will eventually stop.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Phew!!! .... I was so close to killing the process but then I stopped. I'm glad I didn't do it. I actually clicked the re-start button from the SQL Server Configuration Manager so hopefully it will come back online soon but I'm looking ta the CPU and it's still maxed out.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Let me know when it comes back.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
I'm getting worried now. The CPU is still maxed out, in SQL Server Configuration Manager the state of the SQL Server Service shows as Change pending...
What to do?!
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Most likely it is trying to complete some process that was running prior to the stop request and may be doing a rollback. Without insight into what queries were running you are hard pressed to determine what is safe. If you restart the server you may force things along but you may also cause problems. If you can wait, and that is my recommendation, I would.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Yup.. you are right.
I'm going to wait and see what happens.
Thanks for all your help, much appreciated.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Finally... it stopped eventually and I started it again.
I now know this is not a dedicated SQL Server box so I took on your advice and changed the maximum server memory to 6144MB so I'm leaving 2GB for the OS.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Excellent news. Get DAC configured now too. 🙂 http://ask.sqlservercentral.com/questions/1770/enable-dedicated-administrator-connection.html
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply