Need More Available Memory For SQL 2005...options?
Dec 10, 2007
My organization is currently migrating about 35 databases from SQL 2000 to SQL 2005. Most of the databases are small with the largest being about 6gb. These databases are not very transaction intensive. We are about half way through as we started the project a year ago. We purchased new hardware for this SQL 2005 so it is a side-by-side migration and it has been going well so far. I made one oversight which I think will come back to haunt us down the line. I installed Windows Server 2003 Standard R2 Edition and of course now I am considering putting more RAM in the server. We have 4 GB of RAM in the server and of course Standard Edition will not address more 4GB of physical ram. I have not seen any memory pressure yet but as databases are migrated over the amount of physical RAM drops. My buffer hit cahce ratio and page life expectancy performance counters are fine so far. The server is a brand new HP DL 380 G5 with with tons of disk and processing power, but only 4GB of RAM. I can't migrate to another server with more ram obviously.
1. What would you do in a situation like this?
2. I could do an in-place upgrade to Windows Server 2003 Enterprise R2. Has anyone attempted an in-place upgrade? I have no applications
on this server, just SQL 2005 Standard.
3. Would the /3gb switch be an option? I have heard mixed reviews on this one.
As far as database optimization, %75 of the databases are third party so we have little flexibility in tuning them or redesigning
tables.
Thanks,
Alex
View 2 Replies
ADVERTISEMENT
Apr 4, 2001
Is it possible to leave this parameter (max server memory (MB)) on default(2147483647) if on my Cluster I have Oracle, Lotus Notes and some other things running, or I have to calculate the amount of memory SQL Server needs?
Thanks for any suggestions!
View 2 Replies
View Related
May 29, 2007
Hi All
Sorry about that naïve question but for some reason on one of my computers when I connect to Sql 2005 I don€™t have Edit option when right clicking on a view.
If I€™m connecting from another computer using same login it€™s all there.
Could you please help?
View 5 Replies
View Related
May 22, 2006
Ok, here goes....
What exactly does a standard SQL 2005 license cover? I want to have 3 client stations entering data, one IIS serving up data to an unknown number of persons via a web page. Under the 1 server 5 CAL package would this setup be covered? or will I have to go the processor route? I might add that the user accessing via the web service would be on an intranet behind a firewall.
Thanks in advance!
View 3 Replies
View Related
Oct 13, 2006
I printed out several pages of documentation regarding the install of 2005. When I tried performing the installation from CD I double-click splash.hta, as indicated in "How to: Install SQL Server 2005 (Setup)". Under Install I see two options:
Server components, tools, Books Online, and samples
Run the SQL Native Client Installation Wizard
The documentation indicates I should be clicking on "Run the SQL Server Installation Wizard", which is not an option. If I select "Run the SQL Native Client..." it appears to be only installing the client and does not follow the remainder of the instructions. I can't find any information on Microsoft's site on how to perform an install from CD.
I'm guessing I should be using the DVD, but has anyone else noticed the "How to" documentation is a little off and have you found any other documentation that is more accurate?
Thanks, Dave
View 1 Replies
View Related
Jul 13, 2007
Why did Microsoft release a replacement for MSDE called SQL 2005 Express with Advanced Options without the SQL Agent? I bought into the MSDE and got rid of the other databases we supported now we have to go a create an equivilent to the SQL Agent for a) scheduled jobs b) backup automation. c) etc.
Does anybody really want a database that does not have these built in? I understand I can do these myself but this seems to defy logic to me?
thanks,
Larry
View 1 Replies
View Related
Apr 25, 2008
Hi,,
when I open a connection to SQL Server 2005 (c#, VB.NET 2005, SQL Native Client) these setting are set (can see it in Profiler):
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
I would like ARITHABORT to be ON. Have tryied to set it as a defaul in my database, but still the connection has OFF at this setting.
How can I force my C# code to set this option to ON during connection opening?
Thanks,
Przemo
View 1 Replies
View Related
Aug 8, 2007
I am looking for a way to replicate databases from an environment using SQL Server 2000 sp4 to machines using either 2000 sp4 or 2005 sp2. The size of the dbs (+3 gb) warrant a transactional model, but the developers opted to not create primary keys for any of their tables and PKs are required for transactional publications (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1627873&SiteID=17&pageid=0#1614178). Creating PKs are, most likely, not an option given the time constraints of the project.
I'm also aware of the fact that merge replication does not work from SQL Server 2000 to SQL Server 2005 (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=499634&SiteID=1).
I'm looking for alternative solutions to this problem, regardless of whether it's replication or not. Log shipping was suggested at one point, but I want secondary confirmation on this. Any help is appreciated. Thanks.
UPDATE: Log shipping in SQL Server 2000 is supported in Enterprise and Developer edition. The source server is using 2000 Standard.
UPDATE 2: Log shipping in SQL Server Standard is *possible* but unsupported http://sql-server-performance.com/Community/forums/t/17798.aspx?PageIndex=1. It looks like the original poster was still having problems by the end of this thread.
View 3 Replies
View Related
Jan 26, 2007
All,
If this questions is misplaced please forgive me.
I have a customer who has a Win SBS 2003 Premium installation with a Win 2003 Standard member server running SQL 2000 housing a Practice Management App.
The new release of the Practice Management App only runs on SQL 2005.
My question is what are my options for obtaining SQL 2005 in the most cost effective way? The SBS Premium install was not a candidate for the 'free' upgrade to R2. Is there an 'upgrade' release of SQL 2005 for existing SBS Premium customers?
Any information would be greatly appreciated.
Thanks!
Ray
View 1 Replies
View Related
Jul 13, 2007
I installed SQL Server 2005 Developer Edition. When i create a new database (using the "New Database" dialog) i cannot set the new database's compatibility level to "SQL Server 2005(90)" because this option is not in the "dropdown list". the only items shown are: "SQL Server 7.0(70)" and "SQL Server 2000(80)". I set the owner to "sa". How do i get "SQL Server 2005(90)" in my "compatibility level" drop down list? Is this an installation option that i missed? Thanks in advance for any assistance!
-chris
View 12 Replies
View Related
Sep 24, 2006
Hi,
SQL Server 2005 isn't accepting any connection other than SQL Management Studio from the local machine. However, even that's exhibiting strange behavior (please see below for more details).
I've read through all the common errors regarding enabling remote connections via the SAC tool, making sure protocols are enabled, etc. This case seems to be completely outside. Any advice appreciated.
Thanks,
Elias
[1] Client side:
What is the connection string in you app or DSN? N/A. Can't connect via any remote method, including client Control Panel > ODBC > create DSN, SQL Server Enterprise Manager, third party application.
If client fails to connect, what is the client error messages? Several, depending on method: "server does not exist or access denied", "error has occurred... under default settings, SQL Server does not accept remote connections", etc.
Is the client remote or local to the SQL server machine? Remote
Can you ping your server? YesCan you telnet to your SQL Server? No. However, there's a functional instance of SQL Server 2000 on the same domain which has no problems- telnet also fails to that instance, even locally from either machine using the 127.0.0.1 address. Telnet functions for other ports.
What
is your client database provider? N/A Or/And, what is your client
application? SQL Management Studio and others
Is your client computer in the same domain as the Server computer? Same domain
What protocol the client enabled? TCPIP when I attempted to create the DSN. Not sure what protocol SQL Management Studio uses.
Do
you have aliases configured that match the server name portion of your
connection string? NoDo you select force encryption on server and/or client? No
[2] Server side:
What is the MS SQL version? SQL Server 2005
What is the SKU of MS SQL? Standard
What
is the SQL Server Protocol enabled? [Shared Memory | TCPIP | Named
Pipes ] Have tried all three individually and together. Configuration Manager exhibits strange behavior (see below)Does the server start successfully? Server starts successfully when the machine boots. However once it's stopped, it can't be restarted (see below)
If SQL Server is a named instance, is the SQL browser enabled? Default instance
What is the account that the SQL Server is running under? Local SystemDo
you make firewall exception for your SQL server TCP port if you want
connect remotely through TCP provider? Not sure, but SQL Server 200 instance is working OK
Do
you make firewall exception for SQL Browser UDP port 1434? Unknown
[3] Platform:
What is the OS version? Client: Windows XPSP2 Server: Windows Server 2003
Do you have third party antivirus, anti-spareware software installed? Client: Trend Micro Server: UnknownOther behavior:
A separate instance of SQL Server 2000 on a different machine on the same domain is functioning without problems.
Telnet to either SQL Server (functional 2000 or problem 2005) on port 1433 is not possible - even when attempted from the local machine using 127.0.0.1! Telnet to both machines works using other ports.
When creating a DSN from a client machine, problem 2005 instance is in list of available SQL Servers, however connection fails ("SQL Server denied access or does not exist").
MSSQLSERVER service starts automatically on boot. However, once stopped the service can not be restarted. Message given: "SQL Server (MSSQLSERVER) service on Local Computer Started and then Stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service."
MSSQLSERVER service starts automatically on boot. However, once stopped the service can not be restarted. Message given: "SQL
Server (MSSQLSERVER) service on Local Computer Started and then
Stopped. Some services stop automatically if they have no work to do,
for example, the Performance Logs and Alerts Service."
SQL Server Surface Area Configuration for Services and Connections tool does not display Database Engine option as indicated by this MS guide to enabling remote connections: http://support.microsoft.com/default.aspx?scid=kb;en-us;914277
SQL Server communication manager shows TCP, Shared memory, named pipes enabled. However event log seems to show instance not listening via any protocol. Message displayed as: "SQL Server listening on ." (should read: "Server is listening on ['any' <ipaddress> <ipv4> <Port Number>])
Several answers indicated uninstall and reinstall could solve these problems. However, wizard refused to uninstall SQL Server.
Attempted disabling Shared Memory as documentation indicates this protocol gets used first. No luck.
Access through SQL Server Management Studio works, from local machine only.
Able to ping machine by name and IP.
View 1 Replies
View Related
Jan 21, 2008
Hi I am using SQL 2005 64 Enterprise edition with 30GB memory.
I have allowed SQL to use min 0 memory and maximum 20GB but sql is only showing it can use 100MB of memory
Anyone know the answer
Ap
View 7 Replies
View Related
Sep 16, 2007
Here'a a break down of what I've done so far.
* Windows 2003 32bit Enterprise with 10gb RAM
* SQL 2005 32bit Enterprise
* boot.ini - have added /3GB /PAE switches
* Confirmed which account SQL Server is running under and added that account to Local Policies/User Rights Assignment/Lock pages in memory
* Enabled AWE in SQL SERVER
* set minimum and maximum server memory to 9gb
* Rebooted.
Task manager says the following:
sqlservr.exe - 95MB usage * I know this is normal when using AWE right?
The actual physical memory used is 3.13GB and never goes higher. So this leads to believe that SQL is utilizing 3GB because of the /3GB switch, but it's not using the rest like it should be. I tried it without the /3GB switch and it only went up to about 2.1GB usage.
I've turned on performance monitor and SQL server target memory says 8.65 GB while the Total Server Memory says 2.45GB.
I've run:
EXEC sp_configure 'show advanced options'
RECONFIGURE
GO
EXEC sp_configure 'awe enabled'
RECONFIGURE
GO
And it confirms that the running value for both is 1
Please help.
View 20 Replies
View Related
Nov 14, 2007
We have been using SQL 2005 for quite sometime however recently I have been seeing memory issues. SQL will grow to use the maximum available! I have set min 1024MB and max to 15360MB leaving approx 5GB for OS.
Windows 2003 64 / SQL2005 64
Lock pages to memory - enabled
Min and Max - 1024, 15360
AWE not checked
We have recently added link server using OLEDB DB2 provider. Could this be the issue?
View 8 Replies
View Related
Jun 8, 2007
Is AWE enabled on SQL 2005 Standard? We currently have it setup using "locked pages in memory" for a domain account and haven't noticed any performance problems.
This article however states that only the SQL2005 Enterprise Edn supports "locked pages in memory". Can anyone confirm this?
http://support.microsoft.com/kb/918483
If I'm mistaken, how should the memory options be set for a server with 8gb ram running on SQL 2005 Standard which runs on Win2003 Enterprise? Should the min and max memory be set or left alone as default?
View 5 Replies
View Related
Dec 4, 2007
Hello
We have a problem with MS SQL 2005 Standard on a Windows 2003 x64 Box.
Server MS 2003 Server x64 R2
Quad Core 2.13GHz
Memory: 12 GB
MS Sql 2005 Standard, Sp2
The Sql process uses only 80MB of RAM (from 8 GB) so this machine is very slow. We set the min and max Memory in SQL without success. SQL ist very slow. Has anyone here some hint's to solve this problem? Thank'.
rainbow1
View 5 Replies
View Related
Mar 26, 2007
Hi
I would like to know how much of memory is taken by sql server 2005 entprise edition 64 bit during sql server startup in default configuration.....(4GB RAM being used).
i also want to know about montioring the performance of sql server 2005.Should i continue to use performance monitor (perfmon) or is there any new feature in slq server 2005 which provides performance monitoring.
Thanks in advance
View 2 Replies
View Related
Nov 13, 2006
If I install SQL 2005 Standard on Windows 2003 Standard, is SQL limited to 4 gigs of physical RAM?
I'm planning a new system that will run SQL 2005 Standard edition on a Windows 2003 Standard platform. The spec calls for 8 GB of RAM. My experience would lead me to suspect I need to install Windows 2003 Enterprise to take advantage of all the installed memory.
View 3 Replies
View Related
Jan 25, 2008
OVERVIEW
I run a MS SQL DB and have 7GB of RAM allocated to it to use. My DB size is around 30GB. I have about 30-40 users at any given time accessing this DB, and on any given moment we may have 2-3 queries being processed at the exact same time, but not really a high rate of usage.
The SQL is running on a 2 - Dual Core Xeon 2.8Ghz processor server in RAID 1+0. The average CPU usage is around 8-15% at most times, spikes to 60-70% sometimes then drops back down.
QUESTION
Why or what would cause SQL to show in my Task Manager as using 7GB of RAM constantly? When I restart SQL, system RAM drops to 1.7GB and holds. As traffic increases, as queries begin to be processed in SQL the RAM rises till it reaches the 7GB limit I set. Before it reaches this limit, the ASPX pages run smooth as silk. Once it reaches this 7GB limit they begin to crawl. A process that would take 2-6 seconds, now takes 30-60 seconds.
I know a couple years back I ran into this and it was because processes were not being closed and SQL was holding them in RAM, but I have been assured this is not the case now. I was told that because my DB is 30GB total (MANY tables, most are hardly ever accessed, and the bulk of the data is hardly accessed) that it is common for SQL to use and hold on to 7GB of data - is this true?
What would cause SQL to hold 7GB and slow way down? IS there a KB article that could help me understand this?
Thanks for your help.
View 2 Replies
View Related
Dec 11, 2007
Hi all,
I needed to load some tables in memory on startup because of performance reasons.
I'm using "select * from <table>", but there are few questions:
1. How to pin already selected data in memory ? (DBCC PINTABLE doesn't work for 2005)
2. How to put index data in memory ? (do you read document(s) for advance memory management - index data caching ?)
3. How to pin index data in memory ? (otherwise sound very bad - table data in fast memory, index data - in slow disks)
Thanks in advance:
Siol En
View 6 Replies
View Related
Apr 17, 2008
So I'm in the middle of building SQL Server 2005 on my new cluster hardware. I've got all the goodies this time around -4x PowerEdge 68004x dual core x64 procs in each box32GB ram in each boxWindows Server 2k3 R2 Enterprise x64 SP2SQL 2005 Enterprise x64 SP2Active Active Active Passive cluster (4 boxes, 3 instances)dedicated data, log, and tempDB 4Gb 15k fiber channel SAN Volumes for each instanceNow, this is the first x64 box I've configured, so I'm looking for some optimization tips for a couple things.First one's memory. On our 32bit systems, I always added the /PAE switch to the boot.ini file and enable AWE in SQL Server. From what I'm reading, that's no longer needed with the enhanced memory addressing. Is that the case? Is there an MS best practices KB doc in regards to configuration? I can't find one.Also, should I set anything special pagefile-wise? I know the old mantra is 1.5x system memory, but that would create a 48GB page file. Our current cluster has 6GB of ram dedicated to each instance (2k) and Perfmon tells me it's barely touching the pagefile. I'm thinking of just leaving it default, but I want to know if there is a best practice that I'm missing. Again, I can't find an MS doc that addresses this particular scenario.
View 14 Replies
View Related
May 13, 2008
I'm having problems with SQL 2005 Express Edition exceeding the maximum memory limit. I hard set the minimum to 100 and the maximum to 500, but the server is currently using over 800MB and is causing the system to page. Has anyone had any experience with similar issues and if so how did you resolve them.
View 8 Replies
View Related
Jul 31, 2007
We just upgraded the memory of our database server from 2GB to 4GB. And its OS is windows 2003 standard and sql is 2005 standard edition. According to microsoft(http://msdn2.microsoft.com/en-us/library/ms143685.aspx and http://msdn2.microsoft.com/en-us/library/aa366778.aspx#physical_memory_limits_windows_server_2003), this configuration should allow database use up to 4 GB memory. My question is : Do I need to do anything(such as: adjust the maximum memory in management studio) to let our server be able to consume this extra 2 GB memory?
Does this solution http://support.microsoft.com/kb/274750 apply for SQL server 2005?
View 1 Replies
View Related
Mar 9, 2006
Hi all,I have recently implemented a SQL 2005 cluster using SQL 2005 Standardon Windows 2003 Enterprise edition.Both nodes have 4GB of RAM and according to the datasheet, SQL 2005Standard can support the OS maximum memory amount and Win2K3 EntEdition can support 64GB!However, in Enterprise Manager, if I go to the "Memory" tab of theinstance properties I can't increase the memory beyond 2147483647(which I assume is around 2GB).I don't have AWE enabled but am unsure as to the ramifications of this.Any advice anyone could provide would be greatly appreciated.Many thanks,Ian
View 2 Replies
View Related
Sep 17, 2007
I've read and noticed SQL 2005 handles memory differently then 2000. In 2000 if I told a server it had 6GB to use, it allocated the memory. In 2005 I have one 32-bit server with 6GB of memory and one 64-bit server with 32 GB. If Target Server Memory is the amount of memory SQL Server would like to have, how does that correspond to Maximum Server Memory? Also, how is Target Server Memory determined?
32-bit
Physical Memory = 8GB
Target Server Memory = 6GB (Willing to consume)
Total Server Memory = 690MB (Currently consuming)
Minimum Server Memory = 2GB
Maximum Server Memory = 6GB
For the 32-bit server the Target Server Memory matches Maximum Server Memory
64-bit
Physical Memory = 32GB
Target Server Memory = 28GB (Willing to consume)
Total Server Memory = 397MB (Currently consuming)
Minimum Server Memory = 4GB
Maximum Server Memory = 30GB
For the 64-bit server the Target Server Memory is less then the Maximum Server Memory
Lock Pages in Memory is set for the service account. Neither server above has yet to be released to production and only the 32-bit server has any users. In 2000 when SQL Server started I could count on it using about 1.72GB of memory immediately. Seeing the servers above consume only only 690MB and 397MB has me concerned. Is this just a case of SQL Server 2005 handling memory better then 2000?
Thanks, Dave
View 4 Replies
View Related
Jan 2, 2008
Hi,
I'm about to install Windows 2003 STD 64BIT and SQL 2005 STD 64BIT with 12GB MEM.
my question is about the switch in the BOOT.INI - do i need to enable any switch with this server configuration?
and do i need to enable the AWE on the SQL?
THX
View 7 Replies
View Related
Jun 26, 2007
Hi everybody,
I know that SQL2005 Express has 1 GB memory limitation.
Well, if my server has 2 GB RAM and I have other applications (without memory constraints) how does this relate to the SQL constraints ?
Can I still run SQL2005 Express if other applications exaust only 1 GigaByte of memory ?
Thank you in advance.
Pierluigi
View 7 Replies
View Related
Oct 10, 2007
Hi,
I have a three-tier app written in C#, which takes information from a third party source (typically an array of double precision floats) and commits it to a SQL 2005 Db.
The Server then notifies the client that the information is available and the client queries the Db (through the server) in order to display it in "real-time". It all seems to work fine except that the memory usage by SQL 2005 just keeps rising. I have run a memory profiler on the server and client apps and they do not have a leak.
The test I am running has all three apps on the same machine and is reading in about 250k of data a second. It typically runs for just under two days on a machine with 2Gb RAM before falling over with System.OutOfMemoryException.
I have tried setting max memory usage but it seems to make no difference... anyone seen anything similar or know what my problem might be?
Thanks,
Paul G
View 19 Replies
View Related
Sep 14, 2007
Hi:
I'm running Windows Small Business Server 2003 Premium R2. I installed SQL Server 2005 Standard Edition on this PC. I'm using Std Edition instead of the Workgroup Edition that comes with SBS since I like the additional features in the Std Edition.
I'm a kind of a newbie to Windows Server administration and SQL Server administration although I've written lots of SQL queries, stored procedures, etc.
I have 2GB of RAM on this PC. When I looked in Task Manager, I saw that SQL Server was using somewhere over 1GB of RAM. So, I opened up SQL Management Studio, right clicked on the server node, clicked to get to the memory configuration page and saw that SQL Server was set to use all 2GB of RAM on the PC. I changed that to 500MB(500000000, or 476MB) and decided to reboot the server. When the server came back up, I forgot to check SQL Server's new RAM usage. The server ran without interruption for over 24 hours. Now, when I checked SQL Server's memory usage in Task Manager, I can see it's using over 750MB of RAM.
How do I fix this?
I have Exchange Server 2003 running on this PC as well.
Thx.
View 9 Replies
View Related
Aug 13, 2007
Hi,
The server is on win 2003 server with sql server 2005 workgroup sp2
ram is 2 GB.
the SQL start with 100MB and 100MB VM and after two days of regular and not massive work
the SQL memory goes up to 1GB ram and 1GB VM
how can i know who is the process that cuase the SQL memory to incress
is it somthing usual for sql 2005 to go up to 2 GB mem?
View 1 Replies
View Related
Mar 6, 2008
We recently did an in-place upgrade of our cluster from SQL 2000 Enterprise to 2005 Enterprise. We are seeing memory utilization on the server that is not expected and wanted to get an idea from others if this is normal. Here is our setup:
32-bit Windows Server 2003 SP1
SQL 2005 SP2, 2-node cluster
16 GB memory on each node
/3GB /PAE switches in the boot.ini file
AWE Enabled, min memory=0, max memory=14000
The strange bit is that in the Task Manager the SQL service is showing as only using 205 MB of memory and the pagefile usage is at 13 GB. This is troublesome since it looks like SQL is using virtual memory instead of physical memory. We recently upgraded the memory from 4 GB to 16 GB, the sql cluster service account was added to the Lock Pages in Memory policy, the boot.ini switches were set and the AWE enabled/max_server_memory script was run before upgrading to 2005. Can anyone confirm either 1) this is normal or 2) how to correct the memory usage?
I have also questioned using the /3GB switch; would it be better to remove that and set the max memory for SQL to 12000?
Any help would be greatly appreciated. If I left off any information that would be helpful, please let me know.
Chris
View 11 Replies
View Related
Dec 11, 2007
Hi all,
I needed to load some tables in memory on startup because of performance reasons.
I'm using "select * from <table>", but there are few questions:
1. How to pin already selected data in memory ? (DBCC PINTABLE doesn't work for 2005)
2. How to put index data in memory ? (do you read document(s) for advance memory management - index data caching ?)
3. How to pin index data in memory ? (otherwise sound very bad - table data in fast memory, index data - in slow disks)
Thanks in advance:
Siol En
View 3 Replies
View Related
Sep 18, 2007
We have an application that we currently run on SQL Server 2000 that works by creating a DTS package that it then executes.
Due to performance reasons, we have been considering switching to 2005, for a few reasons. Can anyone confirm clarify the following?
1) SQL Server 2000 caps RAM usage at 2GB, whereas SQL Server 2005 is only limited by the OS - RAM usage is a big current issue for us, so if upgrading to 2005 would solve this it would help a lot. Can anyone confirm my understanding of this?
2) Would using the legacy DTS in SQL Server 2005 take advantage of this RAM difference, or is it running on the old 2000 engine and only able to use the 2GB?
Thanks for any help.
View 4 Replies
View Related
