Sql Activity log backup: memory

Showing posts with label memory. Show all posts

Thursday, March 29, 2012

Ad-hoc reporting memory consumption

Hi to all, on our project we are facing this problem: our users using report builder are making reports containing several Mb of data (sometimes also 1Gb!). Now it happens that when 3-4 users do a report like that on the server the process w3wp.exe reaches also 10 Gb of memory allocated and then the whole reporting services istance stops working. We want to limit the number of rows that can be extracted from the sql server db using report builder. In the report model reference seems that there is not this kind of option (something like "max rowcount"). Is it possible to set this limit to the user that connects to sql server (something like: whatever is the query this user does, set @.@.rowcount to 10000)? Thanks. Alberto.

Hi Alberto,

We are currently facing your same issue. Did you get some solution to limit the rowcount on reportbuilder models? Thanks in advance for any ideas.

|||

Hi Cato, unluckly it seems that there isn't a good solution. Keep an eye on security filters on report models,this is the only thing that seems to "filter" data.

HTH.

Alberto

Ad-hoc reporting memory consumption

We want to limit the number of rows that can be extracted from the sql server db using report builder. In the report model reference seems that there is not this kind of option (something like "max rowcount"). Is it possible to set this limit to the user that connects to sql server (something like: whatever is the query this user does, set @.@.rowcount to 10000)?

Thanks.

Alberto.

Hi Alberto,

We are currently facing your same issue. Did you get some solution to limit the rowcount on reportbuilder models? Thanks in advance for any ideas.

|||

Hi Cato, unluckly it seems that there isn't a good solution. Keep an eye on security filters on report models,this is the only thing that seems to "filter" data.

HTH.

Alberto

Sunday, March 11, 2012

Adding physical CPU's to a server running SQL Server 2000

We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
The machine currently has 4 procs and 32GB physical memory.
We will be going to 8 procs and 64GB.
I'm pretty sure that the memory add is not big deal.
My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
4 more procs"?
Hi
On startup, when the OS detects the additional CPU's it uses them
automatically. The same goes for SQL Server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"frankm" wrote:

> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
> 4 more procs"?
>
>
|||thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...[vbcol=seagreen]
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
adding[vbcol=seagreen]
|||By default only I thought?
A system that big, has probably had a bit of tuning with the Affinity Mask and Max Memory settings.
It is running on Server 2003? - Not explicitly stated, if not then there is AWE stuff to set up as well.
Regards
AJ
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...[vbcol=seagreen]
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
|||No AWE setup on 64Bit Itaniums.
The true benefit of true 64Bit
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew John" wrote:

> By default only I thought?
> A system that big, has probably had a bit of tuning with the Affinity Mask and Max Memory settings.
> It is running on Server 2003? - Not explicitly stated, if not then there is AWE stuff to set up as well.
> Regards
> AJ
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
>
>
|||"frankm" <frankm@.nospam.postalias> wrote in message
news:ODUMMKOnFHA.3312@.TK2MSFTNGP12.phx.gbl...
> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to
> adding
> 4 more procs"?
>
Nice box... Do you have SAN drives too?
<drool drool drool>
How are you finding the speeds differences between the 32 and 64 bit
systems.
Rick

Adding physical CPU's to a server running SQL Server 2000

We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
The machine currently has 4 procs and 32GB physical memory.
We will be going to 8 procs and 64GB.
I'm pretty sure that the memory add is not big deal.
My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
4 more procs"?Hi
On startup, when the OS detects the additional CPU's it uses them
automatically. The same goes for SQL Server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"frankm" wrote:

> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to addi
ng
> 4 more procs"?
>
>|||thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...[vbcol=seagreen]
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
>
adding[vbcol=seagreen]|||By default only I thought?
A system that big, has probably had a bit of tuning with the Affinity Mask a
nd Max Memory settings.
It is running on Server 2003? - Not explicitly stated, if not then there is
AWE stuff to set up as well.
Regards
AJ
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...[vbcol=seagreen]
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
>|||No AWE setup on 64Bit Itaniums.
The true benefit of true 64Bit
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew John" wrote:

> By default only I thought?
> A system that big, has probably had a bit of tuning with the Affinity Mask
and Max Memory settings.
> It is running on Server 2003? - Not explicitly stated, if not then there i
s AWE stuff to set up as well.
> Regards
> AJ
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
>
>|||"frankm" <frankm@.nospam.postalias> wrote in message
news:ODUMMKOnFHA.3312@.TK2MSFTNGP12.phx.gbl...
> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to
> adding
> 4 more procs"?
>
Nice box... Do you have SAN drives too?
<drool drool drool>
How are you finding the speeds differences between the 32 and 64 bit
systems.
Rick

