Nonpaged pool ? What is it ?

The server was unable to allocate from the system nonpaged pool because the
pool was empty .
We keep seeing the above errors and our server crashes. I am trying to
understand the basics here as to what is the nonpaged pool ?
And how does the pool become empty and what does it want to allocate ?
Whats a paged pool if theres a nonpaged pool ?
Thanks
Hello,
In article <#ThJVX#VFHA.3076@.TK2MSFTNGP12.phx.gbl>,
fatima_ja@.hotmail.com says...
> The server was unable to allocate from the system nonpaged pool because the
> pool was empty .
Yeah...
What you are running? Win2K3 on a 8 MB machine?! ;)

> We keep seeing the above errors and our server crashes. I am trying to
> understand the basics here as to what is the nonpaged pool ?
Kernel mode components can choose between paged- and nonpaged pool
memory pool, depends on there needs. User mode applications use always
the paged pool, except they lock memory to keep them resident. Paged
memory can be paged out, this means that your hard drive will be used as
memory. Nonpaged pool memory can't be paged out.

> And how does the pool become empty and what does it want to allocate ?
AFAIK if more nonpaged memory is required than available.
h.wulff
[dont send me an email]
|||Hi
Nonpaged pool. The portion of shared system memory that cannot be paged to
the disk paging file. Nonpaged pool memory can be accessed at any time
without causing a page fault.
Page fault. A page fault occurs when a thread accesses a page that is not
in memory. For more information about paging and other memory management
topics, see "Evaluating Memory and Cache Usage" in the Server Operations
Guide of the Microsoft? Windows 2000 Server Resource
Try this link for Windows 2000 Non-Paged Pool Is Exhausted ?
http://support.microsoft.com/default.aspx?kbid=296265
Regards
Ramki

Nonpaged pool ? What is it ?

The server was unable to allocate from the system nonpaged pool because the
pool was empty .
We keep seeing the above errors and our server crashes. I am trying to
understand the basics here as to what is the nonpaged pool ?
And how does the pool become empty and what does it want to allocate ?
Whats a paged pool if theres a nonpaged pool ?
ThanksHello,
In article <#ThJVX#VFHA.3076@.TK2MSFTNGP12.phx.gbl>,
fatima_ja@.hotmail.com says...
> The server was unable to allocate from the system nonpaged pool because th
e
> pool was empty .
Yeah...
What you are running? Win2K3 on a 8 MB machine?! ;)

> We keep seeing the above errors and our server crashes. I am trying to
> understand the basics here as to what is the nonpaged pool ?
Kernel mode components can choose between paged- and nonpaged pool
memory pool, depends on there needs. User mode applications use always
the paged pool, except they lock memory to keep them resident. Paged
memory can be paged out, this means that your hard drive will be used as
memory. Nonpaged pool memory can't be paged out.

> And how does the pool become empty and what does it want to allocate ?
AFAIK if more nonpaged memory is required than available.
h.wulff
[dont send me an email]|||Hi
Nonpaged pool. The portion of shared system memory that cannot be paged to
the disk paging file. Nonpaged pool memory can be accessed at any time
without causing a page fault.
Page fault. A page fault occurs when a thread accesses a page that is not
in memory. For more information about paging and other memory management
topics, see "Evaluating Memory and Cache Usage" in the Server Operations
Guide of the Microsoft? Windows 2000 Server Resource
Try this link for Windows 2000 Non-Paged Pool Is Exhausted ?
http://support.microsoft.com/default.aspx?kbid=296265
Regards
Ramki

Nonpaged pool ? What is it ?

