RE: Consolidating instances of SQL server

Hi Duncan,

Thanks for your update.

SQL Server is designed to use memory. It caches data and query plans to
memory so that it will be able to quickly access data. All relational
database management systems do this. Unless you are experiencing problems
because of the high memory usage from SQL Server, I would not worry.

If an instance of SQL Server is running on a computer where other
applications are frequently stopped or started, the allocation and
deallocation of memory by the instance of SQL Server may slow the startup
times of other applications. Also, if SQL Server is one of several server
applications running on a single computer - SBS is indeed such a server,
the system administrators may need to control the amount of memory
allocated to SQL Server.

The maximum amount of memory SQL Server can use varies depending on your
system. Basically, you can configure the SQL Server max server memory
option so that there is sufficient virtual memory left to satisfy the OS
and the other server applications memory requirement.

For your current situation, I would like to suggest you do as follows:

Firstly, we need to use the steps below to identify what instance is using
the most memory: In Task Manager, click Select Columns in the View menu,
and make sure that PID (Process Identifier) is checked. Then find out the
sqlservr.exe process that is using the most of the RAM and note its PID.
Once you have the PID run the following from a command prompt:

tasklist /svc

This should give you the instance associated with the PID you identified in
the previous step. And then you can configure a maximum amount of memory to
the instance.

Then, use max server memory to prevent SQL Server from using more that the
specified amount of memory. Try to test to see if it helps.

I am happy to be of assistance to you!

Have a nice day!

Sincerely,

Jenny Wu
Microsoft CSS Online Newsgroup Support
Get Secure! - www.microsoft.com/security
======================================================
This newsgroup only focuses on SBS technical issues. If you have issues
regarding other Microsoft products, you'd better post in the corresponding
newsgroups so that they can be resolved in an efficient and timely manner.
You can locate the newsgroup here:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx

When opening a new thread via the web interface, we recommend you check the
"Notify me of replies" box to receive e-mail notifications when there are
any updates in your thread. When responding to posts via your newsreader,
please "Reply to Group" so that others may learn and benefit from your
issue.

Microsoft engineers can only focus on one issue per thread. Although we
provide other information for your reference, we recommend you post
different incidents in different threads to keep the thread clean. In doing
so, it will ensure your issues are resolved in a timely manner.

For urgent issues, you may want to contact Microsoft CSS directly. Please
check http://support.microsoft.com for regional support phone numbers.

Any input or comments in this thread are highly appreciated.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

The customer mail content:
===================
Hi Jenny,

Thanks for your message. I've made some progress, but eventually ran out
of time to spend on the problem.

I managed to combine two of the instances of SQL server (the full version
of Sql2000 and my source control software instance) and with your help have
set min and max memory for each of the other instances at 100mb, but I'm
still running out of memory horribly.

Thanks again for the follow-up though!

Regards,

Duncan
===================

--------------------
X-Tomcat-ID: 119346940
References: <1139494218.384840.26570@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
From: v-yanniw@xxxxxxxxxxxxxxxxxxxx ("Jenny wu [MSFT]")
Organization: Microsoft
Date: Fri, 10 Feb 2006 06:16:06 GMT
Subject: RE: Consolidating instances of SQL server
X-Tomcat-NG: microsoft.public.windows.server.sbs
Message-ID: <arQi8lgLGHA.3504@xxxxxxxxxxxxxxxxxxxxx>
Newsgroups: microsoft.public.windows.server.sbs
Lines: 123
Path: TK2MSFTNGXA01.phx.gbl
Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.windows.server.sbs:243529
NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182

Hi Dunc,

Thanks for using the SBS newsgroup.

From your description, I understand that you want to know if several SQL
instances can be move to a single instance and you have some concern about
that the SQL instances use so many memory. If I am off base, please don't
hesitate to let me know.

Technically speaking, we can consolidate several SQL instances to a
instance by moving the user databases location to the new instance. To get
detail steps to process, you can take a look the following KB article:

224071 INF: Moving SQL Server Databases to a New Location with
Detach/Attach
http://support.microsoft.com/?id=224071

However the instances like SBSMONITORING, SHAREPOINT are SBS system
built-in instances, the application will not find the instance location if
you change the instance configuration. It is not feasible to customize all
applications that need to involve the corresponding instances to work.

