How to change max server memory in SQL Server

Question

I was trying to change the max server memory for my database server, and I accidentally saved it as "4" instead of the number I wanted to enter.

Now I can't even connect to the server.

Is there a way for me to reset the max server memory setting to the default value without connecting to the database server somehow?

score 7 · Answer 1 · edited May 14 '12 at 05:26

7

Just start SQL Server with the -f switch, which will start it in a minimal, single-user mode. You should be able to change it back afterwards.

edited May 14 '12 at 05:26

Wesley

answered May 14 '12 at 03:46

Taylor Jasko

I'm very beginner when it comes to this stuff. Can you give me a link that will show me how to do that? – Steven May 14 '12 at 03:48
You should be able to start it with a simple "sqlservr.exe -f" in the command prompt. – Taylor Jasko May 14 '12 at 03:53

score 1 · Answer 2 · answered May 14 '12 at 04:47

Use this link to start sql server in single user mode and set max server memory by using following command.

2.sp_configure 'Max Server Memory',1024 reconfigure

When you are starting sql server in single user mode you have to disconnect all connections.

2 Answers2