|
Limiting MSDE
|
Previous Top Next |
| This document is intended for the use of computer technicians or for someone whom is comfortable with working at the Command Prompt.
|
| Please read the instructions fully before beginning.
|
| If you need assistance with this please contact your computer technician or the RMS support desk.
|
|
|
| Overview:
|
| Most RMS customers install the free version of Microsoft's SQL database environment.
|
| This has the benefit of being free but has (but is not limited to) the following limitations.
|
| Can only be used by up to 5 concurrent users as a time – beyond this it deliberately slows down. Concurrent means 'at the same time',and as RMS generally opens, reads/writes then closes the database it would be normal that up to 12 pc's may have RMS installed and running without tripping this limit.
|
| Memory management is limited. Without the tools provided with the full SQL Server product it is difficult to set various optimization settings.
|
|
|
| Most importantly it is the memory management that can affect the system by slowing the system down as MSDE uses up more and more RAM.
|
| It is a normal function of MSDE to utilize up to 2Gb RAM before it self limits. This is used as a database cache to supposedly speed the system up.
|
| In reality, unless you have in excess of 2Gb RAM it actually has the opposite effect – using up all but 2-5 MB of system RAM before imposing its own self limit.
|
| This has the effect of slowing the system down while Windows tries to find additional RAM buy using Hard Disk Swap Space.
|
| The below instructions are designed to 'reset' this limit to a more reasonable limit that is well within your systems capabilities.
|
|
|
| For more technical information please refer to http://support.microsoft.com/default.aspx?scid=kb;en-us;321363
|
|
|
| How to determine the amount of RAM in your system (on the machine running SQL):
|
| Follow these steps
|
| Right click on My Computer – Found on the desktop or in the start menu
|
| Select Properties
|
| Looking at the displayed information the amount of RAM is usually displayed in MB in the bottom right hand corner.
|
| Possible/Usual amounts of RAM that you may see on this screen are
|
| 128 MB
|
| 256 MB
|
| 512 MB
|
| 1024 MB or 1GB
|
|
|
| The RAM in your system could also be shared by your video card; meaning that the amount displayed on this screen could be slightly less that the normal amounts shown above.
|
| If you are unsure of the amount of RAM that you have please consult your computer technician.
|
|
|
| Calculating your recommended SQL RAM limit:
|
| You need to determine how much RAM SQL can use. This must then be entered into the GREEN section of step 13 instead of the 600 that is there in the example below.
|
|
|
| RMS requires (as specified in the Hardware Requirements) 1Gb RAM on the machine that will act as the Server (the pc the stores the RMS data)
|
| Based on this you should limit SQL to 600Mb RAM, thus giving Windows 400Mb for itself and other programs (including the RMS application itself).
|
|
|
| If you have ore than 1Gb RAM on your server you should set it such that you leave at least 400-600Mb for Windows and other programs. This could largely depend on what other programs and services this PC will be used for. Please consult with your technician if you are not sure.
|
|
|
| If you have only 512 MB RAM you should limit SQL to 256MB, thus giving Windows 256MB, however this is BELOW the minimum requirement for RMS, Speed will not be greatly improved by this setting. Please add more RAM to make a total of at least 1Gb.
|
|
|
| If you have less that 512MB RAM, please add more RAM – DO NOT follow the instructions below, you could make Windows unstable.
|
|
|
| Warnings:
|
| The below instructions require several textual commands to be issued to the SQL server. Failure to follow these instructions properly may render your RMS data unusable.
|
| RMS takes no responsibility for damage to your data through the improper/incorrect use of the instructions below.
|
| Please ensure that you have a proper RMS backup before proceeding.
|
| Ensure that this backup has worked correctly by restoring it to your training system. If you do not know how to do this please consult an RMS support staff member.
|
|
|
| Instructions:
|
| Please follow these instructions carefully and closely, if you are not certain that you understand what's happening STOP and contact the RMS support desk.
|
| Items that you must type are in RED, items that the computer responds with are in BLUE.
|
|
|
| You will need to be on your MAIN Machine (SERVER). Exit RMS on all machines, including any external interface programs such as PABX, POS, RMSOnline and Boomgate monitors
|
| Click on Start, All Programs, Accessories, Command Prompt – you will see a black box with a command prompt – note the prompt may be different depending on your setup; this is not important.
|
| The cursor will be flashing to the right of the prompt
|
| Type the following osql –U sa then press enter
|
| You will be asked to enter the password, for those using a version LESS than 8.8.0 the password is blank, just press enter. For those using version 8.8.0 and later the password will be emu, then press enter (you will not see this entry on screen)
|
| The prompt should have now changed to 1>
|
| Type in sp_configure 'show advanced options','1' then press enter
|
| Type in GO then press enter
|
| You should see the following - DBCC execution completed. If DBCC printed error messages, contact your system administrator. Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
|
| Type in RECONFIGURE and press enter
|
| Type in GO and press enter
|
| You should now be back to a 1> prompt
|
| Type in sp_configure 'max server memory (MB)','600' then press enter – this is where you enter the amount of RAM that you calculated above.
|
| Type in GO and press enter
|
| You should see the following - Configuration option 'max server memory (MB)' changed from 2147483647 to 600. Run the RECONFIGURE statement to install.
|
| Type in RECONFIGURE and press enter
|
| Type in GO and press enter
|
| We are now done, all we need to do is exit the prompt to go back to DOS
|
| Type in exit and press enter
|
| You will now be returned to the same MSDOS prompt that you started with in step 2
|
| Type in exit again and press enter to close the black box
|
|
|
| SQL is now limited to the amount of RAM specified in step 13.
|
|
|
| You may now either restart your computer or reload any RMS interfaces that you closed in step 1.
|
|
|
| Below is a screen dump of an example
|
|
|
| C:\>osql -U sa
|
| Password:
|
| 1> sp_configure 'show advanced options','1'
|
| 2> GO
|
| DBCC execution completed. If DBCC printed error messages, contact your system administrator.
|
| Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
|
| 1> RECONFIGURE
|
| 2> GO
|
| 1> sp_configure 'max server memory (MB)','600'
|
| 2> GO
|
| DBCC execution completed. If DBCC printed error messages, contact your system administrator.
|
| Configuration option 'max server memory (MB)' changed from 2147483647 to 600. Run the RECONFIGURE statement to install.
|
| 1> RECONFIGURE
|
| 2> GO
|
| 1> exit
|
| C:\>
|
|
|
| Testing the results:
|
| The results of setting the SQL limit should become evident as you use RMS from day to day. Most notably, the speed of the system should not be affected over time because the SQL server will not consume the entire available RAM.
|
|
|
| You can test that your new settings have worked by performing the following test:
|
| Restart the Server (to flush out the memory)
|
| Open the Windows Task manager by pressing CTRL + ALT + DEL together
|
| Select the Processes TAB and find the entry called sqlservr.exe, notice the Mem Usage column on the right
|
| Leaving this box on the screen (move it out of the way if necessary) Run RMS
|
| You should see the Mem Usage figure of sqlserve.exe start to increase as you load RMS
|
| Open the Reports Menu and select Accounting reports
|
| Select Debtors ledger, leave the options alone and press BUILD
|
| Notice the Mem Usage now climbing rapidly
|
| The set limit amount will determine how high the figure can go before it stops
|
| If the Debtors Ledger has finished and the figure was still climbing, run a Tax audit report for the last 6 months from the Accounting Reports menu. This should increase the usage by even further until it hits the limit.
|
| Please note that the RAM value shown in this list is not measured in Mega Bytes. It is actually K or Kilo Bytes. This is like comparing milliliters with liters; however there is actually 1024K to 1 MB, so it is not as easy to convert.
|
|
|
| The CHM file was converted to HTML by chm2web software. |