To the high memory usage concern, we can use max server memory to prevent
SQL Server from using more that the specified amount of memory (we can
also
use min server memory to guarantee a minimum amount of memory to an
instance of SQL Server). To do so, open a command prompt and run the
following command:

osql -E -S sbsserver\<instance name>

You will enter the osql command prompt. Run the commends below (replace
<xxx> with the amount of memory):

1> EXEC sp_configure 'show advanced options', 1
2> reconfigure
3> go

1> EXEC sp_configure 'min server memory', <xxx>
2> reconfigure
3> go

1> EXEC sp_configure 'max server memory', <xxx>
2> reconfigure
3> go

After doing the above steps, you may run the commands to check the
configured memory usage:

1> EXEC sp_configure
2> go

For more information, please see the article below:

321363 INF: SQL Server Memory Usage
http://support.microsoft.com/?id=321363

Furthermore, the following KB could also help you manage the Exchange
memory usage.

815372 How to Optimize Memory Usage in Exchange Server 2003
http://support.microsoft.com/?id=815372

Hope above inforamtion helps. I am happy to be of assistance to you and
look forward to your reply!

Have a nice day!

Sincerely,

Jenny Wu
Microsoft CSS Online Newsgroup Support
======================================================

PLEASE NOTE the newsgroup SECURE CODE and PASSWORD will be updated at 9:00
AM PST, February 14, 2006. Please complete a re-registration process by
entering the secure code mmpng2006 when prompted. Once you have entered
the
secure code mmpng2006, you will be able to update your profile and access
the partner newsgroups.

======================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from this issue.

======================================================

This posting is provided "AS IS" with no warranties, and confers no
rights.

======================================================

--------------------
From: duncan.welch@xxxxxxxxx
Newsgroups: microsoft.public.windows.server.sbs
Subject: Consolidating instances of SQL server
Date: 9 Feb 2006 06:10:18 -0800
Organization: http://groups.google.com
Lines: 18
Message-ID: <1139494218.384840.26570@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>
NNTP-Posting-Host: 217.206.135.244
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1139494223 22450 127.0.0.1 (9 Feb 2006
14:10:23 GMT)
X-Complaints-To: groups-abuse@xxxxxxxxxx
NNTP-Posting-Date: Thu, 9 Feb 2006 14:10:23 +0000 (UTC)
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1;
.NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.1),gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 IRMLSERVER01
Complaints-To: groups-abuse@xxxxxxxxxx
Injection-Info: g43g2000cwa.googlegroups.com;
posting-host=217.206.135.244;
posting-account=7nt0hQ0AAAAtRG6lRD78qtEIhTr29nJw
Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onl
i
ne.de!news.glorb.com!postnews.google.com!g43g2000cwa.googlegroups.com!not-f
o
r-mail
Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.windows.server.sbs:243306
X-Tomcat-NG: microsoft.public.windows.server.sbs

I've got SBS2003sp1 installed on a server, and I've had to install a
copy of SQL2000 SP4 onto the same box. There are now a total of 5
instances of sqlserver.exe running, and they're eating all the memory.

I'd like to consolidate all instances of SQL server into a different
databases on a single instance. Does anyone know how to move these
instances to the real SQL server so I don't have to load them all
individually?

I'm most interested in:
MYSERVER$SBSMONITORING
MYSERVER$MSFW
MYSERVER$SHAREPOINT

Thanks in advance,

Dunc





.

Relevant Pages

  • RE: Allocated Memory Error
    ... Microsoft CSS Online Newsgroup Support ... This newsgroup only focuses on SBS technical issues. ... | Thread-Topic: Allocated Memory Error ... this is a by-design behavior of SQL Server. ...
    (microsoft.public.windows.server.sbs)
  • Re: FTS Performance in SQL 2005
    ... we had no end of problems with SQL FTS. ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS Performance in SQL 2005
    ... we had no end of problems with SQL FTS. ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • RE: how to reduce the memory useage of the sqlserver.exe process
    ... Based on my research, SQL Server is designed to use memory, it dynamically ... Microsoft Online Newsgroup Support ... This newsgroup only focuses on SBS technical issues. ...
    (microsoft.public.windows.server.sbs)
  • RE: The DBMS returned an unspecified error.
    ... | We are using Microsoft's JDBC driver for Java database access to SQL ... | Insufficient memory available. ... even though SQL Server 2000 is configured to use up to the ... the problem is most likely in the MemToLeave ...
    (microsoft.public.sqlserver.jdbcdriver)