Adding physical CPU's to a server running SQL Server 2000

We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
The machine currently has 4 procs and 32GB physical memory.
We will be going to 8 procs and 64GB.
I'm pretty sure that the memory add is not big deal.
My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
4 more procs"?Hi
On startup, when the OS detects the additional CPU's it uses them
automatically. The same goes for SQL Server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"frankm" wrote:
> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
> 4 more procs"?
>
>|||thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
> > We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> > The machine currently has 4 procs and 32GB physical memory.
> > We will be going to 8 procs and 64GB.
> > I'm pretty sure that the memory add is not big deal.
> >
> > My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to
adding
> > 4 more procs"?
> >
> >
> >|||By default only I thought?
A system that big, has probably had a bit of tuning with the Affinity Mask and Max Memory settings.
It is running on Server 2003? - Not explicitly stated, if not then there is AWE stuff to set up as well.
Regards
AJ
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
> Hi
> On startup, when the OS detects the additional CPU's it uses them
> automatically. The same goes for SQL Server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "frankm" wrote:
>> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
>> The machine currently has 4 procs and 32GB physical memory.
>> We will be going to 8 procs and 64GB.
>> I'm pretty sure that the memory add is not big deal.
>> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
>> 4 more procs"?
>>|||No AWE setup on 64Bit Itaniums.
The true benefit of true 64Bit
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andrew John" wrote:
> By default only I thought?
> A system that big, has probably had a bit of tuning with the Affinity Mask and Max Memory settings.
> It is running on Server 2003? - Not explicitly stated, if not then there is AWE stuff to set up as well.
> Regards
> AJ
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:84EDFC2B-C8D1-40AE-A2E4-8BACC65EE541@.microsoft.com...
> > Hi
> >
> > On startup, when the OS detects the additional CPU's it uses them
> > automatically. The same goes for SQL Server.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "frankm" wrote:
> >
> >> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> >> The machine currently has 4 procs and 32GB physical memory.
> >> We will be going to 8 procs and 64GB.
> >> I'm pretty sure that the memory add is not big deal.
> >>
> >> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to adding
> >> 4 more procs"?
> >>
> >>
> >>
>
>|||"frankm" <frankm@.nospam.postalias> wrote in message
news:ODUMMKOnFHA.3312@.TK2MSFTNGP12.phx.gbl...
> We are going to be adding RAM and Physical CPU's to a HP4640 (64bit).
> The machine currently has 4 procs and 32GB physical memory.
> We will be going to 8 procs and 64GB.
> I'm pretty sure that the memory add is not big deal.
> My question is "How will SQL Server 2000 64bit sp3a, MS03031 react to
> adding
> 4 more procs"?
>
Nice box... Do you have SAN drives too?
<drool drool drool>
How are you finding the speeds differences between the 32 and 64 bit
systems.
Rick

Tuesday, March 6, 2012

Adding more RAM for MS SQL Server 2000

