March 29, 2014 at 7:22 am
Hi All! We have an issue that when we were allocating set amounts of memory to a group of named instances, someone accidentally allocated just 6MB to one instance, now it won't start. How do I fix this issue;TITLE: Connect to Server
------------------------------
Cannot connect to qa-db-farm\duke.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
There is insufficient system memory in resource pool 'default' to run this query. (Microsoft SQL Server, Error: 701)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=701&LinkId=20476
Thanks!!!
--------------------------------------------
Laughing in the face of contention...
March 29, 2014 at 7:36 am
Not sure if this will help but it is a place to start:
http://msdn.microsoft.com/en-us/library/ms162819(v=sql.120).aspx
March 30, 2014 at 11:15 am
Using the link Lynn mentioned you'll likely need to start SQL Server in single user mode (-m). If you find that you can't connect to the instance after doing this it's probably because another connection beat you to it... (an application connection for example).
If that's the case and you can RDP to the box I suggest turning off the TCP/IP protocol using the SQL Server Configuration Manager, then restart the instance and try connecting to it locally while RDP'd. This is because coming from the same host you'll use shared memory rather than TCP/IP to connect and shouldn't have an issue (because you basically getting in because you effectively cut off all others).
Change the memory and turn the TCP/IP protocol back on and bounce the instance of SQL and you should be good.
I hope this helps, best of luck.
March 30, 2014 at 12:22 pm
March 30, 2014 at 1:45 pm
Hi all - Thanks for your feedback.
I'm still struggling to rectify this issue.
I have multiple named instances on one server. $ out of the 5 have the correct allocation, the other doesn't have any memory allocated.
I have tried changing the start up parameters by adding ;-m to the end of the path however when I try access the database instance it errors stating;
TITLE: Connect to Server
------------------------------
Cannot connect to ttmx-tt-home\drake.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'T-BX\UserName'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
I have tried disabling TCP/IP but when I do I can't connect to the database engine using SSMS.
Should I be going about this in a different way?
--------------------------------------------
Laughing in the face of contention...
March 30, 2014 at 2:13 pm
If you are trying to connect to the instance using SSMS, the object explorer takes one connection to the server, preventing you from using a query window to run an queries. Try connecting using SQLCMD while the server is in single user mode.
March 30, 2014 at 3:12 pm
Brillant! Thanks Lynn.
I have just connected through SQLCMD and updated the memory.
Thanks again all. Bacon saved!
--------------------------------------------
Laughing in the face of contention...
March 31, 2014 at 2:41 am
Hi - Me again! After updating the memory, I am now being hit with the following error;
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
I've seen this error on numerous occasion and generally you can fix it be enabling TCP/IP or starting the services. All are on or started.... I wonder why changing the memory allocation would do this. Any ideas?
Thanks!
--------------------------------------------
Laughing in the face of contention...
March 31, 2014 at 7:00 am
AmarettoSlim (3/30/2014)
Using the link Lynn mentioned you'll likely need to start SQL Server in single user mode (-m). If you find that you can't connect to the instance after doing this it's probably because another connection beat you to it... (an application connection for example).If that's the case and you can RDP to the box I suggest turning off the TCP/IP protocol using the SQL Server Configuration Manager, then restart the instance and try connecting to it locally while RDP'd. This is because coming from the same host you'll use shared memory rather than TCP/IP to connect and shouldn't have an issue (because you basically getting in because you effectively cut off all others).
Change the memory and turn the TCP/IP protocol back on and bounce the instance of SQL and you should be good.
I hope this helps, best of luck.
No need for any of that. When starting single user with -m the following will limit connections to SQLCMD only
sqlservr -m"SQLCMD"
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 31, 2014 at 8:04 am
Ah, awesome, thanks for sharing this!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply