I use the job processing functionality to do a variety of things by running
DTS packages. One of my DTS packages has been failing as run a .NET
application to convert/modify the data extracted from an Oracle Database in a
previous step. I get an exception "There is insufficient system memory to
run this query." when the application runs under the Agent but not when I run
the same package interactively. Is there some restriction on memory use for
jobs running under the SQL Agent? If this is so is there someway to modify
it?
thanks for any assistance
Mike Mattix
CP Kelco, Inc
Okmulgee, OK
Hi
No. But how much RAM do you have and how much is allocated the SQL Server?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
>I use the job processing functionality to do a variety of things by running
> DTS packages. One of my DTS packages has been failing as run a .NET
> application to convert/modify the data extracted from an Oracle Database
> in a
> previous step. I get an exception "There is insufficient system memory to
> run this query." when the application runs under the Agent but not when I
> run
> the same package interactively. Is there some restriction on memory use
> for
> jobs running under the SQL Agent? If this is so is there someway to
> modify
> it?
> thanks for any assistance
> --
> Mike Mattix
> CP Kelco, Inc
> Okmulgee, OK
|||Mike,
Thanks for the reply. The server has 3GB and SQL is setup to dynamically
manage it memory. Min=0, Max=3GB, Min Query= 1GB
Mike Mattix
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> No. But how much RAM do you have and how much is allocated the SQL Server?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
> news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
>
>
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Friday, March 30, 2012
Memory Allocation for Jobs Running under SQL Agent
I use the job processing functionality to do a variety of things by running
DTS packages. One of my DTS packages has been failing as run a .NET
application to convert/modify the data extracted from an Oracle Database in
a
previous step. I get an exception "There is insufficient system memory to
run this query." when the application runs under the Agent but not when I ru
n
the same package interactively. Is there some restriction on memory use for
jobs running under the SQL Agent? If this is so is there someway to modify
it?
thanks for any assistance
Mike Mattix
CP Kelco, Inc
Okmulgee, OKHi
No. But how much RAM do you have and how much is allocated the SQL Server?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
>I use the job processing functionality to do a variety of things by running
> DTS packages. One of my DTS packages has been failing as run a .NET
> application to convert/modify the data extracted from an Oracle Database
> in a
> previous step. I get an exception "There is insufficient system memory to
> run this query." when the application runs under the Agent but not when I
> run
> the same package interactively. Is there some restriction on memory use
> for
> jobs running under the SQL Agent? If this is so is there someway to
> modify
> it?
> thanks for any assistance
> --
> Mike Mattix
> CP Kelco, Inc
> Okmulgee, OK|||Mike,
Thanks for the reply. The server has 3GB and SQL is setup to dynamically
manage it memory. Min=0, Max=3GB, Min Query= 1GB
Mike Mattix
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> No. But how much RAM do you have and how much is allocated the SQL Server?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
> news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
>
>
DTS packages. One of my DTS packages has been failing as run a .NET
application to convert/modify the data extracted from an Oracle Database in
a
previous step. I get an exception "There is insufficient system memory to
run this query." when the application runs under the Agent but not when I ru
n
the same package interactively. Is there some restriction on memory use for
jobs running under the SQL Agent? If this is so is there someway to modify
it?
thanks for any assistance
Mike Mattix
CP Kelco, Inc
Okmulgee, OKHi
No. But how much RAM do you have and how much is allocated the SQL Server?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
>I use the job processing functionality to do a variety of things by running
> DTS packages. One of my DTS packages has been failing as run a .NET
> application to convert/modify the data extracted from an Oracle Database
> in a
> previous step. I get an exception "There is insufficient system memory to
> run this query." when the application runs under the Agent but not when I
> run
> the same package interactively. Is there some restriction on memory use
> for
> jobs running under the SQL Agent? If this is so is there someway to
> modify
> it?
> thanks for any assistance
> --
> Mike Mattix
> CP Kelco, Inc
> Okmulgee, OK|||Mike,
Thanks for the reply. The server has 3GB and SQL is setup to dynamically
manage it memory. Min=0, Max=3GB, Min Query= 1GB
Mike Mattix
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> No. But how much RAM do you have and how much is allocated the SQL Server?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
> news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
>
>
Memory Allocation for Jobs Running under SQL Agent
I use the job processing functionality to do a variety of things by running
DTS packages. One of my DTS packages has been failing as run a .NET
application to convert/modify the data extracted from an Oracle Database in a
previous step. I get an exception "There is insufficient system memory to
run this query." when the application runs under the Agent but not when I run
the same package interactively. Is there some restriction on memory use for
jobs running under the SQL Agent? If this is so is there someway to modify
it?
thanks for any assistance
--
Mike Mattix
CP Kelco, Inc
Okmulgee, OKHi
No. But how much RAM do you have and how much is allocated the SQL Server?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
>I use the job processing functionality to do a variety of things by running
> DTS packages. One of my DTS packages has been failing as run a .NET
> application to convert/modify the data extracted from an Oracle Database
> in a
> previous step. I get an exception "There is insufficient system memory to
> run this query." when the application runs under the Agent but not when I
> run
> the same package interactively. Is there some restriction on memory use
> for
> jobs running under the SQL Agent? If this is so is there someway to
> modify
> it?
> thanks for any assistance
> --
> Mike Mattix
> CP Kelco, Inc
> Okmulgee, OK|||Mike,
Thanks for the reply. The server has 3GB and SQL is setup to dynamically
manage it memory. Min=0, Max=3GB, Min Query= 1GB
Mike Mattix
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> No. But how much RAM do you have and how much is allocated the SQL Server?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
> news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
> >I use the job processing functionality to do a variety of things by running
> > DTS packages. One of my DTS packages has been failing as run a .NET
> > application to convert/modify the data extracted from an Oracle Database
> > in a
> > previous step. I get an exception "There is insufficient system memory to
> > run this query." when the application runs under the Agent but not when I
> > run
> > the same package interactively. Is there some restriction on memory use
> > for
> > jobs running under the SQL Agent? If this is so is there someway to
> > modify
> > it?
> >
> > thanks for any assistance
> >
> > --
> > Mike Mattix
> > CP Kelco, Inc
> > Okmulgee, OK
>
>
DTS packages. One of my DTS packages has been failing as run a .NET
application to convert/modify the data extracted from an Oracle Database in a
previous step. I get an exception "There is insufficient system memory to
run this query." when the application runs under the Agent but not when I run
the same package interactively. Is there some restriction on memory use for
jobs running under the SQL Agent? If this is so is there someway to modify
it?
thanks for any assistance
--
Mike Mattix
CP Kelco, Inc
Okmulgee, OKHi
No. But how much RAM do you have and how much is allocated the SQL Server?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
>I use the job processing functionality to do a variety of things by running
> DTS packages. One of my DTS packages has been failing as run a .NET
> application to convert/modify the data extracted from an Oracle Database
> in a
> previous step. I get an exception "There is insufficient system memory to
> run this query." when the application runs under the Agent but not when I
> run
> the same package interactively. Is there some restriction on memory use
> for
> jobs running under the SQL Agent? If this is so is there someway to
> modify
> it?
> thanks for any assistance
> --
> Mike Mattix
> CP Kelco, Inc
> Okmulgee, OK|||Mike,
Thanks for the reply. The server has 3GB and SQL is setup to dynamically
manage it memory. Min=0, Max=3GB, Min Query= 1GB
Mike Mattix
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> No. But how much RAM do you have and how much is allocated the SQL Server?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Mike Mattix" <MikeMattix@.discussions.microsoft.com> wrote in message
> news:20484DEE-330A-4ABB-B4D1-29C6CE1AB488@.microsoft.com...
> >I use the job processing functionality to do a variety of things by running
> > DTS packages. One of my DTS packages has been failing as run a .NET
> > application to convert/modify the data extracted from an Oracle Database
> > in a
> > previous step. I get an exception "There is insufficient system memory to
> > run this query." when the application runs under the Agent but not when I
> > run
> > the same package interactively. Is there some restriction on memory use
> > for
> > jobs running under the SQL Agent? If this is so is there someway to
> > modify
> > it?
> >
> > thanks for any assistance
> >
> > --
> > Mike Mattix
> > CP Kelco, Inc
> > Okmulgee, OK
>
>
Memory Allocation 8192
I have windows 2003 with 4 CPU's and 32GB of memory running multiple
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just curious
if any else has seen this problem with this "magic" number.
We have seen this problem, and I believe that a fix is being considered for SQL 2000 SP4.
Thanks!
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Rich" <Rich@.discussions.microsoft.com> wrote in message news:C90B5CAD-773E-4A75-8A99-06DBFC8A3C6C@.microsoft.com...
I have windows 2003 with 4 CPU's and 32GB of memory running multiple
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just curious
if any else has seen this problem with this "magic" number.
|||Ryan, were you not assisting on replication a year or 2 ago ?
"Ryan Stonecipher [MSFT]" <ryanston@.online.microsoft.com> wrote in message
news:u3CXMbKhEHA.1656@.TK2MSFTNGP09.phx.gbl...
We have seen this problem, and I believe that a fix is being considered for
SQL 2000 SP4.
Thanks!
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C90B5CAD-773E-4A75-8A99-06DBFC8A3C6C@.microsoft.com...
I have windows 2003 with 4 CPU's and 32GB of memory running multiple
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just
curious
if any else has seen this problem with this "magic" number.
sql
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just curious
if any else has seen this problem with this "magic" number.
We have seen this problem, and I believe that a fix is being considered for SQL 2000 SP4.
Thanks!
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Rich" <Rich@.discussions.microsoft.com> wrote in message news:C90B5CAD-773E-4A75-8A99-06DBFC8A3C6C@.microsoft.com...
I have windows 2003 with 4 CPU's and 32GB of memory running multiple
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just curious
if any else has seen this problem with this "magic" number.
|||Ryan, were you not assisting on replication a year or 2 ago ?
"Ryan Stonecipher [MSFT]" <ryanston@.online.microsoft.com> wrote in message
news:u3CXMbKhEHA.1656@.TK2MSFTNGP09.phx.gbl...
We have seen this problem, and I believe that a fix is being considered for
SQL 2000 SP4.
Thanks!
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C90B5CAD-773E-4A75-8A99-06DBFC8A3C6C@.microsoft.com...
I have windows 2003 with 4 CPU's and 32GB of memory running multiple
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just
curious
if any else has seen this problem with this "magic" number.
sql
Memory Allocation 8192
I have windows 2003 with 4 CPU's and 32GB of memory running multiple
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just curiou
s
if any else has seen this problem with this "magic" number.We have seen this problem, and I believe that a fix is being considered for
SQL 2000 SP4.
Thanks!
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Rich" <Rich@.discussions.microsoft.com> wrote in message news:C90B5CAD-773E-
4A75-8A99-06DBFC8A3C6C@.microsoft.com...
I have windows 2003 with 4 CPU's and 32GB of memory running multiple
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just curiou
s
if any else has seen this problem with this "magic" number.|||Ryan, were you not assisting on replication a year or 2 ago ?
"Ryan Stonecipher [MSFT]" <ryanston@.online.microsoft.com> wrote in messa
ge
news:u3CXMbKhEHA.1656@.TK2MSFTNGP09.phx.gbl...
We have seen this problem, and I believe that a fix is being considered for
SQL 2000 SP4.
Thanks!
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C90B5CAD-773E-4A75-8A99-06DBFC8A3C6C@.microsoft.com...
I have windows 2003 with 4 CPU's and 32GB of memory running multiple
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just
curious
if any else has seen this problem with this "magic" number.
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just curiou
s
if any else has seen this problem with this "magic" number.We have seen this problem, and I believe that a fix is being considered for
SQL 2000 SP4.
Thanks!
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Rich" <Rich@.discussions.microsoft.com> wrote in message news:C90B5CAD-773E-
4A75-8A99-06DBFC8A3C6C@.microsoft.com...
I have windows 2003 with 4 CPU's and 32GB of memory running multiple
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just curiou
s
if any else has seen this problem with this "magic" number.|||Ryan, were you not assisting on replication a year or 2 ago ?
"Ryan Stonecipher [MSFT]" <ryanston@.online.microsoft.com> wrote in messa
ge
news:u3CXMbKhEHA.1656@.TK2MSFTNGP09.phx.gbl...
We have seen this problem, and I believe that a fix is being considered for
SQL 2000 SP4.
Thanks!
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C90B5CAD-773E-4A75-8A99-06DBFC8A3C6C@.microsoft.com...
I have windows 2003 with 4 CPU's and 32GB of memory running multiple
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just
curious
if any else has seen this problem with this "magic" number.
Memory Allocation 8192
I have windows 2003 with 4 CPU's and 32GB of memory running multiple
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just curious
if any else has seen this problem with this "magic" number.This is a multi-part message in MIME format.
--=_NextPart_000_0016_01C4846C.1D613220
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
We have seen this problem, and I believe that a fix is being considered =for SQL 2000 SP4.
Thanks!
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Rich" <Rich@.discussions.microsoft.com> wrote in message =news:C90B5CAD-773E-4A75-8A99-06DBFC8A3C6C@.microsoft.com...
I have windows 2003 with 4 CPU's and 32GB of memory running multiple instances of SQL. Server sees the full 32GB of memory without any =problems with the /PAE option enabled. Problem is when a instance is =configured for the exact size of 8192mb the instance has a hard time starting. It =fails multiple times and what when a normal startup takes 30 seconds can now =10 minutes or more to get going. Any instance goes back to normal by =changing to a different number other than 8192 as the memory allocation. Just =curious if any else has seen this problem with this "magic" number.
--=_NextPart_000_0016_01C4846C.1D613220
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
We have seen this problem, and =I believe that a fix is being considered for SQL 2000 SP4.
Thanks!
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Rich" wrote in message news:C90=B5CAD-773E-4A75-8A99-06DBFC8A3C6C@.microsoft.com...I have windows 2003 with 4 CPU's and 32GB of memory running multiple instances of SQL. Server sees the full 32GB of memory =without any problems with the /PAE option enabled. Problem is when a =instance is configured for the exact size of 8192mb the instance has a hard =time starting. It fails multiple times and what when a normal =startup takes 30 seconds can now 10 minutes or more to get going. =Any instance goes back to normal by changing to a different number =other than 8192 as the memory allocation. Just curious if any else has =seen this problem with this "magic" number.
--=_NextPart_000_0016_01C4846C.1D613220--|||Ryan, were you not assisting on replication a year or 2 ago ?
"Ryan Stonecipher [MSFT]" <ryanston@.online.microsoft.com> wrote in message
news:u3CXMbKhEHA.1656@.TK2MSFTNGP09.phx.gbl...
We have seen this problem, and I believe that a fix is being considered for
SQL 2000 SP4.
Thanks!
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C90B5CAD-773E-4A75-8A99-06DBFC8A3C6C@.microsoft.com...
I have windows 2003 with 4 CPU's and 32GB of memory running multiple
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just
curious
if any else has seen this problem with this "magic" number.
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just curious
if any else has seen this problem with this "magic" number.This is a multi-part message in MIME format.
--=_NextPart_000_0016_01C4846C.1D613220
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
We have seen this problem, and I believe that a fix is being considered =for SQL 2000 SP4.
Thanks!
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Rich" <Rich@.discussions.microsoft.com> wrote in message =news:C90B5CAD-773E-4A75-8A99-06DBFC8A3C6C@.microsoft.com...
I have windows 2003 with 4 CPU's and 32GB of memory running multiple instances of SQL. Server sees the full 32GB of memory without any =problems with the /PAE option enabled. Problem is when a instance is =configured for the exact size of 8192mb the instance has a hard time starting. It =fails multiple times and what when a normal startup takes 30 seconds can now =10 minutes or more to get going. Any instance goes back to normal by =changing to a different number other than 8192 as the memory allocation. Just =curious if any else has seen this problem with this "magic" number.
--=_NextPart_000_0016_01C4846C.1D613220
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
We have seen this problem, and =I believe that a fix is being considered for SQL 2000 SP4.
Thanks!
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Rich"
--=_NextPart_000_0016_01C4846C.1D613220--|||Ryan, were you not assisting on replication a year or 2 ago ?
"Ryan Stonecipher [MSFT]" <ryanston@.online.microsoft.com> wrote in message
news:u3CXMbKhEHA.1656@.TK2MSFTNGP09.phx.gbl...
We have seen this problem, and I believe that a fix is being considered for
SQL 2000 SP4.
Thanks!
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C90B5CAD-773E-4A75-8A99-06DBFC8A3C6C@.microsoft.com...
I have windows 2003 with 4 CPU's and 32GB of memory running multiple
instances of SQL. Server sees the full 32GB of memory without any problems
with the /PAE option enabled. Problem is when a instance is configured for
the exact size of 8192mb the instance has a hard time starting. It fails
multiple times and what when a normal startup takes 30 seconds can now 10
minutes or more to get going. Any instance goes back to normal by changing
to a different number other than 8192 as the memory allocation. Just
curious
if any else has seen this problem with this "magic" number.
Memory allocation ?
I am running SQL Server 2000 Enterprise edition on Windows Server 2003
Enterprise, do I need to do anything special to utilize >2GB of Ram?
We have 4GB, but I don't think we are using very much of it. Is there a
easy way I can find out what we are currently limited to?
I read somewhere on groups that if "SQLServer:Buffer Manager - Page life
expectancy" in perfmon dips below 300 it could be a sign of too little ram.
Thanks!Jesse
Have you tried to switch in the .ini file \3GB? SQL Server will be able to
use 2GB and 1GB for OS.
"Jesse Gardner" <ih8spam@.badcrc.com> wrote in message
news:2sdv2bF1j6t65U1@.uni-berlin.de...
> I am running SQL Server 2000 Enterprise edition on Windows Server 2003
> Enterprise, do I need to do anything special to utilize >2GB of Ram?
> We have 4GB, but I don't think we are using very much of it. Is there a
> easy way I can find out what we are currently limited to?
> I read somewhere on groups that if "SQLServer:Buffer Manager - Page life
> expectancy" in perfmon dips below 300 it could be a sign of too little
ram.
>
> Thanks!
Enterprise, do I need to do anything special to utilize >2GB of Ram?
We have 4GB, but I don't think we are using very much of it. Is there a
easy way I can find out what we are currently limited to?
I read somewhere on groups that if "SQLServer:Buffer Manager - Page life
expectancy" in perfmon dips below 300 it could be a sign of too little ram.
Thanks!Jesse
Have you tried to switch in the .ini file \3GB? SQL Server will be able to
use 2GB and 1GB for OS.
"Jesse Gardner" <ih8spam@.badcrc.com> wrote in message
news:2sdv2bF1j6t65U1@.uni-berlin.de...
> I am running SQL Server 2000 Enterprise edition on Windows Server 2003
> Enterprise, do I need to do anything special to utilize >2GB of Ram?
> We have 4GB, but I don't think we are using very much of it. Is there a
> easy way I can find out what we are currently limited to?
> I read somewhere on groups that if "SQLServer:Buffer Manager - Page life
> expectancy" in perfmon dips below 300 it could be a sign of too little
ram.
>
> Thanks!
Memory allocation ?
I am running SQL Server 2000 Enterprise edition on Windows Server 2003
Enterprise, do I need to do anything special to utilize >2GB of Ram?
We have 4GB, but I don't think we are using very much of it. Is there a
easy way I can find out what we are currently limited to?
I read somewhere on groups that if "SQLServer:Buffer Manager - Page life
expectancy" in perfmon dips below 300 it could be a sign of too little ram.
Thanks!
Jesse
Have you tried to switch in the .ini file \3GB? SQL Server will be able to
use 2GB and 1GB for OS.
"Jesse Gardner" <ih8spam@.badcrc.com> wrote in message
news:2sdv2bF1j6t65U1@.uni-berlin.de...
> I am running SQL Server 2000 Enterprise edition on Windows Server 2003
> Enterprise, do I need to do anything special to utilize >2GB of Ram?
> We have 4GB, but I don't think we are using very much of it. Is there a
> easy way I can find out what we are currently limited to?
> I read somewhere on groups that if "SQLServer:Buffer Manager - Page life
> expectancy" in perfmon dips below 300 it could be a sign of too little
ram.
>
> Thanks!
Enterprise, do I need to do anything special to utilize >2GB of Ram?
We have 4GB, but I don't think we are using very much of it. Is there a
easy way I can find out what we are currently limited to?
I read somewhere on groups that if "SQLServer:Buffer Manager - Page life
expectancy" in perfmon dips below 300 it could be a sign of too little ram.
Thanks!
Jesse
Have you tried to switch in the .ini file \3GB? SQL Server will be able to
use 2GB and 1GB for OS.
"Jesse Gardner" <ih8spam@.badcrc.com> wrote in message
news:2sdv2bF1j6t65U1@.uni-berlin.de...
> I am running SQL Server 2000 Enterprise edition on Windows Server 2003
> Enterprise, do I need to do anything special to utilize >2GB of Ram?
> We have 4GB, but I don't think we are using very much of it. Is there a
> easy way I can find out what we are currently limited to?
> I read somewhere on groups that if "SQLServer:Buffer Manager - Page life
> expectancy" in perfmon dips below 300 it could be a sign of too little
ram.
>
> Thanks!
Wednesday, March 28, 2012
Memory : Pool Paged Bytes Increasing until Database Server Hang Up
Hi,
I have a SQL2000 server that is running a DTS continuously and I
discover that
my server pool paged bytes keep increasing until the whole db server
would hang up and I need to reboot the db server.
Details of my db server :
OS Patch : SP3
SQL Patch : SP3
Memory : 3GB
I have tried monitoring all the process but none of them seems to be
increasing. These are the process that I am monitoring.
-Memory : Available Bytes
-Memory : Pool Paged Bytes
-Process : Handle Count
-Process : Non Paged Bytes
-Process : Paged Bytes
-Process : Private Bytes
Is there anything that I can monitor to check why my memory:pool paged
bytes is increasing?
Appreciate your help.
ThanksMike,
Did you ever find an answer to your question? I am having a similar
issue with my servers.
jtw
On 28 Aug 2003 19:45:38 -0700, MikeLee@.techsemicon.com.sg (Mike Lee)
wrote:
>Hi,
>
>I have a SQL2000 server that is running a DTS continuously and I
>discover that
>my server pool paged bytes keep increasing until the whole db server
>would hang up and I need to reboot the db server.
>Details of my db server :
>OS Patch : SP3
>SQL Patch : SP3
>Memory : 3GB
>I have tried monitoring all the process but none of them seems to be
>increasing. These are the process that I am monitoring.
>-Memory : Available Bytes
>-Memory : Pool Paged Bytes
>-Process : Handle Count
>-Process : Non Paged Bytes
>-Process : Paged Bytes
>-Process : Private Bytes
>Is there anything that I can monitor to check why my memory:pool paged
>bytes is increasing?
>Appreciate your help.
>Thanks
--
http://www.UsenetRocket.comsql
I have a SQL2000 server that is running a DTS continuously and I
discover that
my server pool paged bytes keep increasing until the whole db server
would hang up and I need to reboot the db server.
Details of my db server :
OS Patch : SP3
SQL Patch : SP3
Memory : 3GB
I have tried monitoring all the process but none of them seems to be
increasing. These are the process that I am monitoring.
-Memory : Available Bytes
-Memory : Pool Paged Bytes
-Process : Handle Count
-Process : Non Paged Bytes
-Process : Paged Bytes
-Process : Private Bytes
Is there anything that I can monitor to check why my memory:pool paged
bytes is increasing?
Appreciate your help.
ThanksMike,
Did you ever find an answer to your question? I am having a similar
issue with my servers.
jtw
On 28 Aug 2003 19:45:38 -0700, MikeLee@.techsemicon.com.sg (Mike Lee)
wrote:
>Hi,
>
>I have a SQL2000 server that is running a DTS continuously and I
>discover that
>my server pool paged bytes keep increasing until the whole db server
>would hang up and I need to reboot the db server.
>Details of my db server :
>OS Patch : SP3
>SQL Patch : SP3
>Memory : 3GB
>I have tried monitoring all the process but none of them seems to be
>increasing. These are the process that I am monitoring.
>-Memory : Available Bytes
>-Memory : Pool Paged Bytes
>-Process : Handle Count
>-Process : Non Paged Bytes
>-Process : Paged Bytes
>-Process : Private Bytes
>Is there anything that I can monitor to check why my memory:pool paged
>bytes is increasing?
>Appreciate your help.
>Thanks
--
http://www.UsenetRocket.comsql
memory
Hi,
can anyone point me in the direction of any articles or whitepapers that give some ideas on memory requirements for running SQL Server 2005 analysis services.
Say we have 250 concurrent users, can anyone tell how much memory I would require. I know this will also depend on cube sizes and number of dimensions, in our case we have approximately 20 AS databases with an estimated 300 cubes in total. Some of the dimensions get up to 100,000 members.
Thinking that 3gb (32bit mode) isn't going to cut it and we will need to run in 64 bit mode for the additional memory availability.
Need some help with concret figures, and how I can prove that we need to run in the 64bit mode.
Any assistance much appreciated.
Thanks Mick
You are correct about going with 64bit hardware. That is better choice for enterprise-level applications.
About memory consumption. It is hard to predict what it is going to be. Memory consumption can go up depending on various conditions:
You using dimension or some kind of security where you have many different security settings for different users. In this case Analysis Server would have to create separate caches for every set of users with similar security settings.
Your dimensions are large and users are querying lots of dimension data.
You've defined calculations on your cube requiring lots of memory to compute.
Besides these considerations, you should definitely try and run some simulations with your application. Memory often is something you can add later to your system. Try getting machine with 6 or 8 gigs try running with it and see if you need some more.
If your simulations show the bottelneck for application is not memory, but CPU, you should also look into setting another machine side by side with the first one. Set them to work in NLB cluster to be able to handle greater load.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Monday, March 26, 2012
Memo Field Comparison
I have SQL Server 7 running.
I recieve input from the user via a PHP form.
I have to match that input to the contents fo the memo field.
No matter what I do I receive errors like
1. Cannot compare VARCHAR
2.Warning: SQL error: [MERANT][ODBC SQLBase driver][SQLBase]00979 PRS PSR Plus sign required for outer join, SQL state 37000 in SQLExecDirect in F:\www\Apache2\htdocs\include\func_db.inc on line 25
generated by this code:
$bits_search_string = trim($bits_search_string);
$sql = "SELECT P.ID, P.DESCRIPTION"
. " FROM PART P, PART_BINARY PB"
. " WHERE P.ID = PB.PART_ID "
. " AND CONTAINS(PB.BITS, '$bits_search_string')";
or the latest
3.Warning: SQL error: [Microsoft][ODBC Cursor Library] Positioned request cannot be performed because result set was generated by a join condition, SQL state SL002 in SQLGetData in F:\www\Apache2\htdocs\include\func_db.inc on line 42
generated by this code:
$bits_search_string = trim($bits_search_string);
$sql = "SELECT P.ID, P.DESCRIPTION, PB.BITS"
. " FROM PART P, PART_BINARY PB"
. " WHERE P.ID = PB.PART_ID ";
$vdb->query($sql);
while ($row = $vdb->getRow(true)){
$memo = $row['PB.BITS'];
Where PB.BITS is the retarded memo field.
I can not change the database to have the field as text, there are
legacy applications dependent upon it.
I would appreciate any insight on how the memo field is structured.
What can I do to manipulate it. I can use PHP or JSP to create this
function. Those are the limitations.
Thank you very much,
I appreciate any feedback.There are a lot of usefull string functions that cannot be used on text (memo) fields, but you can use the CAST or CONVERT functions within your sql query to treat the fields as varchar datatypes. The disadvantage is that you can't index them.
blindman
I recieve input from the user via a PHP form.
I have to match that input to the contents fo the memo field.
No matter what I do I receive errors like
1. Cannot compare VARCHAR
2.Warning: SQL error: [MERANT][ODBC SQLBase driver][SQLBase]00979 PRS PSR Plus sign required for outer join, SQL state 37000 in SQLExecDirect in F:\www\Apache2\htdocs\include\func_db.inc on line 25
generated by this code:
$bits_search_string = trim($bits_search_string);
$sql = "SELECT P.ID, P.DESCRIPTION"
. " FROM PART P, PART_BINARY PB"
. " WHERE P.ID = PB.PART_ID "
. " AND CONTAINS(PB.BITS, '$bits_search_string')";
or the latest
3.Warning: SQL error: [Microsoft][ODBC Cursor Library] Positioned request cannot be performed because result set was generated by a join condition, SQL state SL002 in SQLGetData in F:\www\Apache2\htdocs\include\func_db.inc on line 42
generated by this code:
$bits_search_string = trim($bits_search_string);
$sql = "SELECT P.ID, P.DESCRIPTION, PB.BITS"
. " FROM PART P, PART_BINARY PB"
. " WHERE P.ID = PB.PART_ID ";
$vdb->query($sql);
while ($row = $vdb->getRow(true)){
$memo = $row['PB.BITS'];
Where PB.BITS is the retarded memo field.
I can not change the database to have the field as text, there are
legacy applications dependent upon it.
I would appreciate any insight on how the memo field is structured.
What can I do to manipulate it. I can use PHP or JSP to create this
function. Those are the limitations.
Thank you very much,
I appreciate any feedback.There are a lot of usefull string functions that cannot be used on text (memo) fields, but you can use the CAST or CONVERT functions within your sql query to treat the fields as varchar datatypes. The disadvantage is that you can't index them.
blindman
Wednesday, March 21, 2012
Measuring Login Times
I'm looking for a way to measure how long it takes to log into a SQL 2005
server from a web server running IIS 6. Does anyone have any ideas?
I've tried using the Profiler, but I haven't been able to think of a way to
capture this info
TIA
--
MGMGeles wrote:
> I'm looking for a way to measure how long it takes to log into a SQL 2005
> server from a web server running IIS 6. Does anyone have any ideas?
> I've tried using the Profiler, but I haven't been able to think of a way t
o
> capture this info
> TIA
> --
> MG
AFAIK, SQL 2005 tracks only when a login is successful or unsuccessful,
not the time it takes to make the connection. You may need to use a
network sniffer to capture traffic between the servers, if that's what
you're trying to measure.
Stavros
server from a web server running IIS 6. Does anyone have any ideas?
I've tried using the Profiler, but I haven't been able to think of a way to
capture this info
TIA
--
MGMGeles wrote:
> I'm looking for a way to measure how long it takes to log into a SQL 2005
> server from a web server running IIS 6. Does anyone have any ideas?
> I've tried using the Profiler, but I haven't been able to think of a way t
o
> capture this info
> TIA
> --
> MG
AFAIK, SQL 2005 tracks only when a login is successful or unsuccessful,
not the time it takes to make the connection. You may need to use a
network sniffer to capture traffic between the servers, if that's what
you're trying to measure.
Stavros
Measuring Login Times
I'm looking for a way to measure how long it takes to log into a SQL 2005
server from a web server running IIS 6. Does anyone have any ideas?
I've tried using the Profiler, but I haven't been able to think of a way to
capture this info
TIA
--
MGMGeles wrote:
> I'm looking for a way to measure how long it takes to log into a SQL 2005
> server from a web server running IIS 6. Does anyone have any ideas?
> I've tried using the Profiler, but I haven't been able to think of a way to
> capture this info
> TIA
> --
> MG
AFAIK, SQL 2005 tracks only when a login is successful or unsuccessful,
not the time it takes to make the connection. You may need to use a
network sniffer to capture traffic between the servers, if that's what
you're trying to measure.
Stavros
server from a web server running IIS 6. Does anyone have any ideas?
I've tried using the Profiler, but I haven't been able to think of a way to
capture this info
TIA
--
MGMGeles wrote:
> I'm looking for a way to measure how long it takes to log into a SQL 2005
> server from a web server running IIS 6. Does anyone have any ideas?
> I've tried using the Profiler, but I haven't been able to think of a way to
> capture this info
> TIA
> --
> MG
AFAIK, SQL 2005 tracks only when a login is successful or unsuccessful,
not the time it takes to make the connection. You may need to use a
network sniffer to capture traffic between the servers, if that's what
you're trying to measure.
Stavros
Friday, March 9, 2012
Mdx Running Total
Is there any way to achieve the following:
PeriodStart TimeCharged CumulativeTotal
Oct-07 10 10
Oct-14 15 25
Oct-21 25 50
Oct-28 5 55
I am not an advanced MDX user so forgive me
if this is a dumb question.
Define CumulativeTotal as a calculated measure in the form of sum(periodstodate(...), TimeCharged). You can also use one of the shortcut variants of periodstodate such as ytd, qtd, mtd, etc.
|||From my example, this suggestion clearly does not
solve the problem. Using period to date would always
result in the same value for the cumulative column:
PeriodStart TimeCharged CumulativeTotal
Oct-07 10 55
Oct-14 15 55
Oct-21 25 55
Oct-28 5 55
Which is not what I'm looking for.
Thanks.
|||Since TimeCharged clearly changes with respect to PeriodStart, why can't you make PeriodsToDate returns different sets based on PeriodStart?|||WITH MEMBER [Measures].[CumulativeTimeCharged] AS
'SUM({NULL:[Date].[Week].CurrentMember},[Time Charged])'
SELECT NON EMPTY
{
[Measures].[CumulativeTimeCharged]
} ON COLUMNS,
NON EMPTY
{
(
[Project].[Project Description].[Project Description].ALLMEMBERS *
[Date].[Week].[Week].ALLMEMBERS
)
} ON ROWS
FROM
(
SELECT
(
NULL:STRTOMEMBER(@.ToDate, CONSTRAINED)
) ON COLUMNS
FROM
(
SELECT
(
STRTOSET(@.Project, CONSTRAINED)
) ON COLUMNS
FROM [Timesheet_Cube]
)
)
The key is the currentmember operator.
This query takes 2 parameters, the cutoff date and a dimension
(in my case a Project) for which you want your measure (in my
case TimeSheet Data) to be cumulatively shown.
This will give you an output like:
The column in "{ }" is not actually part of the output
from the query above, but is shown so you get an idea how
the Cumulative total numbers are generated.
Project Week { TimeCharged} CumulativeTotal
FooProject Oct-07 10 10
FooProject Oct-14 15 25
FooProject Oct-21 25 50
FooProject Oct-28 5 55
Cheers.
|||Thanks for the query - it's the only "Running Total"-like query I've found that worked for me.
My question is - why isn't there a simple way to create calculated members that, when spliced by Time, show the running total instead of the total for that time period? I've scoured the forums and help and books but haven't found anything that solves this yet.
Mdx Running Total
Is there any way to achieve the following:
PeriodStart TimeCharged CumulativeTotal
Oct-07 10 10
Oct-14 15 25
Oct-21 25 50
Oct-28 5 55
I am not an advanced MDX user so forgive me
if this is a dumb question.
Define CumulativeTotal as a calculated measure in the form of sum(periodstodate(...), TimeCharged). You can also use one of the shortcut variants of periodstodate such as ytd, qtd, mtd, etc.
|||From my example, this suggestion clearly does not
solve the problem. Using period to date would always
result in the same value for the cumulative column:
PeriodStart TimeCharged CumulativeTotal
Oct-07 10 55
Oct-14 15 55
Oct-21 25 55
Oct-28 5 55
Which is not what I'm looking for.
Thanks.
|||Since TimeCharged clearly changes with respect to PeriodStart, why can't you make PeriodsToDate returns different sets based on PeriodStart?|||WITH MEMBER [Measures].[CumulativeTimeCharged] AS
'SUM({NULL:[Date].[Week].CurrentMember},[Time Charged])'
SELECT NON EMPTY
{
[Measures].[CumulativeTimeCharged]
} ON COLUMNS,
NON EMPTY
{
(
[Project].[Project Description].[Project Description].ALLMEMBERS *
[Date].[Week].[Week].ALLMEMBERS
)
} ON ROWS
FROM
(
SELECT
(
NULL:STRTOMEMBER(@.ToDate, CONSTRAINED)
) ON COLUMNS
FROM
(
SELECT
(
STRTOSET(@.Project, CONSTRAINED)
) ON COLUMNS
FROM [Timesheet_Cube]
)
)
The key is the currentmember operator.
This query takes 2 parameters, the cutoff date and a dimension
(in my case a Project) for which you want your measure (in my
case TimeSheet Data) to be cumulatively shown.
This will give you an output like:
The column in "{ }" is not actually part of the output
from the query above, but is shown so you get an idea how
the Cumulative total numbers are generated.
Project Week { TimeCharged} CumulativeTotal
FooProject Oct-07 10 10
FooProject Oct-14 15 25
FooProject Oct-21 25 50
FooProject Oct-28 5 55
Cheers.
|||Thanks for the query - it's the only "Running Total"-like query I've found that worked for me.
My question is - why isn't there a simple way to create calculated members that, when spliced by Time, show the running total instead of the total for that time period? I've scoured the forums and help and books but haven't found anything that solves this yet.
Wednesday, March 7, 2012
MDX Query Tool Problem
I am trying to get to grips with cube reporting and am running the MDX Query
Tool against AdventureWorksDW on SQL 2005 sp1, Standard Edition (I am not
fluent in MDX). No matter what dimensions I drag on, the only output I can
get is a single cell / report services field.
For example, if I drag on the geography.country dimension and Sales
Order.Order Count measure, I just get a count of all orders.
--
simonI have found some sample MDX code for AdventureWorks that does work with the
Report Builder. Clearly I need to get more proficient with writing good
queries.
--
simon
"simon p" wrote:
> I am trying to get to grips with cube reporting and am running the MDX Query
> Tool against AdventureWorksDW on SQL 2005 sp1, Standard Edition (I am not
> fluent in MDX). No matter what dimensions I drag on, the only output I can
> get is a single cell / report services field.
> For example, if I drag on the geography.country dimension and Sales
> Order.Order Count measure, I just get a count of all orders.
> --
> simon
Tool against AdventureWorksDW on SQL 2005 sp1, Standard Edition (I am not
fluent in MDX). No matter what dimensions I drag on, the only output I can
get is a single cell / report services field.
For example, if I drag on the geography.country dimension and Sales
Order.Order Count measure, I just get a count of all orders.
--
simonI have found some sample MDX code for AdventureWorks that does work with the
Report Builder. Clearly I need to get more proficient with writing good
queries.
--
simon
"simon p" wrote:
> I am trying to get to grips with cube reporting and am running the MDX Query
> Tool against AdventureWorksDW on SQL 2005 sp1, Standard Edition (I am not
> fluent in MDX). No matter what dimensions I drag on, the only output I can
> get is a single cell / report services field.
> For example, if I drag on the geography.country dimension and Sales
> Order.Order Count measure, I just get a count of all orders.
> --
> simon
MDX Query Governor ?
Hi,
There are 2 problematic cases that would require some kind of MDX Query Governor or time out:
- User leave an endless query running forever
- User cancel an endless query on the client and it does not get cancelled on the server
I looked into the server properties and saw a couple items like Admin Time out, External Connection Time out etc.
Which one of these properties should I use to get the SSAS server kill run-away queries that where either let go or canceled on the client side but still run and eat the CPU on the server? My take is that any Client issued MDX lasting over 30 minutes should be automatically cancelled on the server.
Right now I restart the server when it get too bad but it is not a solution, one cannot work with a server that need restart every single day.
It is also unpracticable to have anyone spending valuable time chasing down useless connections/queries and cancelling them manually.
Thanks,
Philippe
For one I suggest you take a look at the ActivityViewer sample application to see long running queries. You can also use it to cancel user sessions and connections to free up server resources.
Governing resources is not a simple problem to solve. We are working on it and hope that in the next version your will see greater responcivencess of Cancel and you will see better ways to detect and cancel runaway queries.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Saturday, February 25, 2012
MDX Query "paging file too small" problem
...but the SSAS 2005 server is running on a machine with a 12 Gig page file.
Although it is a big select statement (with "intersect()"s and "hierarchize()"s and multiple cross-joins), I would expect the query to complete successfully. If there is a shortage of memory, I would think the server would simply make use of the paging file (all 12 Gig).
The machine is an HP dual-IA64 server with 4 Gig of RAM running W2K3 SP1. The SSAS 2005 instance has been upgraded to SP1 recently.
Any ideas?
Thanks!
Which flavour of Analysis Services 2005 are you using?
We've also had problems with memory usage - typically, though, when processing the dimensions or measure groups. Is the machine dedicated to SSAS, or does it have e.g. SQL Server installed as well?
Regards,
Will.
|||Hi, Hippunky! Thanks for replying.
The machine has both IIS and the full "suite" of SQL Server services installed (Sql Server, SSAS, SSIS, RS, SQL Agent, etc.) The SSAS installation is the full (mostly default) install.
Bob
|||I've bumped the paging file to 30 Gig (24 Gig wasn't enough), and the MDX query starts successfully (before it was failing almost immediately with the "paging file too small" message). I'll post if it completes successfully.
"Just talking to myself...",
Bob Hodgman
|||
Hmmm... I didn't mark this as the answer although it certainly took care of the initial failure. So I guess it is the "answer" to that issue.
However, the query has been running for 22 hours and 53 minutes, so far.
TGIF.
Bob
|||We've been having the same problem when we process our dimensions and cube. How did you change the size of the paging file?
TIA
MDX Query "paging file too small" problem
...but the SSAS 2005 server is running on a machine with a 12 Gig page file.
Although it is a big select statement (with "intersect()"s and "hierarchize()"s and multiple cross-joins), I would expect the query to complete successfully. If there is a shortage of memory, I would think the server would simply make use of the paging file (all 12 Gig).
The machine is an HP dual-IA64 server with 4 Gig of RAM running W2K3 SP1. The SSAS 2005 instance has been upgraded to SP1 recently.
Any ideas?
Thanks!
Which flavour of Analysis Services 2005 are you using?
We've also had problems with memory usage - typically, though, when processing the dimensions or measure groups. Is the machine dedicated to SSAS, or does it have e.g. SQL Server installed as well?
Regards,
Will.
|||Hi, Hippunky! Thanks for replying.
The machine has both IIS and the full "suite" of SQL Server services installed (Sql Server, SSAS, SSIS, RS, SQL Agent, etc.) The SSAS installation is the full (mostly default) install.
Bob
|||I've bumped the paging file to 30 Gig (24 Gig wasn't enough), and the MDX query starts successfully (before it was failing almost immediately with the "paging file too small" message). I'll post if it completes successfully.
"Just talking to myself...",
Bob Hodgman
|||
Hmmm... I didn't mark this as the answer although it certainly took care of the initial failure. So I guess it is the "answer" to that issue.
However, the query has been running for 22 hours and 53 minutes, so far.
TGIF.
Bob
|||We've been having the same problem when we process our dimensions and cube. How did you change the size of the paging file?
TIA
MDX Parser
Hello,
Im currently running SEPT CTP, and im trying to create a report based on a
dataset filled by a MDX query. That query must have parameters.
I have defined the query in query designer, but after that I must edit the
query because some of my parameters include spaces ' ' and I must edit
StrToSet to surround my @.var with '[ ]' .
The problem is that i cant make any change to the query, it always gives the
error "An MDX expression was expected. An empty expression was found.". Even
when I switch from designer to hand-edit it gives the same error if I process
it.
I think it has somethin to do with the parser, because the StrTo... family
of functions expect a valid MDX expression as a parameter but all I have is
@.varname.
Help anyone? Thanks.hello Fernando,
How exactly are you changing your mdx query to accept the parameter values?
If you show me i may be able to help.
I've done it by running the query without any parameters to get the fields,
then editing the query to accept the parameters. This is done by placing an
'=' sign at the beginning of the query then at the end of each string of text
insert " & "
i.e
="with" & "member" &
and
"select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
etc etc
dont know if taht helps but i tried.
bobfoc
"Fernando Marçal" wrote:
> Hello,
> Im currently running SEPT CTP, and im trying to create a report based on a
> dataset filled by a MDX query. That query must have parameters.
> I have defined the query in query designer, but after that I must edit the
> query because some of my parameters include spaces ' ' and I must edit
> StrToSet to surround my @.var with '[ ]' .
> The problem is that i cant make any change to the query, it always gives the
> error "An MDX expression was expected. An empty expression was found.". Even
> when I switch from designer to hand-edit it gives the same error if I process
> it.
> I think it has somethin to do with the parser, because the StrTo... family
> of functions expect a valid MDX expression as a parameter but all I have is
> @.varname.
> Help anyone? Thanks.|||sorry made an error the parameter string should say [" &
Parameter!aParameter.Value &"] not [" & Parameter!Value.aParameter &"] got it
the wrong way round!
"bobfoc" wrote:
> hello Fernando,
> How exactly are you changing your mdx query to accept the parameter values?
> If you show me i may be able to help.
> I've done it by running the query without any parameters to get the fields,
> then editing the query to accept the parameters. This is done by placing an
> '=' sign at the beginning of the query then at the end of each string of text
> insert " & "
> i.e
> ="with" & "member" &
> and
> "select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
> etc etc
> dont know if taht helps but i tried.
> bobfoc
> "Fernando Marçal" wrote:
> > Hello,
> >
> > Im currently running SEPT CTP, and im trying to create a report based on a
> > dataset filled by a MDX query. That query must have parameters.
> > I have defined the query in query designer, but after that I must edit the
> > query because some of my parameters include spaces ' ' and I must edit
> > StrToSet to surround my @.var with '[ ]' .
> > The problem is that i cant make any change to the query, it always gives the
> > error "An MDX expression was expected. An empty expression was found.". Even
> > when I switch from designer to hand-edit it gives the same error if I process
> > it.
> >
> > I think it has somethin to do with the parser, because the StrTo... family
> > of functions expect a valid MDX expression as a parameter but all I have is
> > @.varname.
> >
> > Help anyone? Thanks.|||Hi bobfoc,
I've manage to use the parameters by starting the query with query designer.
What is absurd is this: I start the query with query designer, and when I
switch to query mode the query is auto-generated and the error is still
produced.
"bobfoc" wrote:
> sorry made an error the parameter string should say [" &
> Parameter!aParameter.Value &"] not [" & Parameter!Value.aParameter &"] got it
> the wrong way round!
> "bobfoc" wrote:
> > hello Fernando,
> >
> > How exactly are you changing your mdx query to accept the parameter values?
> > If you show me i may be able to help.
> >
> > I've done it by running the query without any parameters to get the fields,
> > then editing the query to accept the parameters. This is done by placing an
> > '=' sign at the beginning of the query then at the end of each string of text
> > insert " & "
> > i.e
> > ="with" & "member" &
> > and
> > "select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
> >
> > etc etc
> >
> > dont know if taht helps but i tried.
> >
> > bobfoc
> >
> > "Fernando Marçal" wrote:
> >
> > > Hello,
> > >
> > > Im currently running SEPT CTP, and im trying to create a report based on a
> > > dataset filled by a MDX query. That query must have parameters.
> > > I have defined the query in query designer, but after that I must edit the
> > > query because some of my parameters include spaces ' ' and I must edit
> > > StrToSet to surround my @.var with '[ ]' .
> > > The problem is that i cant make any change to the query, it always gives the
> > > error "An MDX expression was expected. An empty expression was found.". Even
> > > when I switch from designer to hand-edit it gives the same error if I process
> > > it.
> > >
> > > I think it has somethin to do with the parser, because the StrTo... family
> > > of functions expect a valid MDX expression as a parameter but all I have is
> > > @.varname.
> > >
> > > Help anyone? Thanks.|||that's just wierd, I've had some strange problems with reporting services
before but nothing that a reboot hasn't fixed.
I've never used query designer, i prefer writing from scratch then you
understand it better, but then i also work on the philosophy of if it works,
leave it unless you have to change it.
Glad you kid of sorted it,
bob
"Fernando Marçal" wrote:
> Hi bobfoc,
> I've manage to use the parameters by starting the query with query designer.
> What is absurd is this: I start the query with query designer, and when I
> switch to query mode the query is auto-generated and the error is still
> produced.
> "bobfoc" wrote:
> > sorry made an error the parameter string should say [" &
> > Parameter!aParameter.Value &"] not [" & Parameter!Value.aParameter &"] got it
> > the wrong way round!
> >
> > "bobfoc" wrote:
> >
> > > hello Fernando,
> > >
> > > How exactly are you changing your mdx query to accept the parameter values?
> > > If you show me i may be able to help.
> > >
> > > I've done it by running the query without any parameters to get the fields,
> > > then editing the query to accept the parameters. This is done by placing an
> > > '=' sign at the beginning of the query then at the end of each string of text
> > > insert " & "
> > > i.e
> > > ="with" & "member" &
> > > and
> > > "select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
> > >
> > > etc etc
> > >
> > > dont know if taht helps but i tried.
> > >
> > > bobfoc
> > >
> > > "Fernando Marçal" wrote:
> > >
> > > > Hello,
> > > >
> > > > Im currently running SEPT CTP, and im trying to create a report based on a
> > > > dataset filled by a MDX query. That query must have parameters.
> > > > I have defined the query in query designer, but after that I must edit the
> > > > query because some of my parameters include spaces ' ' and I must edit
> > > > StrToSet to surround my @.var with '[ ]' .
> > > > The problem is that i cant make any change to the query, it always gives the
> > > > error "An MDX expression was expected. An empty expression was found.". Even
> > > > when I switch from designer to hand-edit it gives the same error if I process
> > > > it.
> > > >
> > > > I think it has somethin to do with the parser, because the StrTo... family
> > > > of functions expect a valid MDX expression as a parameter but all I have is
> > > > @.varname.
> > > >
> > > > Help anyone? Thanks.
Im currently running SEPT CTP, and im trying to create a report based on a
dataset filled by a MDX query. That query must have parameters.
I have defined the query in query designer, but after that I must edit the
query because some of my parameters include spaces ' ' and I must edit
StrToSet to surround my @.var with '[ ]' .
The problem is that i cant make any change to the query, it always gives the
error "An MDX expression was expected. An empty expression was found.". Even
when I switch from designer to hand-edit it gives the same error if I process
it.
I think it has somethin to do with the parser, because the StrTo... family
of functions expect a valid MDX expression as a parameter but all I have is
@.varname.
Help anyone? Thanks.hello Fernando,
How exactly are you changing your mdx query to accept the parameter values?
If you show me i may be able to help.
I've done it by running the query without any parameters to get the fields,
then editing the query to accept the parameters. This is done by placing an
'=' sign at the beginning of the query then at the end of each string of text
insert " & "
i.e
="with" & "member" &
and
"select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
etc etc
dont know if taht helps but i tried.
bobfoc
"Fernando Marçal" wrote:
> Hello,
> Im currently running SEPT CTP, and im trying to create a report based on a
> dataset filled by a MDX query. That query must have parameters.
> I have defined the query in query designer, but after that I must edit the
> query because some of my parameters include spaces ' ' and I must edit
> StrToSet to surround my @.var with '[ ]' .
> The problem is that i cant make any change to the query, it always gives the
> error "An MDX expression was expected. An empty expression was found.". Even
> when I switch from designer to hand-edit it gives the same error if I process
> it.
> I think it has somethin to do with the parser, because the StrTo... family
> of functions expect a valid MDX expression as a parameter but all I have is
> @.varname.
> Help anyone? Thanks.|||sorry made an error the parameter string should say [" &
Parameter!aParameter.Value &"] not [" & Parameter!Value.aParameter &"] got it
the wrong way round!
"bobfoc" wrote:
> hello Fernando,
> How exactly are you changing your mdx query to accept the parameter values?
> If you show me i may be able to help.
> I've done it by running the query without any parameters to get the fields,
> then editing the query to accept the parameters. This is done by placing an
> '=' sign at the beginning of the query then at the end of each string of text
> insert " & "
> i.e
> ="with" & "member" &
> and
> "select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
> etc etc
> dont know if taht helps but i tried.
> bobfoc
> "Fernando Marçal" wrote:
> > Hello,
> >
> > Im currently running SEPT CTP, and im trying to create a report based on a
> > dataset filled by a MDX query. That query must have parameters.
> > I have defined the query in query designer, but after that I must edit the
> > query because some of my parameters include spaces ' ' and I must edit
> > StrToSet to surround my @.var with '[ ]' .
> > The problem is that i cant make any change to the query, it always gives the
> > error "An MDX expression was expected. An empty expression was found.". Even
> > when I switch from designer to hand-edit it gives the same error if I process
> > it.
> >
> > I think it has somethin to do with the parser, because the StrTo... family
> > of functions expect a valid MDX expression as a parameter but all I have is
> > @.varname.
> >
> > Help anyone? Thanks.|||Hi bobfoc,
I've manage to use the parameters by starting the query with query designer.
What is absurd is this: I start the query with query designer, and when I
switch to query mode the query is auto-generated and the error is still
produced.
"bobfoc" wrote:
> sorry made an error the parameter string should say [" &
> Parameter!aParameter.Value &"] not [" & Parameter!Value.aParameter &"] got it
> the wrong way round!
> "bobfoc" wrote:
> > hello Fernando,
> >
> > How exactly are you changing your mdx query to accept the parameter values?
> > If you show me i may be able to help.
> >
> > I've done it by running the query without any parameters to get the fields,
> > then editing the query to accept the parameters. This is done by placing an
> > '=' sign at the beginning of the query then at the end of each string of text
> > insert " & "
> > i.e
> > ="with" & "member" &
> > and
> > "select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
> >
> > etc etc
> >
> > dont know if taht helps but i tried.
> >
> > bobfoc
> >
> > "Fernando Marçal" wrote:
> >
> > > Hello,
> > >
> > > Im currently running SEPT CTP, and im trying to create a report based on a
> > > dataset filled by a MDX query. That query must have parameters.
> > > I have defined the query in query designer, but after that I must edit the
> > > query because some of my parameters include spaces ' ' and I must edit
> > > StrToSet to surround my @.var with '[ ]' .
> > > The problem is that i cant make any change to the query, it always gives the
> > > error "An MDX expression was expected. An empty expression was found.". Even
> > > when I switch from designer to hand-edit it gives the same error if I process
> > > it.
> > >
> > > I think it has somethin to do with the parser, because the StrTo... family
> > > of functions expect a valid MDX expression as a parameter but all I have is
> > > @.varname.
> > >
> > > Help anyone? Thanks.|||that's just wierd, I've had some strange problems with reporting services
before but nothing that a reboot hasn't fixed.
I've never used query designer, i prefer writing from scratch then you
understand it better, but then i also work on the philosophy of if it works,
leave it unless you have to change it.
Glad you kid of sorted it,
bob
"Fernando Marçal" wrote:
> Hi bobfoc,
> I've manage to use the parameters by starting the query with query designer.
> What is absurd is this: I start the query with query designer, and when I
> switch to query mode the query is auto-generated and the error is still
> produced.
> "bobfoc" wrote:
> > sorry made an error the parameter string should say [" &
> > Parameter!aParameter.Value &"] not [" & Parameter!Value.aParameter &"] got it
> > the wrong way round!
> >
> > "bobfoc" wrote:
> >
> > > hello Fernando,
> > >
> > > How exactly are you changing your mdx query to accept the parameter values?
> > > If you show me i may be able to help.
> > >
> > > I've done it by running the query without any parameters to get the fields,
> > > then editing the query to accept the parameters. This is done by placing an
> > > '=' sign at the beginning of the query then at the end of each string of text
> > > insert " & "
> > > i.e
> > > ="with" & "member" &
> > > and
> > > "select" & "[sdjhfskljf].[" & Parameter!Value.aParameter &"] from columns, " &
> > >
> > > etc etc
> > >
> > > dont know if taht helps but i tried.
> > >
> > > bobfoc
> > >
> > > "Fernando Marçal" wrote:
> > >
> > > > Hello,
> > > >
> > > > Im currently running SEPT CTP, and im trying to create a report based on a
> > > > dataset filled by a MDX query. That query must have parameters.
> > > > I have defined the query in query designer, but after that I must edit the
> > > > query because some of my parameters include spaces ' ' and I must edit
> > > > StrToSet to surround my @.var with '[ ]' .
> > > > The problem is that i cant make any change to the query, it always gives the
> > > > error "An MDX expression was expected. An empty expression was found.". Even
> > > > when I switch from designer to hand-edit it gives the same error if I process
> > > > it.
> > > >
> > > > I think it has somethin to do with the parser, because the StrTo... family
> > > > of functions expect a valid MDX expression as a parameter but all I have is
> > > > @.varname.
> > > >
> > > > Help anyone? Thanks.
Subscribe to:
Posts (Atom)