Thursday, March 8, 2012
Another memory question
Server has 8GB RAM - SQL Server currently takes 1.7GB
I'd like to get SQL Server to about 4gb RAM. I've been reading all the
posts and replies and I think I may have all the info, taking a little from
several posts. I'm just not positive as I haven't seen a step by step (in
the proper order) document or posting. Not sure if Microsoft has a step by
step. I've been through books on-line and have seen all these topics.
Are these the steps in the proper order?
1-Update the boot.ini to add the /3gb switch
2-Enable awe (via SSMS)
3-Select running values or configured values? (via SSMS)
4-Set the SQL Server max memory to 4gb (via SSMS)
5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
6-Reboot server
Anything else?
Thanks
Ron
You will also have to turn on the /PAE switch in BOOT.INI. Use sp_configure
to turn on AWE and to set the max server memory.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
Server has 8GB RAM - SQL Server currently takes 1.7GB
I'd like to get SQL Server to about 4gb RAM. I've been reading all the
posts and replies and I think I may have all the info, taking a little from
several posts. I'm just not positive as I haven't seen a step by step (in
the proper order) document or posting. Not sure if Microsoft has a step by
step. I've been through books on-line and have seen all these topics.
Are these the steps in the proper order?
1-Update the boot.ini to add the /3gb switch
2-Enable awe (via SSMS)
3-Select running values or configured values? (via SSMS)
4-Set the SQL Server max memory to 4gb (via SSMS)
5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
6-Reboot server
Anything else?
Thanks
Ron
|||Do not use /3gb switch if you want sql server to use 4GB. You could
probably go up to 5.5-6GB or so if this is a dedicated machine.
Occasionally observe pages/sec performance monitor counter to see if sql is
taking too much ram.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> Server has 8GB RAM - SQL Server currently takes 1.7GB
> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> posts and replies and I think I may have all the info, taking a little
> from
> several posts. I'm just not positive as I haven't seen a step by step (in
> the proper order) document or posting. Not sure if Microsoft has a step
> by
> step. I've been through books on-line and have seen all these topics.
> Are these the steps in the proper order?
> 1-Update the boot.ini to add the /3gb switch
> 2-Enable awe (via SSMS)
> 3-Select running values or configured values? (via SSMS)
> 4-Set the SQL Server max memory to 4gb (via SSMS)
> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> 6-Reboot server
> Anything else?
> Thanks
> Ron
>
>
|||While I also have to ask why you want to limit it to 4GB when you have 8GB
total I don't agree with the statement not to use the /3GB. Since you will
have to use AWE to access anything over 3GB anyway that is not a factor. The
question is can you benefit from the extra 1GB of directly addressable
memory or not. Since the data buffer pool is the only thing that can use AWE
memory you need to see if you could use that extra GB for things like proc
cache, connection memory etc.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13stt0f7jhtnk14@.corp.supernews.com...
> Do not use /3gb switch if you want sql server to use 4GB. You could
> probably go up to 5.5-6GB or so if this is a dedicated machine.
> Occasionally observe pages/sec performance monitor counter to see if sql
> is taking too much ram.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
>
|||If something should not work correctly can I simply change these settings
back to the original configuration and reboot?
Thanks, I'll remove the /3gb and replace it with the /pae.
"TheSQLGuru" wrote:
> Do not use /3gb switch if you want sql server to use 4GB. You could
> probably go up to 5.5-6GB or so if this is a dedicated machine.
> Occasionally observe pages/sec performance monitor counter to see if sql is
> taking too much ram.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
>
>
|||Your right, I should have said I wanted to get it to a minimum of 4gb. I
will be setting it at 5.5gb as recommended.
So do I want both /3gb and /pae?
"Andrew J. Kelly" wrote:
> While I also have to ask why you want to limit it to 4GB when you have 8GB
> total I don't agree with the statement not to use the /3GB. Since you will
> have to use AWE to access anything over 3GB anyway that is not a factor. The
> question is can you benefit from the extra 1GB of directly addressable
> memory or not. Since the data buffer pool is the only thing that can use AWE
> memory you need to see if you could use that extra GB for things like proc
> cache, connection memory etc.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13stt0f7jhtnk14@.corp.supernews.com...
>
|||Depending on what server you have, you may or may not need to specify PAE in
boot.ini. For instance, most, if not all the, current HP ProLiant servers
support hot add memory, and don't require PAE. Regardless, if you can already
see 8GB at the OS level, you are all set, and don't have to be bothered with
setting PAE or not.
See this KB for additional info: http://support.microsoft.com/kb/283037
For a dedicated SQL Server with 8GB of RAM, we generally give 6GB or
slightly more to the buffer pool, and generally don't use 3GB unless
otherwise needed.
Linchi
"Ron" wrote:
[vbcol=seagreen]
> Your right, I should have said I wanted to get it to a minimum of 4gb. I
> will be setting it at 5.5gb as recommended.
> So do I want both /3gb and /pae?
>
> "Andrew J. Kelly" wrote:
|||Linchi,
That's interesting. The /PAE is an operating system feature so how does the
new Proliant machines get around not having to set this? I know Windows 2003
has the ability to set it automatically but I haven't heard of the hardware
doing this. Can you elaborate?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:C00A3183-DE64-4F34-93D8-0FE342EC411E@.microsoft.com...[vbcol=seagreen]
> Depending on what server you have, you may or may not need to specify PAE
> in
> boot.ini. For instance, most, if not all the, current HP ProLiant servers
> support hot add memory, and don't require PAE. Regardless, if you can
> already
> see 8GB at the OS level, you are all set, and don't have to be bothered
> with
> setting PAE or not.
> See this KB for additional info: http://support.microsoft.com/kb/283037
> For a dedicated SQL Server with 8GB of RAM, we generally give 6GB or
> slightly more to the buffer pool, and generally don't use 3GB unless
> otherwise needed.
> Linchi
> "Ron" wrote:
Another memory question
Server has 8GB RAM - SQL Server currently takes 1.7GB
I'd like to get SQL Server to about 4gb RAM. I've been reading all the
posts and replies and I think I may have all the info, taking a little from
several posts. I'm just not positive as I haven't seen a step by step (in
the proper order) document or posting. Not sure if Microsoft has a step by
step. I've been through books on-line and have seen all these topics.
Are these the steps in the proper order?
1-Update the boot.ini to add the /3gb switch
2-Enable awe (via SSMS)
3-Select running values or configured values? (via SSMS)
4-Set the SQL Server max memory to 4gb (via SSMS)
5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
6-Reboot server
Anything else?
Thanks
RonYou will also have to turn on the /PAE switch in BOOT.INI. Use sp_configure
to turn on AWE and to set the max server memory.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
Server has 8GB RAM - SQL Server currently takes 1.7GB
I'd like to get SQL Server to about 4gb RAM. I've been reading all the
posts and replies and I think I may have all the info, taking a little from
several posts. I'm just not positive as I haven't seen a step by step (in
the proper order) document or posting. Not sure if Microsoft has a step by
step. I've been through books on-line and have seen all these topics.
Are these the steps in the proper order?
1-Update the boot.ini to add the /3gb switch
2-Enable awe (via SSMS)
3-Select running values or configured values? (via SSMS)
4-Set the SQL Server max memory to 4gb (via SSMS)
5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
6-Reboot server
Anything else?
Thanks
Ron|||Do not use /3gb switch if you want sql server to use 4GB. You could
probably go up to 5.5-6GB or so if this is a dedicated machine.
Occasionally observe pages/sec performance monitor counter to see if sql is
taking too much ram.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> Server has 8GB RAM - SQL Server currently takes 1.7GB
> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> posts and replies and I think I may have all the info, taking a little
> from
> several posts. I'm just not positive as I haven't seen a step by step (in
> the proper order) document or posting. Not sure if Microsoft has a step
> by
> step. I've been through books on-line and have seen all these topics.
> Are these the steps in the proper order?
> 1-Update the boot.ini to add the /3gb switch
> 2-Enable awe (via SSMS)
> 3-Select running values or configured values? (via SSMS)
> 4-Set the SQL Server max memory to 4gb (via SSMS)
> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> 6-Reboot server
> Anything else?
> Thanks
> Ron
>
>|||While I also have to ask why you want to limit it to 4GB when you have 8GB
total I don't agree with the statement not to use the /3GB. Since you will
have to use AWE to access anything over 3GB anyway that is not a factor. The
question is can you benefit from the extra 1GB of directly addressable
memory or not. Since the data buffer pool is the only thing that can use AWE
memory you need to see if you could use that extra GB for things like proc
cache, connection memory etc.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13stt0f7jhtnk14@.corp.supernews.com...
> Do not use /3gb switch if you want sql server to use 4GB. You could
> probably go up to 5.5-6GB or so if this is a dedicated machine.
> Occasionally observe pages/sec performance monitor counter to see if sql
> is taking too much ram.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
>> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
>> Server has 8GB RAM - SQL Server currently takes 1.7GB
>> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
>> posts and replies and I think I may have all the info, taking a little
>> from
>> several posts. I'm just not positive as I haven't seen a step by step
>> (in
>> the proper order) document or posting. Not sure if Microsoft has a step
>> by
>> step. I've been through books on-line and have seen all these topics.
>> Are these the steps in the proper order?
>> 1-Update the boot.ini to add the /3gb switch
>> 2-Enable awe (via SSMS)
>> 3-Select running values or configured values? (via SSMS)
>> 4-Set the SQL Server max memory to 4gb (via SSMS)
>> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
>> 6-Reboot server
>> Anything else?
>> Thanks
>> Ron
>>
>>
>|||If something should not work correctly can I simply change these settings
back to the original configuration and reboot?
Thanks, I'll remove the /3gb and replace it with the /pae.
"TheSQLGuru" wrote:
> Do not use /3gb switch if you want sql server to use 4GB. You could
> probably go up to 5.5-6GB or so if this is a dedicated machine.
> Occasionally observe pages/sec performance monitor counter to see if sql is
> taking too much ram.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> > SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> > Server has 8GB RAM - SQL Server currently takes 1.7GB
> >
> > I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> > posts and replies and I think I may have all the info, taking a little
> > from
> > several posts. I'm just not positive as I haven't seen a step by step (in
> > the proper order) document or posting. Not sure if Microsoft has a step
> > by
> > step. I've been through books on-line and have seen all these topics.
> >
> > Are these the steps in the proper order?
> >
> > 1-Update the boot.ini to add the /3gb switch
> > 2-Enable awe (via SSMS)
> > 3-Select running values or configured values? (via SSMS)
> > 4-Set the SQL Server max memory to 4gb (via SSMS)
> > 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> > 6-Reboot server
> >
> > Anything else?
> >
> > Thanks
> >
> > Ron
> >
> >
> >
> >
>
>|||Your right, I should have said I wanted to get it to a minimum of 4gb. I
will be setting it at 5.5gb as recommended.
So do I want both /3gb and /pae?
"Andrew J. Kelly" wrote:
> While I also have to ask why you want to limit it to 4GB when you have 8GB
> total I don't agree with the statement not to use the /3GB. Since you will
> have to use AWE to access anything over 3GB anyway that is not a factor. The
> question is can you benefit from the extra 1GB of directly addressable
> memory or not. Since the data buffer pool is the only thing that can use AWE
> memory you need to see if you could use that extra GB for things like proc
> cache, connection memory etc.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13stt0f7jhtnk14@.corp.supernews.com...
> > Do not use /3gb switch if you want sql server to use 4GB. You could
> > probably go up to 5.5-6GB or so if this is a dedicated machine.
> > Occasionally observe pages/sec performance monitor counter to see if sql
> > is taking too much ram.
> >
> > --
> > Kevin G. Boles
> > Indicium Resources, Inc.
> > SQL Server MVP
> > kgboles a earthlink dt net
> >
> >
> > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> >> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> >> Server has 8GB RAM - SQL Server currently takes 1.7GB
> >>
> >> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> >> posts and replies and I think I may have all the info, taking a little
> >> from
> >> several posts. I'm just not positive as I haven't seen a step by step
> >> (in
> >> the proper order) document or posting. Not sure if Microsoft has a step
> >> by
> >> step. I've been through books on-line and have seen all these topics.
> >>
> >> Are these the steps in the proper order?
> >>
> >> 1-Update the boot.ini to add the /3gb switch
> >> 2-Enable awe (via SSMS)
> >> 3-Select running values or configured values? (via SSMS)
> >> 4-Set the SQL Server max memory to 4gb (via SSMS)
> >> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> >> 6-Reboot server
> >>
> >> Anything else?
> >>
> >> Thanks
> >>
> >> Ron
> >>
> >>
> >>
> >>
> >
> >
>|||Depending on what server you have, you may or may not need to specify PAE in
boot.ini. For instance, most, if not all the, current HP ProLiant servers
support hot add memory, and don't require PAE. Regardless, if you can already
see 8GB at the OS level, you are all set, and don't have to be bothered with
setting PAE or not.
See this KB for additional info: http://support.microsoft.com/kb/283037
For a dedicated SQL Server with 8GB of RAM, we generally give 6GB or
slightly more to the buffer pool, and generally don't use 3GB unless
otherwise needed.
Linchi
"Ron" wrote:
> Your right, I should have said I wanted to get it to a minimum of 4gb. I
> will be setting it at 5.5gb as recommended.
> So do I want both /3gb and /pae?
>
> "Andrew J. Kelly" wrote:
> > While I also have to ask why you want to limit it to 4GB when you have 8GB
> > total I don't agree with the statement not to use the /3GB. Since you will
> > have to use AWE to access anything over 3GB anyway that is not a factor. The
> > question is can you benefit from the extra 1GB of directly addressable
> > memory or not. Since the data buffer pool is the only thing that can use AWE
> > memory you need to see if you could use that extra GB for things like proc
> > cache, connection memory etc.
> >
> > --
> > Andrew J. Kelly SQL MVP
> > Solid Quality Mentors
> >
> >
> > "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> > news:13stt0f7jhtnk14@.corp.supernews.com...
> > > Do not use /3gb switch if you want sql server to use 4GB. You could
> > > probably go up to 5.5-6GB or so if this is a dedicated machine.
> > > Occasionally observe pages/sec performance monitor counter to see if sql
> > > is taking too much ram.
> > >
> > > --
> > > Kevin G. Boles
> > > Indicium Resources, Inc.
> > > SQL Server MVP
> > > kgboles a earthlink dt net
> > >
> > >
> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > > news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> > >> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> > >> Server has 8GB RAM - SQL Server currently takes 1.7GB
> > >>
> > >> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> > >> posts and replies and I think I may have all the info, taking a little
> > >> from
> > >> several posts. I'm just not positive as I haven't seen a step by step
> > >> (in
> > >> the proper order) document or posting. Not sure if Microsoft has a step
> > >> by
> > >> step. I've been through books on-line and have seen all these topics.
> > >>
> > >> Are these the steps in the proper order?
> > >>
> > >> 1-Update the boot.ini to add the /3gb switch
> > >> 2-Enable awe (via SSMS)
> > >> 3-Select running values or configured values? (via SSMS)
> > >> 4-Set the SQL Server max memory to 4gb (via SSMS)
> > >> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> > >> 6-Reboot server
> > >>
> > >> Anything else?
> > >>
> > >> Thanks
> > >>
> > >> Ron
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> >
> >|||Linchi,
That's interesting. The /PAE is an operating system feature so how does the
new Proliant machines get around not having to set this? I know Windows 2003
has the ability to set it automatically but I haven't heard of the hardware
doing this. Can you elaborate?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:C00A3183-DE64-4F34-93D8-0FE342EC411E@.microsoft.com...
> Depending on what server you have, you may or may not need to specify PAE
> in
> boot.ini. For instance, most, if not all the, current HP ProLiant servers
> support hot add memory, and don't require PAE. Regardless, if you can
> already
> see 8GB at the OS level, you are all set, and don't have to be bothered
> with
> setting PAE or not.
> See this KB for additional info: http://support.microsoft.com/kb/283037
> For a dedicated SQL Server with 8GB of RAM, we generally give 6GB or
> slightly more to the buffer pool, and generally don't use 3GB unless
> otherwise needed.
> Linchi
> "Ron" wrote:
>> Your right, I should have said I wanted to get it to a minimum of 4gb. I
>> will be setting it at 5.5gb as recommended.
>> So do I want both /3gb and /pae?
>>
>> "Andrew J. Kelly" wrote:
>> > While I also have to ask why you want to limit it to 4GB when you have
>> > 8GB
>> > total I don't agree with the statement not to use the /3GB. Since you
>> > will
>> > have to use AWE to access anything over 3GB anyway that is not a
>> > factor. The
>> > question is can you benefit from the extra 1GB of directly addressable
>> > memory or not. Since the data buffer pool is the only thing that can
>> > use AWE
>> > memory you need to see if you could use that extra GB for things like
>> > proc
>> > cache, connection memory etc.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> > Solid Quality Mentors
>> >
>> >
>> > "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> > news:13stt0f7jhtnk14@.corp.supernews.com...
>> > > Do not use /3gb switch if you want sql server to use 4GB. You could
>> > > probably go up to 5.5-6GB or so if this is a dedicated machine.
>> > > Occasionally observe pages/sec performance monitor counter to see if
>> > > sql
>> > > is taking too much ram.
>> > >
>> > > --
>> > > Kevin G. Boles
>> > > Indicium Resources, Inc.
>> > > SQL Server MVP
>> > > kgboles a earthlink dt net
>> > >
>> > >
>> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> > > news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
>> > >> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
>> > >> Server has 8GB RAM - SQL Server currently takes 1.7GB
>> > >>
>> > >> I'd like to get SQL Server to about 4gb RAM. I've been reading all
>> > >> the
>> > >> posts and replies and I think I may have all the info, taking a
>> > >> little
>> > >> from
>> > >> several posts. I'm just not positive as I haven't seen a step by
>> > >> step
>> > >> (in
>> > >> the proper order) document or posting. Not sure if Microsoft has a
>> > >> step
>> > >> by
>> > >> step. I've been through books on-line and have seen all these
>> > >> topics.
>> > >>
>> > >> Are these the steps in the proper order?
>> > >>
>> > >> 1-Update the boot.ini to add the /3gb switch
>> > >> 2-Enable awe (via SSMS)
>> > >> 3-Select running values or configured values? (via SSMS)
>> > >> 4-Set the SQL Server max memory to 4gb (via SSMS)
>> > >> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL
>> > >> user
>> > >> 6-Reboot server
>> > >>
>> > >> Anything else?
>> > >>
>> > >> Thanks
>> > >>
>> > >> Ron
>> > >>
>> > >>
>> > >>
>> > >>
>> > >
>> > >
>> >
>> >
Sunday, February 19, 2012
Analyze / Data Mining Ribbons
I have installed SP2 on my laptop and I have installed the latest Data Mining Add-Ins.
When I open the sample spreadsheet and select the table in a worksheet called "Table Analysis Tools Sample", I dont get Analyze ribbon under Table Tools. Is ther any reson for it?
I dont get Data Mining option either! Is there something I have to do before getting those menus to appear in the ribbon?
I have been through "Getting Started" and set the AS connection to the local AS Server.
Thanks
Sutha
Possibly the Add-ins have been disabled due to an exception or other fault when opening them. You could try the following ...
Open Excel.
From the Office Button in the top left hand corner of the Excel UI, click the "Excel Options" button.
Navigate to the "Add-ins" page.
In the "Manage" drop down (at the bottom of the page) select "Disabled items".
Select the data mining addins and click "Enable".
Close the dialogs and close Excel
Restart Excel.
|||Donald
I did check that out already. Nothing has been disabled.
I did notice something though. If I select "Com Add-Ins" from "Add-ins" page I get the following list.
1. Send To Bluetooth
2. SQLServer.DMClientXLAddIn
3. SQLServer.DMXLAddIn
4. TFC Office Shim
The "SQLServer.DMClientXLAddIn" and "SQLServer.DMXLAddIn" were not selected. If I select and click OK and re-start excel it removes it.
Thanks
Sutha
|||This seems to be a corrupted installation. You could try uninstalling and re-installing the add-ins.
Are you running Vista? If this is the case, can you try the same steps as above (select Com Add-ins, then check the SQLServer add-ins) AFTER starting Excel as Administrator? (Create a shortcut to excel, right click, select run as Admin...)
|||I am having the same problem.The DM Ribbon showed up when I was using the Office 2007 trial edition. After I had someone in my IT department install just Excel 2007 on my computer, I reinstalled the add ins. I receive the following error message when trying to install the com objects: "Not loaded. A run-time error occurred during the loading of the COM add in".
Brian
|||
Did you try the steps that I suggested to restore the add-ins? And were you running as administrator?
|||Yes and yes.|||Then I do suspect the installation is corrupt. I would suggest using MS product support to work through this one. It may be difficult for us to discover over the forum whether this is an Excel or Add-in issue.|||Has anyone resolved this error? We're having the same problem. It can't load mscoree.dll for some reason. We have implemented the steps stated above but excel still can't load the add-in.|||
We're encountering the same problem here. In our case, we needed to install the Add-In to 9 PCs. Only 4 has no problems, the rest has the same common problem. All PCs are running the same specifications (WinXP,512MB).
We've tried checking the following:
1. Re-installed the Add-In
2. Installed SQL2K5 client tools
3. Re-installed the add-in using administrator priviledges
4. Tweaked the registry
5. Checked the configuration using excel options (like what Donald suggested)
Surprisingly, we've encountered a common error status message while trying to enable the DM Add-Ins. “Not loaded. A runtime error occurred during the loading of the COM Add-In”. We encounter this as a status message when trying to select the add-in from the manage dialog box.
Any insights on this one?
|||Sure, I had that error before contacting MS Support for the fix.In my case, it was because the .NET Programmability Support for Excel 2007 was not installed. The Add-ins are written in .NET, so no support means no add-ins.
1. Under Add/Remove Programs, select Office 2007.
2. Select Change.
3. In the pop-up, select Add or Remove Features.
4. Under Microsoft Excel, check to see if .NET Programmability Support is Not Available, and change it to Run from My Computer. (I think this is not installed by default, not sure)
That fixed it for me. If not, check if .NET Framework 2.0 is installed as well. It really seems to me that it's a .NET problem.
Good luck!
-Tim
|||
Tim,
This is freaky. I discovered this about an hour before I received your response.
The .Net stuff is definitely not installed by default in the Excel 2007 typical installation.
Thanks for the tip this solved my problem for the data mining ribbon.
However, I still cannot get the table add-on package to install the table ribbon.
-Brian
Analyze / Data Mining Ribbons
I have installed SP2 on my laptop and I have installed the latest Data Mining Add-Ins.
When I open the sample spreadsheet and select the table in a worksheet called "Table Analysis Tools Sample", I dont get Analyze ribbon under Table Tools. Is ther any reson for it?
I dont get Data Mining option either! Is there something I have to do before getting those menus to appear in the ribbon?
I have been through "Getting Started" and set the AS connection to the local AS Server.
Thanks
Sutha
Possibly the Add-ins have been disabled due to an exception or other fault when opening them. You could try the following ...
Open Excel.
From the Office Button in the top left hand corner of the Excel UI, click the "Excel Options" button.
Navigate to the "Add-ins" page.
In the "Manage" drop down (at the bottom of the page) select "Disabled items".
Select the data mining addins and click "Enable".
Close the dialogs and close Excel
Restart Excel.
|||Donald
I did check that out already. Nothing has been disabled.
I did notice something though. If I select "Com Add-Ins" from "Add-ins" page I get the following list.
1. Send To Bluetooth
2. SQLServer.DMClientXLAddIn
3. SQLServer.DMXLAddIn
4. TFC Office Shim
The "SQLServer.DMClientXLAddIn" and "SQLServer.DMXLAddIn" were not selected. If I select and click OK and re-start excel it removes it.
Thanks
Sutha
|||This seems to be a corrupted installation. You could try uninstalling and re-installing the add-ins.
Are you running Vista? If this is the case, can you try the same steps as above (select Com Add-ins, then check the SQLServer add-ins) AFTER starting Excel as Administrator? (Create a shortcut to excel, right click, select run as Admin...)
|||I am having the same problem.The DM Ribbon showed up when I was using the Office 2007 trial edition. After I had someone in my IT department install just Excel 2007 on my computer, I reinstalled the add ins. I receive the following error message when trying to install the com objects: "Not loaded. A run-time error occurred during the loading of the COM add in".
Brian
|||
Did you try the steps that I suggested to restore the add-ins? And were you running as administrator?
|||Yes and yes.|||Then I do suspect the installation is corrupt. I would suggest using MS product support to work through this one. It may be difficult for us to discover over the forum whether this is an Excel or Add-in issue.|||Has anyone resolved this error? We're having the same problem. It can't load mscoree.dll for some reason. We have implemented the steps stated above but excel still can't load the add-in.|||
We're encountering the same problem here. In our case, we needed to install the Add-In to 9 PCs. Only 4 has no problems, the rest has the same common problem. All PCs are running the same specifications (WinXP,512MB).
We've tried checking the following:
1. Re-installed the Add-In
2. Installed SQL2K5 client tools
3. Re-installed the add-in using administrator priviledges
4. Tweaked the registry
5. Checked the configuration using excel options (like what Donald suggested)
Surprisingly, we've encountered a common error status message while trying to enable the DM Add-Ins. “Not loaded. A runtime error occurred during the loading of the COM Add-In”. We encounter this as a status message when trying to select the add-in from the manage dialog box.
Any insights on this one?
|||Sure, I had that error before contacting MS Support for the fix.In my case, it was because the .NET Programmability Support for Excel 2007 was not installed. The Add-ins are written in .NET, so no support means no add-ins.
1. Under Add/Remove Programs, select Office 2007.
2. Select Change.
3. In the pop-up, select Add or Remove Features.
4. Under Microsoft Excel, check to see if .NET Programmability Support is Not Available, and change it to Run from My Computer. (I think this is not installed by default, not sure)
That fixed it for me. If not, check if .NET Framework 2.0 is installed as well. It really seems to me that it's a .NET problem.
Good luck!
-Tim
|||
Tim,
This is freaky. I discovered this about an hour before I received your response.
The .Net stuff is definitely not installed by default in the Excel 2007 typical installation.
Thanks for the tip this solved my problem for the data mining ribbon.
However, I still cannot get the table add-on package to install the table ribbon.
-Brian
Thursday, February 16, 2012
Analysis Services through Management Studio: cannot connect. Possible workaround.
I have a SQL server 2005 developer edition installation on my Windows XP SP2 workstation. SQL Server was installed after Visual Studio 2005 pro, and therefore after SQL Express. From what I recall, SQL Browser was also installed with SQL Express using the Local System account as the Browser service account.
As per best-security practice, I have installed 2005 developer edition with the accounts for the services as local accounts (different account for each service). Obviously, due to the VS2005 install, Browser was installed using the Local System account. I therefore created a local account for the Browser, and put it into the SQLServer2005SQLBrowserUser$MACHINENAME security group. I then switched the Browser service account to the local account using Configuration Manager, which therefore restarted the Browser service.
Next, when accessing Analysis Services (SSAS) through the management studio (SSMS) I got the following error:
Cannot connect to MACHINENAME\INSTANCENAME
|
--A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)
|
--No connection could be made because the target machine actively refused it (System)
Changing the Browser logon account back to Local System allowed me to connect to SSAS through SSMS (after a Browser service restart).
Reading BOL for 2005, it states that the security permissions required for the Browser service account are:
90\shared\msmdlocal.ini
Read
90\shared
Read, Execute
90\shared\Errordumps
Read, Write
Looking at those folders, I could see that this was the set correctly, as implied through the SQLServer2005SQLBrowserUser$MACHINENAME security group membership which SYSTEM (set through the installation of SQL express, presumably) and the local account were members of. msmdlocal.ini does not exist on my installation.
However, the major clue that I could see was that SYSTEM account had Full access to the Shared and ErrorDumps folders by default; so I then elevated the SQLServer2005SQLBrowserUser$MACHINENAME security permissions to Full on the Shared and ErrorDumps folders, set the Browser service back to the local user account and restarted it. Bingo! I can now access the SSAS through SSMS.
Better still, I immediately reduced the SQLServer2005SQLBrowserUser$MACHINENAME to the recommended settings, restarted the Browser service, and I can still access SSAS through SSMS.
My question is ... why? What happened during my restart under elevated permissions that let the Browser service work properly? I suspect it is something along the lines of the local account taking ownership of something :-s, although I really have no idea. Is this a recognised problem? Is there a KB article on it? Do we have to do similar things to this when we change service accounts for other SQL services?
Andy
Did you use SQL Server Configuration Manager to change account or Windows Computer Manager?
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Configuration Manager. Though I take it you mean the Service console snap-in rather than Computer Manager directly? However the answer is still SSCM.
Andy
|||In this case I dont see any explanation to the behavior you describe.
Can you please try an contact Customer support about this situation.
Thanks.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
There was something in your original description that may explain what is going on. According to your 1st post, you:
1. Change the account of the SQL Browser
2. Then added the account to the SQL Browser User group.
3. Then updated the account through SQL Computer Manger.
You should have only done step 3.
By doing 1 and 2, the SQL Computer Manger could have been tricked into thinking you only wished to changes the password. SQL Computer Manager needs to do other operation, individual to each service, than change the account and update the user group.
This also fits with the problem "going away" after you "fixed" the problem.
Does this seem like a possibility?
|||I had the exact problem as you described, SQL Browser started fine, allowed access to a SQL instance, but actively refused connection to a SSAS instance.
Switching to System solved the problem, but the permanent fix was to increase the permissions to the Shared + Error dump folders as described above.
|||Thanks so much for posting the details of your experience! I had the same problem and was able to fix it because of your post.
Cheers!
Analysis Services through Management Studio: cannot connect. Possible workaround.
I have a SQL server 2005 developer edition installation on my Windows XP SP2 workstation. SQL Server was installed after Visual Studio 2005 pro, and therefore after SQL Express. From what I recall, SQL Browser was also installed with SQL Express using the Local System account as the Browser service account.
As per best-security practice, I have installed 2005 developer edition with the accounts for the services as local accounts (different account for each service). Obviously, due to the VS2005 install, Browser was installed using the Local System account. I therefore created a local account for the Browser, and put it into the SQLServer2005SQLBrowserUser$MACHINENAME security group. I then switched the Browser service account to the local account using Configuration Manager, which therefore restarted the Browser service.
Next, when accessing Analysis Services (SSAS) through the management studio (SSMS) I got the following error:
Cannot connect to MACHINENAME\INSTANCENAME
|
--A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)
|
--No connection could be made because the target machine actively refused it (System)
Changing the Browser logon account back to Local System allowed me to connect to SSAS through SSMS (after a Browser service restart).
Reading BOL for 2005, it states that the security permissions required for the Browser service account are:
90\shared\msmdlocal.ini
Read
90\shared
Read, Execute
90\shared\Errordumps
Read, Write
Looking at those folders, I could see that this was the set correctly, as implied through the SQLServer2005SQLBrowserUser$MACHINENAME security group membership which SYSTEM (set through the installation of SQL express, presumably) and the local account were members of. msmdlocal.ini does not exist on my installation.
However, the major clue that I could see was that SYSTEM account had Full access to the Shared and ErrorDumps folders by default; so I then elevated the SQLServer2005SQLBrowserUser$MACHINENAME security permissions to Full on the Shared and ErrorDumps folders, set the Browser service back to the local user account and restarted it. Bingo! I can now access the SSAS through SSMS.
Better still, I immediately reduced the SQLServer2005SQLBrowserUser$MACHINENAME to the recommended settings, restarted the Browser service, and I can still access SSAS through SSMS.
My question is ... why? What happened during my restart under elevated permissions that let the Browser service work properly? I suspect it is something along the lines of the local account taking ownership of something :-s, although I really have no idea. Is this a recognised problem? Is there a KB article on it? Do we have to do similar things to this when we change service accounts for other SQL services?
Andy
Did you use SQL Server Configuration Manager to change account or Windows Computer Manager?
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Configuration Manager. Though I take it you mean the Service console snap-in rather than Computer Manager directly? However the answer is still SSCM.
Andy
|||In this case I dont see any explanation to the behavior you describe.
Can you please try an contact Customer support about this situation.
Thanks.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
There was something in your original description that may explain what is going on. According to your 1st post, you:
1. Change the account of the SQL Browser
2. Then added the account to the SQL Browser User group.
3. Then updated the account through SQL Computer Manger.
You should have only done step 3.
By doing 1 and 2, the SQL Computer Manger could have been tricked into thinking you only wished to changes the password. SQL Computer Manager needs to do other operation, individual to each service, than change the account and update the user group.
This also fits with the problem "going away" after you "fixed" the problem.
Does this seem like a possibility?
|||I had the exact problem as you described, SQL Browser started fine, allowed access to a SQL instance, but actively refused connection to a SSAS instance.
Switching to System solved the problem, but the permanent fix was to increase the permissions to the Shared + Error dump folders as described above.
|||Thanks so much for posting the details of your experience! I had the same problem and was able to fix it because of your post.
Cheers!
Analysis Services started and then stopped, Event ID: 0xC1210000
I just installed SQL Server 2005 Developer Edition and SP1 and SP2. I cannot start Analysis Services. The message box states: The SQL Server Analysis Services (MSSQLSERVER) service on Local Computer started and then stopped. I looked in the log files located at: C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log and found these entries:
msmdsrv.log:
(6/15/2007 10:07:34 AM)
Message: The service cannot be started:
(Source: \\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log\msmdsrv.log,
Type: 3, Category: 289, Event ID: 0xC1210000)
?(6/15/2007 10:10:26 AM) Message:
The service cannot be started:
(Source: \\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log\msmdsrv.log,
Type: 3, Category: 289, Event ID: 0xC1210000)
During installation I did get an unhandled exception (I don't know what component caused the exception). I have found this in the SQLDUMPER_ERRORLOG.log file:
06/14/07 17:39:38, ERROR , msmdsrv.exe, Unable to open file C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\FlightRecorderCurrent.trc, error 2
06/14/07 17:39:38, ACTION, msmdsrv.exe, Watson Invoke: No
Any ideas how to get this service started?
I had the same problem as you discribed. Then I tried to reapply SP2. It works fine now.
I guess when you first applied SP2, analysis service was not installed and therefore the patch is not installed. Download the latest SP2 and try it again:
http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en
good luck!