The server was unable to allocate from the system nonpaged pool because the
pool was empty .
We keep seeing the above errors and our server crashes. I am trying to
understand the basics here as to what is the nonpaged pool ?
And how does the pool become empty and what does it want to allocate ?
Whats a paged pool if theres a nonpaged pool ?
ThanksHello,
In article <#ThJVX#VFHA.3076@.TK2MSFTNGP12.phx.gbl>,
fatima_ja@.hotmail.com says...
> The server was unable to allocate from the system nonpaged pool because the
> pool was empty .
Yeah...
What you are running? Win2K3 on a 8 MB machine?! ;)
> We keep seeing the above errors and our server crashes. I am trying to
> understand the basics here as to what is the nonpaged pool ?
Kernel mode components can choose between paged- and nonpaged pool
memory pool, depends on there needs. User mode applications use always
the paged pool, except they lock memory to keep them resident. Paged
memory can be paged out, this means that your hard drive will be used as
memory. Nonpaged pool memory can't be paged out.
> And how does the pool become empty and what does it want to allocate ?
AFAIK if more nonpaged memory is required than available.
--
h.wulff
[dont send me an email]|||Hi
Nonpaged pool. The portion of shared system memory that cannot be paged to
the disk paging file. Nonpaged pool memory can be accessed at any time
without causing a page fault.
Page fault. A page fault occurs when a thread accesses a page that is not
in memory. For more information about paging and other memory management
topics, see "Evaluating Memory and Cache Usage" in the Server Operations
Guide of the Microsoft® Windows 2000 Server Resource
Try this link for Windows 2000 Non-Paged Pool Is Exhausted ?
http://support.microsoft.com/default.aspx?kbid=296265
Regards
Ramki|||Hello
We have this same problem. Our sql reports in about once in two month
this in to system log
The server was unable to allocate from the system nonpaged poo
because the pool was empty.
After that SQL server stopts responding as well as other networ
related services. We are not using anything else on that server, n
IIS, no nothing just SQLserver 2000 SP3a, win2000 server sp4. Onl
thing we can do is to restart server.
What should I do to stopt this happening every two months...
Could this be caused becouse our server has 3gb of memory an
win2000server standard can't handel over 2 gb|||Hi Timolous,
This does not seems to be caused by the 3gb of RAM as Windows 2000 Server
supports 4 GB of RAM. May be the following link is helpful to you.
Regards,
Syed Zulfiqar
"timolous" <timo@.suorakanava-dot-fi.no-spam.invalid> wrote in message
news:Z96dneK4_ayZJS7fRVn_vg@.giganews.com...
> Hello!
> We have this same problem. Our sql reports in about once in two months
> this in to system log:
> The server was unable to allocate from the system nonpaged pool
> because the pool was empty.
> After that SQL server stopts responding as well as other network
> related services. We are not using anything else on that server, no
> IIS, no nothing just SQLserver 2000 SP3a, win2000 server sp4. Only
> thing we can do is to restart server.
> What should I do to stopt this happening every two months...?
> Could this be caused becouse our server has 3gb of memory and
> win2000server standard can't handel over 2 gb?
>|||Hi,
Here is the link
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q272568
Regards,
Syed Zulfiqar

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
DonUse select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:

> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don

>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
message[vbcol=seagreen]
90[vbcol=seagreen]
structure[vbcol=seagreen]
>
>.
>|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don

>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
>
90[vbcol=seagreen]
>.
>|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...[vbcol=seagreen]
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
>
> operation in SQL Server.
> by doing a Select
> set. This still
> compared to the fully
> need to be in the
> message
> 90
> structure|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:

> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
>
> N'
> N'
> maybe 10 million
> queryout on an indexed
> 90
>

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
DonUse select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with 90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file structure
>> address, char,30
>> thanks,
>> Don
>|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don
>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
>> Use select into statement
>> That is not logged.
>> You can select * or select fieldlist.
>>
>> "Don" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file
structure
>> address, char,30
>> thanks,
>> Don
>>
>
>.
>|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don
>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file structure
>> address, char,30
>> thanks,
>> Don
>>
>.
>|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
>>--Original Message--
>>Just to be clear you can never have a non-logged
> operation in SQL Server.
>>You can get what is called a Minimally Logged operation
> by doing a Select
>>Into, BCP or Bulk Insert when the proper conditions are
> set. This still
>>logs entries to the transaction log but are minimal
> compared to the fully
>>logged operations. In the case of the Select Into you
> need to be in the
>>Bulk Logged or Simple recovery mode for this to occur.
>>
>>--
>>Andrew J. Kelly SQL MVP
>>
>><news.optonline.net> wrote in message
>>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
>> Use select into statement
>> That is not logged.
>> You can select * or select fieldlist.
>>
>> "Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
> 90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file
> structure
>> address, char,30
>> thanks,
>> Don
>>
>>
>>.|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:
> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
> >--Original Message--
> >To do this I would create a view on the new table
> >create view vw_newtable
> >as
> >select * from newtable
> >
> >than bcp the data out
> >
> >master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
> N'
> >then bcp in
> >master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
> N'
> >
> >Note there must be no indexes on the dest table.
> >For 90 million recs I would consider doing the above but
> maybe 10 million
> >recs at a time. Use a query to extract the data with
> queryout on an indexed
> >column (or you can use views in the same way)
> >
> >
> >
> >
> >
> >
> >"Don" wrote:
> >
> >> SQL 7.0
> >>
> >> I need to use nonlogged bcp to copy from a table with
> 90
> >> millin records to an empty table structure that's
> >> different than the source structure.
> >> Does someone know the syntax?
> >>
> >> old table example:
> >> name, char, 30
> >> address,char,30
> >>
> >> new table example:
> >> name,char,30
> >> id,decimal,10 this is NULL on the source file structure
> >> address, char,30
> >>
> >> thanks,
> >> Don
> >>
> >>
> >.
> >
>sql

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
Don
Use select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>
|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>
|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:

> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>
|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don

>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the[vbcol=seagreen]
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
message[vbcol=seagreen]
90[vbcol=seagreen]
structure
>
>.
>
|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don

>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed[vbcol=seagreen]
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
90
>.
>
|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...[vbcol=seagreen]
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
> operation in SQL Server.
> by doing a Select
> set. This still
> compared to the fully
> need to be in the
> message
> 90
> structure
|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:

> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
> N'
> N'
> maybe 10 million
> queryout on an indexed
> 90
>

Nonempty Problem

following query is nt working properly. need a solution (need to filter the non empty rows)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
non empty CROSSJOIN ([CustomTimeSet],[GeneralLedgerSet]) on rows

FROM Profitability
WHERE [Account—ETBillingCode].[MDA]NonEmpty({filter(CROSSJOIN([CustomTimeSet],[GeneralLedgerSet]),[Measures].[MdaCodeTotal] <> 0 )})on rows

I am a bit suspicious about the [Measures].[Description] measure. Is this a calculated measure? It could be what is causing the non empty clause not to work. If you are using SSAS 2005, something like the following might work:

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
nonempty( CROSSJOIN ([CustomTimeSet],[GeneralLedgerSet]) , {Measures.MdaCodeTotal})on rows

FROM Profitability
WHERE [Account—ETBillingCode].[MDA]

Note I am using the second parameter in the NonEmpty() function to set the measure context for the non empty evaluation.

NON_EMPTY_BEHAVIOR if NON EMPTY isn't used

A cube I'm working on has lots of calculations. But none of the MDX queries in the reports use the NON EMPTY clause or the NonEmpty function. What I'm wondering is whether the optimizer or any other part of the Formula Engine uses NON_EMPTY_BEHAVIOR for anything else besides removing empty cells from the resultset.

If the Sum function is summing two calculated measures, will a NEB on those two calculated measures help the Sum function, for example? That's the kind of thing I'm looking for... places where NEB is used other than removing entire cells from the resultset.

Yes, NON_EMPTY_BEHAVIOR is used even if there are no NON EMPTY queries. Some more information about it here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx|||

Mosha, thanks for the reply.

The takeaway from your blog regarding NEBs is that the multiplication operator (as in the * operator used for math, not for crossjoin) uses the NEB. Are there any others?

What I was hoping for was sort of a list of places where NEB's are used unless that's an enormous list.

|||

Multiplication was one of the examples. Many other (but not all) MDX functions also take it into account. The exact list is not easy to build, because there are some other additional factors affecting whether or not NEB will be used.

The most important thing though, is to ensure that NEB is used correctly, before thinking about perf. Even in this forum, we routinely see people using NEB incorrectly, which can and does cause wrong results.

