Kazmarek

Free Site Analysis 1-858-952-5400
REMOTE ASSISTANCE
  • Home
  • OnGoing Management
  • IT Services
    • Microsoft 365, Exchange Services, and Consulting
    • Cloud Solutions
    • Virtualization
    • Mobility
    • Backup & Disaster Recovery
    • Server Installation/Migration
    • VoIP
    • Network & System Administration
    • Additional Solutions
  • Why Kazmarek
    • Case Studies
    • Testimonials
    • KTS Values
  • For Clients
    • O365 Log In
    • Client Portal
    • KTS Documents
    • SonicWALL VPN Client
  • News
    • Newsletters
  • Who We Are
    • Company
    • Team
    • KTS E-Award Winners
    • KTS Values
    • Charities We Support
  • Contact Us
    • Contact Us
    • Careers

MSSQL Database – Limiting RAM Usage

July 8, 2014 by Tayla Dodson

Every so often I log into a server to find that it is encountering errors due to it running low on Resources, usually RAM.  If it is a Windows SBS or SQL Database Host, more often than not the issue is due to an SQL Database that just got out of hand.  Most of the time it seems that the SQL Database has no Max Memory limit set.  After many interactions where I have had to go into SQL Studio to try to set the limits.  I recently discovered a set of commands to accomplish the same in 5 minutes or less.  That is what I shall share.

PLEASE NOTE, WINDOWS SERVER 2008 + ONLY

The SQL Database I was working on when I encountered this fix was the Internal Database used for WSUS, so I will start with that example.

Start by opening up a command prompt, then type in the following and hit enter.

sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E

sqlcmd
Flags Used:
  • -S [protocol:]server[\instance_name][,port]
    Example: sqlcmd -S tcp:*ComputerName*\*DatabaseName*,1433
  • -E (use trusted connection)

This will bring you to an SQL Prompt that in the Command Prompt window.  Your line should be: >1

From here, we are entering a few SQL commands to run against the Database.

sp_configure ’show advanced options’, 1;
reconfigure;
go
sp_configure ‘max server memory’, 512;
reconfigure;
go
exit

sp_configure [ [ @configname = ] 'option_name' [ , [ @configvalue = ] 'value' ] ]
  • show advanced options – Required to access Max Server Memory
  • max server memory – Sets Max Memory in MB the Server can use
  • min server memory – Sets Minimum Memory in MB the Server starts with
RECONFIGURE - Specifies that if the configuration setting does not require a server stop and restart, the currently running value should be updated
GO - sqlcmd specific, Used to send all commands to SQL Database to execute.
And that’s it.  The SQL Server with reconfigure the RAM usage on the fly.

Categories

  • Active Directory
  • Backup Exec (All Versions)
  • Blackberry / Blackberry Enterprise Server
  • Citrix
  • Cool Tech Stuff
  • CRM
  • Dell
  • Exchange 2007
  • Exchange 2010
  • Exchange 2013
  • Exchange Hosted
  • Exchange Server
  • Group Policy
  • IIS
  • Kaseya
  • kazmarek
  • MacOS
  • Microsoft
  • Networking
  • newsletter
  • Office
  • Office 365
  • Office Communications Server
  • OfficeScan
  • Outlook
  • Printing
  • SBS 2003
  • SBS 2008
  • SBS 2011
  • Server 2003
  • Sharepoint
  • Spyware
  • SQL
  • Symantec
  • Terminal Server
  • Thawte
  • Trend Micro
  • Uncategorized
  • Virtual Server
  • Websense
  • Windows 7
  • Windows Mobile
  • Windows Server 2008
  • Windows Server 2012
  • Windows Vista
  • Windows XP Pro/Home
Copyrights: © 2023 Kazmarek. All rights reserved.

Designed by TinyFrog & N Halie Designs