Hi
I have a MS SQL Server 2000 SP3 on MS Windows Server 2003 SP1 machine at my
hand. The SQL Server is configured for dynamic memory allocation. The machine
had 2GB of RAM in it when I installed the SQL Server. Now I've installed two
more gigabytes of RAM into the machine and I want to allow the SQL Server to
use a total of 3GB of RAM (by means of turning on the /3GB switch in Windows
boot.ini).
Do I also have to reconfigure any SQL Server parameters after enabling it to
use the additional gigabyte of RAM?
--
Many thanks,
OskarThis is a multi-part message in MIME format.
--060508000204080606090209
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
No. All you have to do is add the /3GB switch to boot.ini and reboot.
If you wanted to address > 4GB of RAM then you would have to start
playing with AWE but <= 4GB is addressable with normal windows memory
addressing & therefore normal MSSQL dynamic memory management.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Oskar wrote:
>Hi
>I have a MS SQL Server 2000 SP3 on MS Windows Server 2003 SP1 machine at my
>hand. The SQL Server is configured for dynamic memory allocation. The machine
>had 2GB of RAM in it when I installed the SQL Server. Now I've installed two
>more gigabytes of RAM into the machine and I want to allow the SQL Server to
>use a total of 3GB of RAM (by means of turning on the /3GB switch in Windows
>boot.ini).
>Do I also have to reconfigure any SQL Server parameters after enabling it to
>use the additional gigabyte of RAM?
>--
>Many thanks,
>Oskar
>
>
--060508000204080606090209
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>No.Â All you have to do is add the /3GB switch to boot.ini and
reboot.Â If you wanted to address > 4GB of RAM then you would have
to start playing with AWE but <= 4GB is addressable with normal
windows memory addressing & therefore normal MSSQL dynamic memory
management.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Oskar wrote:
<blockquote cite="midDA9DCAEE-A7F3-4E04-986C-97D1E43CDE91@.microsoft.com"
type="cite">
<pre wrap="">Hi
I have a MS SQL Server 2000 SP3 on MS Windows Server 2003 SP1 machine at my
hand. The SQL Server is configured for dynamic memory allocation. The machine
had 2GB of RAM in it when I installed the SQL Server. Now I've installed two
more gigabytes of RAM into the machine and I want to allow the SQL Server to
use a total of 3GB of RAM (by means of turning on the /3GB switch in Windows
boot.ini).
Do I also have to reconfigure any SQL Server parameters after enabling it to
use the additional gigabyte of RAM?
--
Many thanks,
Oskar
</pre>
</blockquote>
</body>
</html>
--060508000204080606090209--|||This is a multi-part message in MIME format.
--=_NextPart_000_004D_01C565EC.8E52DE90
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Hi,
This will work out if your SQL server 2000 edition is Enterprise or =Developer. Standard edition can support a maximum of 2 GB only.
Thanks
Hari
SQL Server MVP
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:OvpsIwbZFHA.4088@.TK2MSFTNGP15.phx.gbl...
No. All you have to do is add the /3GB switch to boot.ini and reboot. = If you wanted to address > 4GB of RAM then you would have to start =playing with AWE but <=3D 4GB is addressable with normal windows memory =addressing & therefore normal MSSQL dynamic memory management.
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W =http://www.mallesons.com=20
Oskar wrote: Hi
I have a MS SQL Server 2000 SP3 on MS Windows Server 2003 SP1 machine at =my hand. The SQL Server is configured for dynamic memory allocation. The =machine had 2GB of RAM in it when I installed the SQL Server. Now I've installed =two more gigabytes of RAM into the machine and I want to allow the SQL =Server to use a total of 3GB of RAM (by means of turning on the /3GB switch in =Windows boot.ini).
Do I also have to reconfigure any SQL Server parameters after enabling =it to use the additional gigabyte of RAM?
--
Many thanks,
Oskar
--=_NextPart_000_004D_01C565EC.8E52DE90
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">
&

Hi,
This will work out if your SQL server =2000 edition is Enterprise or Developer. Standard edition can support a maximum of 2 =GB only.
Thanks
Hari
SQL Server MVP
"Mike Hodgson" wrote in message news:OvpsIwbZFHA.4088=@.TK2MSFTNGP15.phx.gbl...No. All you have to do is add the /3GB switch to boot.ini and =reboot. If you wanted to address > 4GB of RAM then you would have to start playing =with AWE but <=3D 4GB is addressable with normal windows memory =addressing & therefore normal MSSQL dynamic memory management.
--mike =hodgson | database =administrator | mallesons =stephen jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.nospam.com | W http://www.mallesons.com Oskar wrote: Hi
I have a MS SQL Server 2000 SP3 on MS Windows Server 2003 SP1 machine at =my hand. The SQL Server is configured for dynamic memory allocation. The =machine had 2GB of RAM in it when I installed the SQL Server. Now I've installed =two more gigabytes of RAM into the machine and I want to allow the SQL =Server to use a total of 3GB of RAM (by means of turning on the /3GB switch in =Windows boot.ini).
Do I also have to reconfigure any SQL Server parameters after enabling =it to use the additional gigabyte of RAM?
--
Many thanks,
Oskar

--=_NextPart_000_004D_01C565EC.8E52DE90--

Thursday, March 29, 2012

Ad-hoc reporting memory consumption

Ad-hoc reporting memory consumption

Sunday, March 11, 2012

Adding physical CPU's to a server running SQL Server 2000

Adding physical CPU's to a server running SQL Server 2000

Adding physical CPU's to a server running SQL Server 2000

Tuesday, March 6, 2012

Adding more RAM for MS SQL Server 2000

Adding more RAM for MS SQL Server 2000

Adding more RAM for MS SQL Server 2000

Sql Activity log backup

Blog Archive

About Me