non paged pool is empty error

I have received the following error
The server was unable to allocate from the system nonpaged pool because the
pool was empty.
Any idea on how to trap which process is causing the memory to leak or be
used up. This has caused SQL Server to be unavailable. I had to reboot as a
fix
I would like to trap it before it happens again. Any way to do so ?
Using Win2K3 and SQL 2KHi Hassan
I would expect this to be a gradual therefore monitoring the memory usage
over time would possibly indicate which process is not releasing memory.
Check out
http://www.microsoft.com/technet/prodtechnol/exchange/Guides/TrblshtE2k3Perf/7a44b064-8872-4edf-aac7-36b2a17f662a.mspx?mfr=true
and the perfmon counters in http://ask.support.microsoft.com/kb/133384
Also see if this applies:
http://support.microsoft.com/default.aspx?scid=kb;en-us;272568&sd=ee
John
"Hassan" wrote:
> I have received the following error
> The server was unable to allocate from the system nonpaged pool because the
> pool was empty.
> Any idea on how to trap which process is causing the memory to leak or be
> used up. This has caused SQL Server to be unavailable. I had to reboot as a
> fix
> I would like to trap it before it happens again. Any way to do so ?
>
> Using Win2K3 and SQL 2K
>
>
>

non paged pool is empty error

I have received the following error
The server was unable to allocate from the system nonpaged pool because the
pool was empty.
Any idea on how to trap which process is causing the memory to leak or be
used up. This has caused SQL Server to be unavailable. I had to reboot as a
fix
I would like to trap it before it happens again. Any way to do so ?
Using Win2K3 and SQL 2KHi Hassan
I would expect this to be a gradual therefore monitoring the memory usage
over time would possibly indicate which process is not releasing memory.
Check out
http://www.microsoft.com/technet/pr...a.mspx?mfr=true
and the perfmon counters in http://ask.support.microsoft.com/kb/133384
Also see if this applies:
http://support.microsoft.com/defaul...us;272568&sd=ee
John
"Hassan" wrote:

> I have received the following error
> The server was unable to allocate from the system nonpaged pool because th
e
> pool was empty.
> Any idea on how to trap which process is causing the memory to leak or be
> used up. This has caused SQL Server to be unavailable. I had to reboot as
a
> fix
> I would like to trap it before it happens again. Any way to do so ?
>
> Using Win2K3 and SQL 2K
>
>
>

non paged pool is empty error

I have received the following error
The server was unable to allocate from the system nonpaged pool because the
pool was empty.
Any idea on how to trap which process is causing the memory to leak or be
used up. This has caused SQL Server to be unavailable. I had to reboot as a
fix
I would like to trap it before it happens again. Any way to do so ?
Using Win2K3 and SQL 2K
Hi Hassan
I would expect this to be a gradual therefore monitoring the memory usage
over time would possibly indicate which process is not releasing memory.
Check out
http://www.microsoft.com/technet/prodtechnol/exchange/Guides/TrblshtE2k3Perf/7a44b064-8872-4edf-aac7-36b2a17f662a.mspx?mfr=true
and the perfmon counters in http://ask.support.microsoft.com/kb/133384
Also see if this applies:
http://support.microsoft.com/default.aspx?scid=kb;en-us;272568&sd=ee
John
"Hassan" wrote:

> I have received the following error
> The server was unable to allocate from the system nonpaged pool because the
> pool was empty.
> Any idea on how to trap which process is causing the memory to leak or be
> used up. This has caused SQL Server to be unavailable. I had to reboot as a
> fix
> I would like to trap it before it happens again. Any way to do so ?
>
> Using Win2K3 and SQL 2K
>
>
>

NON EMPTY issue

Apologies if this is a bit simple but it's doing my head in a bit

Why would the following MDX return 4 correct records

Code Snippet

SELECT { [Measures].[Signed Surface Area], [Measures].[Amount Signed] } ON COLUMNS ,

non empty{ DESCENDANTS( [Property].[Customer].[All Property], [Property].[Customer].[Customer] ) } ON ROWS

FROM [Lease]

WHERE ( [Time Calcs].[YTD],[Time].[Year].&[2007].&[2].&[5], [Currency].&[26], [Unit of Measure].&[2] )

however adding another measure returns 50 additional spurious measures, all with blank rows

Code Snippet

SELECT NON EMPTY { [Measures].[Signed Leases], [Measures].[Signed Surface Area], [Measures].[Amount Signed] } ON COLUMNS ,

NON EMPTY { DESCENDANTS( [Property].[Customer].[All Property], [Property].[Customer].[Customer] ) } ON ROWS

FROM [Lease]

WHERE ( [Time].[Year].&[2007].&[2].&[5], [Currency].&[26], [Time Calcs].[YTD], [Unit of Measure].&[2] )

The "signed leases" measure was the field added

Presumably you mean that adding the Signed Leases measure returns another 50 spurious rows, not measures? Is Signed Leases a calculated measure, and if so what is its definition? I'm guessing [Time Calcs].[YTD] is another calculated measure (and is probably the culprit) - what is its definition? Have you been using the Non_Empty_Behavior property anywhere?

Chris

|||

Chris,

Yes, you were correct in your assumption. TimeCalcs was OK, it was actually the fact that signed leases was calculated. Adding an iif statement to create a null sorted it

Non empty for calculated members

I have a cube with a calculated member The calculation for the calculated member "MyMembers" is a simple addition:
[Measures].[NoOfOwners] + [Measures].[NoOfUsers]
In the MDX select statment I use the "non empty" expression on both columns and rows. My MDX query goes like this:
SELECT
non empty {[TMD_Products].[All TMD_Products].[Datatjenester].[Bedriftsnett]} on columns ,
non empty {[TMD_KIDs].[Kid].members} on rows
from TMC_Products where ([Measures].[MyMembers])
The problem is that when I substitute "MyMembers" in the from statement with "NoOfOwners" or "NoOfUsers" I always get the same number of rows as with "MyMembers". The query seems to return correct values for the member but does not suppress non empty valu
es. In stead the query returns the value 0.
How can I set up my query to supress the the lines with value 0?
I run SQL Server 2000 Enterprise edition with Analysis Server on windows 2000 server, Service Pack 3a is installed for sql server and analysis server.
Regards
Tore G.
Hello Tore,
I would appreciate your patience while I am looking into this issue. I will
post my response at soon as I have update for you. In the mean time, if you
have any other useful information, please feel free to let me know.
Thank you,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||Hi Tore,
I learned you want to set up the query to suppress the lines with value 0.
When we use NON EMPTY keyword, it is important to note that this function
screens out empty tuples, not individual empty cells. Because of this,
empty cells can appear in a result dataset even when the NON EMPTY keyword
is used For example:
With member [Measures].[MYTESTCAL] as '[Measures].[Units
Shipped]+[Measures].[Units Ordered]'
Select
NON EMPTY{[Time].[1997],[Time].[1998] } on columns,
NON EMPTY [Store].[Store Name].members on rows
From Warehouse where ([Measures].[MYTESTCAL])
I tried the above MDX statements on FoodMart 2000 sample database using MDX
Sample application and use [Measures].[Units Shipped] or [Measures].[Units
Ordered] instead of [Measures].[MYTESTCAL]. It seems the query did not
return 0 instead of NuLL. I am afraid I cannot reproduce the problem you
described. If it is possible, please create a example on the FoodMart 2000
sample database so that I can reproduce it in house and perform further
research.
I also found the following articles for your reference.
244650 INF: Working with NULL Values in OLAP Services
http://support.microsoft.com/?id=244650
Working with Empty Cells
http://msdn.microsoft.com/library/de...us/olapdmad/ag
mdxadvanced_8jcj.asp
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||Have you tried using the 'Filter' function?
"T. Gylver" <nospam@.tgylnospam.no> wrote in message
news:9BD94A01-B03F-4B75-9224-CF3C9CF471E6@.microsoft.com...
> I have a cube with a calculated member The calculation for the calculated
member "MyMembers" is a simple addition:
> [Measures].[NoOfOwners] + [Measures].[NoOfUsers]
> In the MDX select statment I use the "non empty" expression on both
columns and rows. My MDX query goes like this:
> SELECT
> non empty {[TMD_Products].[All
TMD_Products].[Datatjenester].[Bedriftsnett]} on columns ,
> non empty {[TMD_KIDs].[Kid].members} on rows
> from TMC_Products where ([Measures].[MyMembers])
>
> The problem is that when I substitute "MyMembers" in the from statement
with "NoOfOwners" or "NoOfUsers" I always get the same number of rows as
with "MyMembers". The query seems to return correct values for the member
but does not suppress non empty values. In stead the query returns the value
0.
> How can I set up my query to supress the the lines with value 0?
> I run SQL Server 2000 Enterprise edition with Analysis Server on windows
2000 server, Service Pack 3a is installed for sql server and analysis
server.
> Regards
> Tore G.
|||WITH
MEMBER [Measures].[M1] AS '10'
MEMBER [Measures].[M2] AS '0'
MEMBER [Measures].[M] AS '[Measures].[M1] + [Measures].[M2]'
--WAY 1
MEMBER [Measures].[Don't Show 0] AS 'IIF([Measures].[M2] = 0,NULL, [Measures].[M2])'
--WAY 2 - You have to put the selected measure in the set
SET [Selected Measure] AS '{[Measures].[M1]}'
MEMBER [Measures].[Don't Show 0 Bis] AS 'IIF([Selected Measure].Item(0) = 0, NULL, [Selected Measure].Item(0))'
SELECT
NON EMPTY CROSSJOIN({[Fecha]}, {[Don't Show 0 Bis]} ) ON COLUMNS,
--NON EMPTY [Fecha] ON COLUMNS, --<== This is for WAY 1
NON EMPTY{[Mercado].[Standard]} ON ROWS
FROM ST_StockResumenII
--WHERE ([Measures].[M1]) --<== YOU CAN FILTER MEASURE BY THIS IN WAY 1

Non empty for calculated members

I have a cube with a calculated member The calculation for the calculated me
mber "MyMembers" is a simple addition:
[Measures].[NoOfOwners] + [Measures].[NoOfUsers]
In the MDX select statment I use the "non empty" expression on both columns
and rows. My MDX query goes like this:
SELECT
non empty {[TMD_Products].[All TMD_Products].[Datatjenester
].[Bedriftsnett]} on columns ,
non empty {[TMD_KIDs].[Kid].members} on rows
from TMC_Products where ([Measures].[MyMembers])
The problem is that when I substitute "MyMembers" in the from statement with
"NoOfOwners" or "NoOfUsers" I always get the same number of rows as with "M
yMembers". The query seems to return correct values for the member but does
not suppress non empty valu
es. In stead the query returns the value 0.
How can I set up my query to supress the the lines with value 0?
I run SQL Server 2000 Enterprise edition with Analysis Server on windows 200
0 server, Service Pack 3a is installed for sql server and analysis server.
Regards
Tore G.Hello Tore,
I would appreciate your patience while I am looking into this issue. I will
post my response at soon as I have update for you. In the mean time, if you
have any other useful information, please feel free to let me know.
Thank you,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Tore,
I learned you want to set up the query to suppress the lines with value 0.
When we use NON EMPTY keyword, it is important to note that this function
screens out empty tuples, not individual empty cells. Because of this,
empty cells can appear in a result dataset even when the NON EMPTY keyword
is used For example:
With member [Measures].[MYTESTCAL] as '[Measures].[Units
Shipped]+[Measures].[Units Ordered]'
Select
NON EMPTY{[Time].[1997],[Time].[1998] } on columns,
NON EMPTY [Store].[Store Name].members on rows
From Warehouse where ([Measures].[MYTESTCAL])
I tried the above MDX statements on FoodMart 2000 sample database using MDX
Sample application and use [Measures].[Units Shipped] or [Measur
es].[Units
Ordered] instead of [Measures].[MYTESTCAL]. It seems the query did n
ot
return 0 instead of NuLL. I am afraid I cannot reproduce the problem you
described. If it is possible, please create a example on the FoodMart 2000
sample database so that I can reproduce it in house and perform further
research.
I also found the following articles for your reference.
244650 INF: Working with NULL Values in OLAP Services
http://support.microsoft.com/?id=244650
Working with Empty Cells
http://msdn.microsoft.com/library/d...-us/olapdmad/ag
mdxadvanced_8jcj.asp
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Have you tried using the 'Filter' function?
"T. Gylver" <nospam@.tgylnospam.no> wrote in message
news:9BD94A01-B03F-4B75-9224-CF3C9CF471E6@.microsoft.com...
> I have a cube with a calculated member The calculation for the calculated
member "MyMembers" is a simple addition:
> [Measures].[NoOfOwners] + [Measures].[NoOfUsers]
> In the MDX select statment I use the "non empty" expression on both
columns and rows. My MDX query goes like this:
> SELECT
> non empty {[TMD_Products].[All
TMD_Products].[Datatjenester].[Bedriftsnett]} on columns ,
> non empty {[TMD_KIDs].[Kid].members} on rows
> from TMC_Products where ([Measures].[MyMembers])
>
> The problem is that when I substitute "MyMembers" in the from statement
with "NoOfOwners" or "NoOfUsers" I always get the same number of rows as
with "MyMembers". The query seems to return correct values for the member
but does not suppress non empty values. In stead the query returns the value
0.
> How can I set up my query to supress the the lines with value 0?
> I run SQL Server 2000 Enterprise edition with Analysis Server on windows
2000 server, Service Pack 3a is installed for sql server and analysis
server.
> Regards
> Tore G.|||WITH
MEMBER [Measures].[M1] AS '10'
MEMBER [Measures].[M2] AS '0'
MEMBER [Measures].[M] AS '[Measures].[M1] + [Measures].&
#91;M2]'
--WAY 1
MEMBER [Measures].[Don't Show 0] AS 'IIF([Measures].[M2] = 0
,NULL, [Measures].[M2])'
--WAY 2 - You have to put the selected measure in the set
SET [Selected Measure] AS '{[Measures].[M1]}'
MEMBER [Measures].[Don't Show 0 Bis] AS 'IIF([Selected Measure].
Item(0) = 0, NULL, [Selected Measure].Item(0))'
SELECT
NON EMPTY CROSSJOIN({[Fecha]}, {[Don't Show 0 Bis]} ) ON C
OLUMNS,
--NON EMPTY [Fecha] ON COLUMNS, --<== This is for WAY 1
NON EMPTY{[Mercado].[Standard]} ON ROWS
FROM ST_StockResumenII
--WHERE ([Measures].[M1]) --<== YOU CAN FILTER MEASURE BY THIS IN WA
Y 1

NON EMPTY

Hello
I have an MDX statement that looks like this:
SELECT
{ Measures.members } on Columns ,
{ CrossJoin( [Time].[Calendar].[2004].[May]:[Time].
[Calendar].[2004].[July],[Business].[User].[Business
Entity Id].[212].Children ) } on Rows
FROM UsageStats
And it returns this via a matrix report:
2004
|--|--|--|--|
| May | June | July |Total |
--|--|--|--|--|
Alan Smith | 2 | 3 | 2 | 7 |
Amy Marcus | | 3 | 3 | 6 |
Bob Fields | | | | |
Carry Grant | 1 | | | 1 |
--|--|--|--|--|
TOTAL | 3 | 6 | 5 | 14 |
--|--|--|--|--|
The report above represents hits to a website on a per-
Monthly basis for each user.
I would like to supress the empty rows so that Bob Fields,
above, won't show up at all. I tried using
NonEmptyCrossJoin like this:
SELECT
{ Measures.members } on Columns ,
{ NonEmptyCrossJoin( [Time].[Calendar].[2004].[May]:
[Time].[Calendar].[2004].[July],[Business].[User].
[Business Entity Id].[212].Children ) } on Rows
FROM UsageStats
and just NON EMPTY like this:
SELECT
{ Measures.members } on Columns ,
{ NonEmptyCrossJoin( [Time].[Calendar].[2004].[May]:
[Time].[Calendar].[2004].[July],[Business].[User].
[Business Entity Id].[212].Children ) } on Rows
FROM UsageStats
Both methods above supress the empty rows, but they also
supress anyone who hasn't had any hits to the site in the
first month, even if they do have hits in the 2nd or 3rd
months (so both Bob Fields, and Amy Marcus are both left
out of the report above).
Does anyone know how to possibly get around this? Any help
at all will be greatly appreciated!!! :D
Best regards
JanoYou might be able to use FILTER in conjunction with the CoalesceEmpty to
meet your need:
Example of CoalesceEmpty against Foodmart 2000 Sales cube to set
Measures.[Store Sales] to 0 for any Quarter that is empty:
=====================================================================WITH
MEMBER [Measures].[NonEmpty Sales] AS
'CoalesceEmpty(Measures.[Store Sales], 0)'
SELECT
CROSSJOIN( {[Time].[1997].children}, { [Measures].[NonEmpty
Sales] } )
ON COLUMNS,
CROSSJOIN( { [Store].[Store City].members }, {[Product].[Product
Department].members} )
ON ROWS
FROM Sales
=====================================================================
Now we add FILTER statement to remove any stores that did not have sales in
all Quarters:
=====================================================================WITH
MEMBER [Measures].[NonEmpty Sales] AS
'CoalesceEmpty(Measures.[Store Sales], 0)'
SELECT
CROSSJOIN( {[Time].[1997].children}, { [Measures].[NonEmpty
Sales] } )
ON COLUMNS,
FILTER(
CROSSJOIN( { [Store].[Store City].members }, {[Product].[Product
Department].members} ),
([Store].CurrentMember,
[Product].CurrentMember, [Measures].[NonEmpty Sales]) > 0 )
ON ROWS
FROM Sales
=====================================================================--
-- John H. Miller [MSFT]
-- SQL Server BI Product Group
----
--
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
----
--
jhmiller@.online.microsoft.com
"Jano" <JanoBermudes@.microsoft.com> wrote in message
news:31d401c47eca$c1c7b200$a301280a@.phx.gbl...
> Hello
> I have an MDX statement that looks like this:
> SELECT
> { Measures.members } on Columns ,
> { CrossJoin( [Time].[Calendar].[2004].[May]:[Time].
> [Calendar].[2004].[July],[Business].[User].[Business
> Entity Id].[212].Children ) } on Rows
> FROM UsageStats
>
> And it returns this via a matrix report:
> 2004
> |--|--|--|--|
> | May | June | July |Total |
> --|--|--|--|--|
> Alan Smith | 2 | 3 | 2 | 7 |
> Amy Marcus | | 3 | 3 | 6 |
> Bob Fields | | | | |
> Carry Grant | 1 | | | 1 |
> --|--|--|--|--|
> TOTAL | 3 | 6 | 5 | 14 |
> --|--|--|--|--|
> The report above represents hits to a website on a per-
> Monthly basis for each user.
>
> I would like to supress the empty rows so that Bob Fields,
> above, won't show up at all. I tried using
> NonEmptyCrossJoin like this:
> SELECT
> { Measures.members } on Columns ,
> { NonEmptyCrossJoin( [Time].[Calendar].[2004].[May]:
> [Time].[Calendar].[2004].[July],[Business].[User].
> [Business Entity Id].[212].Children ) } on Rows
> FROM UsageStats
> and just NON EMPTY like this:
> SELECT
> { Measures.members } on Columns ,
> { NonEmptyCrossJoin( [Time].[Calendar].[2004].[May]:
> [Time].[Calendar].[2004].[July],[Business].[User].
> [Business Entity Id].[212].Children ) } on Rows
> FROM UsageStats
> Both methods above supress the empty rows, but they also
> supress anyone who hasn't had any hits to the site in the
> first month, even if they do have hits in the 2nd or 3rd
> months (so both Bob Fields, and Amy Marcus are both left
> out of the report above).
> Does anyone know how to possibly get around this? Any help
> at all will be greatly appreciated!!! :D
> Best regards
> Jano
>