Changing allocated memory in SQL Management Studio - No Access - SBS 2011

Hi All,

I am logged into a SBS2011 Server as the domain admin and want to lower the max ram usage setting for Sql. Its using 15gb RAM and the only Sql databases running are SBSMonitoring and BackupExec. Leaving around 200mb RAM free. The server runs like absolute crap so i am hoping setting the ram to a max of 2gb will help.

Only problem is i am logging into the databases using windows authentication and when i click apply to make the change... it comes up that i do not have access. Is there anyway i can get access to make this change easily?

I didn't originally setup this server so am not aware of any special sysadmin logins for sql, i would need to reset them if i have to use the sysadmin account.

Any help would be great.

Thanks
phoenix81Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
Option 1 -  You can use "SA" login if you know the password.
Option 2 - You can restart SQL server in single user mode. Here is the steps to restart SQL server in single user mode.

https://msdn.microsoft.com/en-us/library/ms345416(v=sql.105).aspx

After changing the startup option restart sql services and connect SQL server using your credentials, But your domain ID should be added in local Administrator windows Group.

Now you can add your domain id in sql server logins as a sysadmin and enjoy.

Change the startup options back once you are done.
David AtkinTechnical DirectorCommented:
In addition to the above.

You will also have a database which is used by WSUS (if you've not removed it).

You can connect to the WSUS Database by entering the following into the Server Name:
\\.\pipe\mssql$microsoft##ssee\sql\query

Make sure that you are opening Microsoft SQL Server Management Studio as an Administrator.

In addition, once you do limit the SQL Servers RAM you will find that Exchange will gobble the free RAM.  You can also limit the Exchange RAM useage as well.

There is an article on what you're trying to do here:
http://blog.switchsystems.co.uk/2014/01/reducing-memory-usage-on-sbs-servers/
phoenix81Author Commented:
Thanks for the tips :)
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

phoenix81Author Commented:
I am stuck on this part - Now you can add your domain id in sql server logins as a sysadmin and enjoy.


Where in in the management do i do this?

Thanks
Deepak ChauhanSQL Server DBACommented:
Connect sql server , expand security folder and right click on login folder > New login , type your domain id here. grant sysadmin server role to this.

Newlogin.JPG

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
phoenix81, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.