Autonumber field: enterprise

Showing posts with label enterprise. Show all posts

Tuesday, March 27, 2012

Backing up SQL databases

I am backing up three databases being used for a
inventory program. I have scheduled these databases to
be backed up at night using enterprise manager. If I
needed to restore any of these databases would I have to
restore all three? I do not back up transaction logs
because they do not need to restore to a specific point
(they could restore back to the previous night when the
databases were being backed up). Also do I have to
backup the other default databases that came with the SQl
2000 server in order to restore the three databases
specific to the inventory program?> If I
> needed to restore any of these databases would I have to
> restore all three?
Technically, each database can be restored independently. However, your
inventory application might need the data in all three databases to be in
sync. It is probably safest to restore all three unless you know enough
about the app to be certain restoring a single database isn't a problem.
> Also do I have to
> backup the other default databases that came with the SQl
> 2000 server in order to restore the three databases
> specific to the inventory program?
You should backup the master and msdb system databases as part of your
backup plan. These system databases only need to be restored if they are
lost or corrupted. You don't need to restore them before your inventory
databases.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:451701c4800c$cb3dbbc0$a301280a@.phx.gbl...
> I am backing up three databases being used for a
> inventory program. I have scheduled these databases to
> be backed up at night using enterprise manager. If I
> needed to restore any of these databases would I have to
> restore all three? I do not back up transaction logs
> because they do not need to restore to a specific point
> (they could restore back to the previous night when the
> databases were being backed up). Also do I have to
> backup the other default databases that came with the SQl
> 2000 server in order to restore the three databases
> specific to the inventory program?|||OK thanks. Most of that makes sense to me. However the
last part you said referring to the default
databases, "you don't need to restore them before the
inventory databases." Do you mean if I had to restore
the inventory databases I would not have to restore these
default databases? Also if these default databases
became corrupt like you said would they not be able to be
replaced without having a backup of them. I mean if they
are default databases do they ever change? Are they
(default databases) unique to my particular SQL server
installation and are needed inclusively to run my
inventory app?
Lastly if I backup these three databases at night at
separate times wihtin a couple of hours of each when no
one is accessing the inventory program at all would they
still be in sync if restored all at once. If not could I
schedule all three databases to be backed up at the same
time to the server hard drive(I later back the database
backup files to tape).
>--Original Message--
>> needed to restore any of these databases would I have
to
>> restore all three?
>Technically, each database can be restored
independently. However, your
>inventory application might need the data in all three
databases to be in
>sync. It is probably safest to restore all three unless
you know enough
>about the app to be certain restoring a single database
isn't a problem.
>> Also do I have to
>> backup the other default databases that came with the
SQl
>> 2000 server in order to restore the three databases
>> specific to the inventory program?
>You should backup the master and msdb system databases
as part of your
>backup plan. These system databases only need to be
restored if they are
>lost or corrupted. You don't need to restore them
before your inventory
>databases.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:451701c4800c$cb3dbbc0$a301280a@.phx.gbl...
>> I am backing up three databases being used for a
>> inventory program. I have scheduled these databases to
>> be backed up at night using enterprise manager. If I
>> needed to restore any of these databases would I have
to
>> restore all three? I do not back up transaction logs
>> because they do not need to restore to a specific point
>> (they could restore back to the previous night when the
>> databases were being backed up). Also do I have to
>> backup the other default databases that came with the
SQl
>> 2000 server in order to restore the three databases
>> specific to the inventory program?
>
>.
>|||Hi,
Answers to your queries:-
1. Do you mean if I had to restore the inventory databases I would not
have to restore these
default databases?
No need to restore the default (system) databases while restoring Inventory
databases. System database keeps tract of database, logins, backups , jobs
... information. But you need to backup and keep these datases in a safe
location, which will be helpful during a crash or corruption.
2. I mean if they are default databases do they ever change?
Yes, they will change. Especially MASTER and MSDB database.
Master database will change while you add a new database, add a new login,
change parameters ...
MSDB database will change when you add a new Job (task), operator, backup,
restore ....
SO it is necessory to restore these backup during problem sitation.
3. Are they (default databases) unique to my particular SQL server
installation and are needed inclusively to run my inventory app?
Uniqueness will be there. Because those databases store the Server name,
logins, system informations...
4. If not could I schedule all three databases to be backed up at the same
time to the server hard drive?
You could backup these databases simulateneosly or one by one. One by one
is also fine, Because changes to these database will happen only during the
above mentioned time (answer to my second question)
Thanks
Hari
MCDBA
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:4b1f01c4801a$65fef810$a401280a@.phx.gbl...
> OK thanks. Most of that makes sense to me. However the
> last part you said referring to the default
> databases, "you don't need to restore them before the
> inventory databases." Do you mean if I had to restore
> the inventory databases I would not have to restore these
> default databases? Also if these default databases
> became corrupt like you said would they not be able to be
> replaced without having a backup of them. I mean if they
> are default databases do they ever change? Are they
> (default databases) unique to my particular SQL server
> installation and are needed inclusively to run my
> inventory app?
> Lastly if I backup these three databases at night at
> separate times wihtin a couple of hours of each when no
> one is accessing the inventory program at all would they
> still be in sync if restored all at once. If not could I
> schedule all three databases to be backed up at the same
> time to the server hard drive(I later back the database
> backup files to tape).
> >--Original Message--
> >> needed to restore any of these databases would I have
> to
> >> restore all three?
> >
> >Technically, each database can be restored
> independently. However, your
> >inventory application might need the data in all three
> databases to be in
> >sync. It is probably safest to restore all three unless
> you know enough
> >about the app to be certain restoring a single database
> isn't a problem.
> >
> >> Also do I have to
> >> backup the other default databases that came with the
> SQl
> >> 2000 server in order to restore the three databases
> >> specific to the inventory program?
> >
> >You should backup the master and msdb system databases
> as part of your
> >backup plan. These system databases only need to be
> restored if they are
> >lost or corrupted. You don't need to restore them
> before your inventory
> >databases.
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >"Andy" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:451701c4800c$cb3dbbc0$a301280a@.phx.gbl...
> >> I am backing up three databases being used for a
> >> inventory program. I have scheduled these databases to
> >> be backed up at night using enterprise manager. If I
> >> needed to restore any of these databases would I have
> to
> >> restore all three? I do not back up transaction logs
> >> because they do not need to restore to a specific point
> >> (they could restore back to the previous night when the
> >> databases were being backed up). Also do I have to
> >> backup the other default databases that came with the
> SQl
> >> 2000 server in order to restore the three databases
> >> specific to the inventory program?
> >
> >
> >.
> >|||>> Lastly if I backup these three databases at night at
>> separate times wihtin a couple of hours of each when no
>> one is accessing the inventory program at all would they
>>still be in sync if restored all at once.
If there is no activity at night, you should not have a consistency problem.
However, if there's activity at night, and if a user adds/modifies data to
database A and B, but not to C, you might hit a consistency issue since the
backups of each database will end at different times. In this case, you
will need to backup the transaction logs and restore to a point-in-time for
all 3 databases to guarantee consistency (or at least close to it, since
there's still a chance a trx is committed on database A, but not yet on B
and C at that time).
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:4b1f01c4801a$65fef810$a401280a@.phx.gbl...
> OK thanks. Most of that makes sense to me. However the
> last part you said referring to the default
> databases, "you don't need to restore them before the
> inventory databases." Do you mean if I had to restore
> the inventory databases I would not have to restore these
> default databases? Also if these default databases
> became corrupt like you said would they not be able to be
> replaced without having a backup of them. I mean if they
> are default databases do they ever change? Are they
> (default databases) unique to my particular SQL server
> installation and are needed inclusively to run my
> inventory app?
> Lastly if I backup these three databases at night at
> separate times wihtin a couple of hours of each when no
> one is accessing the inventory program at all would they
> still be in sync if restored all at once. If not could I
> schedule all three databases to be backed up at the same
> time to the server hard drive(I later back the database
> backup files to tape).
>|||Can I also schedule backup of nondefault databases(our
inventory program) simultaneously?
>--Original Message--
>Hi,
>Answers to your queries:-
>1. Do you mean if I had to restore the inventory
databases I would not
>have to restore these
>default databases?
>No need to restore the default (system) databases while
restoring Inventory
>databases. System database keeps tract of database,
logins, backups , jobs
>... information. But you need to backup and keep these
datases in a safe
>location, which will be helpful during a crash or
corruption.
>2. I mean if they are default databases do they ever
change?
>Yes, they will change. Especially MASTER and MSDB
database.
>Master database will change while you add a new database,
add a new login,
>change parameters ...
>MSDB database will change when you add a new Job (task),
operator, backup,
>restore ....
>SO it is necessory to restore these backup during problem
sitation.
>3. Are they (default databases) unique to my particular
SQL server
>installation and are needed inclusively to run my
inventory app?
> Uniqueness will be there. Because those databases
store the Server name,
>logins, system informations...
>
>4. If not could I schedule all three databases to be
backed up at the same
>time to the server hard drive?
> You could backup these databases simulateneosly or one
by one. One by one
>is also fine, Because changes to these database will
happen only during the
> above mentioned time (answer to my second question)
>Thanks
>Hari
>MCDBA
>
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4b1f01c4801a$65fef810$a401280a@.phx.gbl...
>> OK thanks. Most of that makes sense to me. However the
>> last part you said referring to the default
>> databases, "you don't need to restore them before the
>> inventory databases." Do you mean if I had to restore
>> the inventory databases I would not have to restore
these
>> default databases? Also if these default databases
>> became corrupt like you said would they not be able to
be
>> replaced without having a backup of them. I mean if
they
>> are default databases do they ever change? Are they
>> (default databases) unique to my particular SQL server
>> installation and are needed inclusively to run my
>> inventory app?
>> Lastly if I backup these three databases at night at
>> separate times wihtin a couple of hours of each when no
>> one is accessing the inventory program at all would they
>> still be in sync if restored all at once. If not could
I
>> schedule all three databases to be backed up at the same
>> time to the server hard drive(I later back the database
>> backup files to tape).
>> >--Original Message--
>> >> needed to restore any of these databases would I have
>> to
>> >> restore all three?
>> >
>> >Technically, each database can be restored
>> independently. However, your
>> >inventory application might need the data in all three
>> databases to be in
>> >sync. It is probably safest to restore all three
unless
>> you know enough
>> >about the app to be certain restoring a single database
>> isn't a problem.
>> >
>> >> Also do I have to
>> >> backup the other default databases that came with the
>> SQl
>> >> 2000 server in order to restore the three databases
>> >> specific to the inventory program?
>> >
>> >You should backup the master and msdb system databases
>> as part of your
>> >backup plan. These system databases only need to be
>> restored if they are
>> >lost or corrupted. You don't need to restore them
>> before your inventory
>> >databases.
>> >
>> >--
>> >Hope this helps.
>> >
>> >Dan Guzman
>> >SQL Server MVP
>> >
>> >"Andy" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:451701c4800c$cb3dbbc0$a301280a@.phx.gbl...
>> >> I am backing up three databases being used for a
>> >> inventory program. I have scheduled these databases
to
>> >> be backed up at night using enterprise manager. If I
>> >> needed to restore any of these databases would I have
>> to
>> >> restore all three? I do not back up transaction logs
>> >> because they do not need to restore to a specific
point
>> >> (they could restore back to the previous night when
the
>> >> databases were being backed up). Also do I have to
>> >> backup the other default databases that came with the
>> SQl
>> >> 2000 server in order to restore the three databases
>> >> specific to the inventory program?
>> >
>> >
>> >.
>> >
>
>.
>sql

Backing up SQL databases

Sunday, March 25, 2012

Backing Up databases with Enterprise Manager

Please forgive my ignorance - I'm completely new to MSSQL.

Our office bought a set of Laserfiche licenses and had to also buy MSSQL to handle the database end of things. Part of my job is to make sure everything gets backed up. I want to use Enterprise Manager to back up the databases which seems straightforward enough - even though I've never done it.

Our Network Admin has installed a temporary USB drive to hold our back-ups until a more permanent form of media can be installed. He is using drive compression to maximize the space available. The server sees it as Drive F:

I created a folder on F: to contain my MSSQL Back-ups. Then I opened Enterprise Manager and clicked on SQL Server Group, which contains two databases:

(local) (Windows NT)

and

CYWD_IMAGE/LASERFICHE (Windows NT)

My understanding is that they both have to be backed up.

I clicked on the first one, then selected the Database Maintenence Plan Wizard from the 'Tools' menu. I stepped through the process (choosing 'all databases') until I got to the point where I could specify the back-up directory. I was able to browse right to the folder I created and choose it. I could then finish the process normally. The problem is that when I selected the second database (CYWD_IMAGE/LASERFICHE) and launched the wizard I was unable to browse to my folder. I could choose the F: Drive but no folder showed up under it. Oddly, it would not even show me my D: partition (just thought I'd mention that in case it is somehow relevent).

I don't see any difference in the properties tabs between these two databases. The obvious difference is the name, and that one says (local) and the other does not.

I really don't want to choose to dump the back-up into the root of F:

Can anyone tell me what I'm doing wrong?

Thanks in advance.

MacIf the plan created by your first Wizard backs up "all databases', why do you need to create a second backup plan?|||If the plan created by your first Wizard backs up "all databases', why do you need to create a second backup plan?

There are two servers under the SQL Servers Group, and each contain multiple databases. As far as I can tell the back-up plan will not span both servers, therefore the need for two plans.

It turns out the second server and it's databases are not currently in use so I lucked out. I am still curious why the second server and it's databases behaved this way, though.

Follow-up question. How to I review the plan I created to make modifications? If I pull up the Maintnence Planner there is no option to review exisiting plans.|||If the two servers are on different machines, then they will have different drives. You created a folder on the F drive of one machine, but not the other.

To review or edit the maitenance plans, open up the job they created in the SQL Server Agent tab.|||If the two servers are on different machines, then they will have different drives. You created a folder on the F drive of one machine, but not the other.

To review or edit the maitenance plans, open up the job they created in the SQL Server Agent tab.

Thanks - the servers both appear to be the same machine. Our other servers do not have external (or network) drives that would evaluate to F: - but it was a good thing to check for!

I was able to locate the SQL Server Agent Tab, I appreciate the information!

I'm sure I'll have other questions later, but I'm set for now.|||Be careful not to fill up your harddrive|||Be careful not to fill up your harddrive

Will do - Supposedly I'm going to have to burn off older jobs to dvd to avoid that.

Doesn't sounds like I'll be getting any additional storage drives any time soon, does it? ;)

Tuesday, March 20, 2012

backing up

hello,
I canot get my databases to automatically backup twice a day. I have tried
through enterprise manager a couple of different ways. Can someone tell me
how to do this?
Thanks!
Eric
What errors are you seing? Is SQLAgent running ?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Eric Throckmorton" <eric@.contractorsunlimited.com> wrote in message
news:%23%23jAasU3EHA.3388@.TK2MSFTNGP15.phx.gbl...
> hello,
> I canot get my databases to automatically backup twice a day. I have
> tried through enterprise manager a couple of different ways. Can someone
> tell me how to do this?
> Thanks!
> Eric
>
|||Eric,
Have you tried backing it up manually from Query Analyzer, does that work?
Have you tried backing up PUBS database or another database?
If you could include the error message that you are getting, that would be
helpful.
Thanks,
Fargham

Monday, March 19, 2012

Back or restore operation terminating abnormally

Hi all,
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29 2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation Enterprise Edition on
Windows NT 5.0 (Build 2195: Service Pack 4)
using windows system login, Loged in as Administrator, have full rights.
Getting the following Error message when trying to restore a database?
Back or restore operation terminating abnormally. SQLDumpExceptionhandler:
process 9 generated Fatal excetion c0000005 Exception_Access_Violation.
SQL Server is terminating this process.
No luck searching on this error. what I did find didn't help.
thanks
gvgv
I see that you do that from .NET application.
When you run the same statement from QA do you get the same error?
Can you show us you RESTORE command?
"gv" <viatorg@.musc.edu> wrote in message
news:uXJNhiWOFHA.2680@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29 2003 15:21:25
> Copyright (c) 1988-2002 Microsoft Corporation Enterprise Edition on
> Windows NT 5.0 (Build 2195: Service Pack 4)
> using windows system login, Loged in as Administrator, have full rights.
> Getting the following Error message when trying to restore a database?
> Back or restore operation terminating abnormally. SQLDumpExceptionhandler:
> process 9 generated Fatal excetion c0000005 Exception_Access_Violation.
> SQL Server is terminating this process.
> No luck searching on this error. what I did find didn't help.
> thanks
> gv
>

Sunday, March 11, 2012

AWE, SQL Server Enterprise, W2k STANDARD + 4GB RAM

Hi
Enabling AWE on SQL Enterprise Ed on a W2K STANDARD ed does appear to work (there seemed to be some doubt). I have reserved 3.5GB max server memory, and it has been allocated. The AWE counters in perfmon also seem to indicate it is functioning.
Does anyone have any experience with this configuration (good or bad)?
Also, any tips as to monitor memory usage - SQLServer/SQL Cache Memory doesn't appear to be of any use.
RegardsHi Stuart,
Not sure what you have done yet, but refers to the following article for
configuring AWE on SQL Server
HOW TO: Configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/defaul...kb;en-us;274750
On 24 Mar 2004 08:04:17 -0800, Stuart wrote:

> Hi
> Enabling AWE on SQL Enterprise Ed on a W2K STANDARD ed does appear to
> work (there seemed to be some doubt). I have reserved 3.5GB max server
> memory, and it has been allocated. The AWE counters in perfmon also
> seem to indicate it is functioning.
> Does anyone have any experience with this configuration (good or bad)?
> Also, any tips as to monitor memory usage - SQLServer/SQL Cache Memory
> doesn't appear to be of any use.
> Regards
> Stuart|||Thanks - have read the MS articles (they often seem contradictory as
to whether AWE would work on W2k Server Standard)
However, even though AWE it does work on W2K server, there seems to be
some doubt as to whether there is any point in doing this, e.g.
http://www.sql-server-performance.com/awe_memory.asp says should leave
AWE off for my config.
To date, the config is looking quite good - enabling AWE has doubled
the available RAM to SQL and the Page Life Expectancy has gone up to
about 3 hours (up from a few seconds)

.
Regards
Stuart
Yih-Yoon Lee <yihyoon@.hotmail.com> wrote in message news:<1lywgm6zbxldi.sdsrs0dj91ss$.dlg@.4
0tude.net>...
> Hi Stuart,
> Not sure what you have done yet, but refers to the following article for
> configuring AWE on SQL Server
> HOW TO: Configure memory for more than 2 GB in SQL Server
> http://support.microsoft.com/defaul...kb;en-us;274750
> On 24 Mar 2004 08:04:17 -0800, Stuart wrote:
>

AWE, 3GB and SQL Boost

Please help. Just installed the SQL 2005 enterprise on 2003 standard server with /3GB switch.
Now, in SQL there is an AWE option. Do I need to check that off and where do I set the memory manualy and not dinamicly?
Also, do I boost SQL priority in processors?
Can you please help and give some other tunning pointers? I set couple of boxes with 2000 SQL and always used 3GB switch but forgot the priority on processors and other fine tunning steps. Thanks in advance.George,
How much memory do you have on the server? How much do you want to give to
SQL?
Chris
"George" <George@.discussions.microsoft.com> wrote in message
news:4DA093A5-D72C-4276-A19A-385B81975C1F@.microsoft.com...
> Please help. Just installed the SQL 2005 enterprise on 2003 standard
> server
> with /3GB switch.
> Now, in SQL there is an AWE option. Do I need to check that off and where
> do
> I set the memory manualy and not dinamicly?
> Also, do I boost SQL priority in processors?
> Can you please help and give some other tunning pointers? I set couple of
> boxes with 2000 SQL and always used 3GB switch but forgot the priority on
> processors and other fine tunning steps. Thanks in advance.
>|||4GB on the server and 3GB to SQL
"Chris Wood" wrote:
> George,
> How much memory do you have on the server? How much do you want to give to
> SQL?
> Chris
> "George" <George@.discussions.microsoft.com> wrote in message
> news:4DA093A5-D72C-4276-A19A-385B81975C1F@.microsoft.com...
> > Please help. Just installed the SQL 2005 enterprise on 2003 standard
> > server
> > with /3GB switch.
> > Now, in SQL there is an AWE option. Do I need to check that off and where
> > do
> > I set the memory manualy and not dinamicly?
> > Also, do I boost SQL priority in processors?
> > Can you please help and give some other tunning pointers? I set couple of
> > boxes with 2000 SQL and always used 3GB switch but forgot the priority on
> > processors and other fine tunning steps. Thanks in advance.
> >
>
>|||George,
On server 2003 the switches should be set by the O/S. You will need to
configure AWE using sp_configure and then set max and min memory. I would
try with 2.5Gb first before you see if you can move up to 3Gb.
Chris
"George" <George@.discussions.microsoft.com> wrote in message
news:26F43606-A322-484B-84B7-7B339139B3A0@.microsoft.com...
> 4GB on the server and 3GB to SQL
> "Chris Wood" wrote:
>> George,
>> How much memory do you have on the server? How much do you want to give
>> to
>> SQL?
>> Chris
>> "George" <George@.discussions.microsoft.com> wrote in message
>> news:4DA093A5-D72C-4276-A19A-385B81975C1F@.microsoft.com...
>> > Please help. Just installed the SQL 2005 enterprise on 2003 standard
>> > server
>> > with /3GB switch.
>> > Now, in SQL there is an AWE option. Do I need to check that off and
>> > where
>> > do
>> > I set the memory manualy and not dinamicly?
>> > Also, do I boost SQL priority in processors?
>> > Can you please help and give some other tunning pointers? I set couple
>> > of
>> > boxes with 2000 SQL and always used 3GB switch but forgot the priority
>> > on
>> > processors and other fine tunning steps. Thanks in advance.
>> >
>>|||Ok. So
1. Set the 3GB switch in Boot.ini (did this before SQL installation)
2. Run the sp_configure and set it to 2.5
Please confirm. Also, is that check for AWE in GUI same as the sp command
via QA?
"Chris Wood" wrote:
> George,
> On server 2003 the switches should be set by the O/S. You will need to
> configure AWE using sp_configure and then set max and min memory. I would
> try with 2.5Gb first before you see if you can move up to 3Gb.
> Chris
> "George" <George@.discussions.microsoft.com> wrote in message
> news:26F43606-A322-484B-84B7-7B339139B3A0@.microsoft.com...
> > 4GB on the server and 3GB to SQL
> >
> > "Chris Wood" wrote:
> >
> >> George,
> >>
> >> How much memory do you have on the server? How much do you want to give
> >> to
> >> SQL?
> >>
> >> Chris
> >>
> >> "George" <George@.discussions.microsoft.com> wrote in message
> >> news:4DA093A5-D72C-4276-A19A-385B81975C1F@.microsoft.com...
> >> > Please help. Just installed the SQL 2005 enterprise on 2003 standard
> >> > server
> >> > with /3GB switch.
> >> > Now, in SQL there is an AWE option. Do I need to check that off and
> >> > where
> >> > do
> >> > I set the memory manualy and not dinamicly?
> >> > Also, do I boost SQL priority in processors?
> >> > Can you please help and give some other tunning pointers? I set couple
> >> > of
> >> > boxes with 2000 SQL and always used 3GB switch but forgot the priority
> >> > on
> >> > processors and other fine tunning steps. Thanks in advance.
> >> >
> >>
> >>
> >>
>
>|||Yes, checking and applying the 'Use AWE to allocate memory' checkbox is the
same as executing the following T-SQL code:
EXEC sys.sp_configure N'awe enabled', N'1'
RECONFIGURE WITH OVERRIDE
BTW, you almost never should boost the SQL process priority. In fact, Books
Online comes with the following warning:
"Raising the priority too high may drain resources from essential operating
system and network functions, resulting in problems shutting down SQL Server
or using other operating system tasks on the server."
Linchi
"George" wrote:
> Ok. So
> 1. Set the 3GB switch in Boot.ini (did this before SQL installation)
> 2. Run the sp_configure and set it to 2.5
> Please confirm. Also, is that check for AWE in GUI same as the sp command
> via QA?
> "Chris Wood" wrote:
> > George,
> >
> > On server 2003 the switches should be set by the O/S. You will need to
> > configure AWE using sp_configure and then set max and min memory. I would
> > try with 2.5Gb first before you see if you can move up to 3Gb.
> >
> > Chris
> >
> > "George" <George@.discussions.microsoft.com> wrote in message
> > news:26F43606-A322-484B-84B7-7B339139B3A0@.microsoft.com...
> > > 4GB on the server and 3GB to SQL
> > >
> > > "Chris Wood" wrote:
> > >
> > >> George,
> > >>
> > >> How much memory do you have on the server? How much do you want to give
> > >> to
> > >> SQL?
> > >>
> > >> Chris
> > >>
> > >> "George" <George@.discussions.microsoft.com> wrote in message
> > >> news:4DA093A5-D72C-4276-A19A-385B81975C1F@.microsoft.com...
> > >> > Please help. Just installed the SQL 2005 enterprise on 2003 standard
> > >> > server
> > >> > with /3GB switch.
> > >> > Now, in SQL there is an AWE option. Do I need to check that off and
> > >> > where
> > >> > do
> > >> > I set the memory manualy and not dinamicly?
> > >> > Also, do I boost SQL priority in processors?
> > >> > Can you please help and give some other tunning pointers? I set couple
> > >> > of
> > >> > boxes with 2000 SQL and always used 3GB switch but forgot the priority
> > >> > on
> > >> > processors and other fine tunning steps. Thanks in advance.
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >|||Thank you. I checked my prvious installations and you are right. I don't have
it on.
Now, as far as 3GB goes, do I keep it in the boot.ini and do I need some
other switch in addition?
If I put same valuses in both min and max memory, would that mean it is now
FIXED MEMORY SIZE assigned vs dynamically when there is a range?
SQL 2000 had a slider and a check box to choose from fixed and dynamyc (I am
sure you know of that just making a point)
"Linchi Shea" wrote:
> Yes, checking and applying the 'Use AWE to allocate memory' checkbox is the
> same as executing the following T-SQL code:
> EXEC sys.sp_configure N'awe enabled', N'1'
> RECONFIGURE WITH OVERRIDE
> BTW, you almost never should boost the SQL process priority. In fact, Books
> Online comes with the following warning:
> "Raising the priority too high may drain resources from essential operating
> system and network functions, resulting in problems shutting down SQL Server
> or using other operating system tasks on the server."
> Linchi
> "George" wrote:
> > Ok. So
> > 1. Set the 3GB switch in Boot.ini (did this before SQL installation)
> > 2. Run the sp_configure and set it to 2.5
> >
> > Please confirm. Also, is that check for AWE in GUI same as the sp command
> > via QA?
> >
> > "Chris Wood" wrote:
> >
> > > George,
> > >
> > > On server 2003 the switches should be set by the O/S. You will need to
> > > configure AWE using sp_configure and then set max and min memory. I would
> > > try with 2.5Gb first before you see if you can move up to 3Gb.
> > >
> > > Chris
> > >
> > > "George" <George@.discussions.microsoft.com> wrote in message
> > > news:26F43606-A322-484B-84B7-7B339139B3A0@.microsoft.com...
> > > > 4GB on the server and 3GB to SQL
> > > >
> > > > "Chris Wood" wrote:
> > > >
> > > >> George,
> > > >>
> > > >> How much memory do you have on the server? How much do you want to give
> > > >> to
> > > >> SQL?
> > > >>
> > > >> Chris
> > > >>
> > > >> "George" <George@.discussions.microsoft.com> wrote in message
> > > >> news:4DA093A5-D72C-4276-A19A-385B81975C1F@.microsoft.com...
> > > >> > Please help. Just installed the SQL 2005 enterprise on 2003 standard
> > > >> > server
> > > >> > with /3GB switch.
> > > >> > Now, in SQL there is an AWE option. Do I need to check that off and
> > > >> > where
> > > >> > do
> > > >> > I set the memory manualy and not dinamicly?
> > > >> > Also, do I boost SQL priority in processors?
> > > >> > Can you please help and give some other tunning pointers? I set couple
> > > >> > of
> > > >> > boxes with 2000 SQL and always used 3GB switch but forgot the priority
> > > >> > on
> > > >> > processors and other fine tunning steps. Thanks in advance.
> > > >> >
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >|||Can anyone please confirm I should keep the 3GB in boot.ini and enable AWE in
SQL?
"Linchi Shea" wrote:
> Yes, checking and applying the 'Use AWE to allocate memory' checkbox is the
> same as executing the following T-SQL code:
> EXEC sys.sp_configure N'awe enabled', N'1'
> RECONFIGURE WITH OVERRIDE
> BTW, you almost never should boost the SQL process priority. In fact, Books
> Online comes with the following warning:
> "Raising the priority too high may drain resources from essential operating
> system and network functions, resulting in problems shutting down SQL Server
> or using other operating system tasks on the server."
> Linchi
> "George" wrote:
> > Ok. So
> > 1. Set the 3GB switch in Boot.ini (did this before SQL installation)
> > 2. Run the sp_configure and set it to 2.5
> >
> > Please confirm. Also, is that check for AWE in GUI same as the sp command
> > via QA?
> >
> > "Chris Wood" wrote:
> >
> > > George,
> > >
> > > On server 2003 the switches should be set by the O/S. You will need to
> > > configure AWE using sp_configure and then set max and min memory. I would
> > > try with 2.5Gb first before you see if you can move up to 3Gb.
> > >
> > > Chris
> > >
> > > "George" <George@.discussions.microsoft.com> wrote in message
> > > news:26F43606-A322-484B-84B7-7B339139B3A0@.microsoft.com...
> > > > 4GB on the server and 3GB to SQL
> > > >
> > > > "Chris Wood" wrote:
> > > >
> > > >> George,
> > > >>
> > > >> How much memory do you have on the server? How much do you want to give
> > > >> to
> > > >> SQL?
> > > >>
> > > >> Chris
> > > >>
> > > >> "George" <George@.discussions.microsoft.com> wrote in message
> > > >> news:4DA093A5-D72C-4276-A19A-385B81975C1F@.microsoft.com...
> > > >> > Please help. Just installed the SQL 2005 enterprise on 2003 standard
> > > >> > server
> > > >> > with /3GB switch.
> > > >> > Now, in SQL there is an AWE option. Do I need to check that off and
> > > >> > where
> > > >> > do
> > > >> > I set the memory manualy and not dinamicly?
> > > >> > Also, do I boost SQL priority in processors?
> > > >> > Can you please help and give some other tunning pointers? I set couple
> > > >> > of
> > > >> > boxes with 2000 SQL and always used 3GB switch but forgot the priority
> > > >> > on
> > > >> > processors and other fine tunning steps. Thanks in advance.
> > > >> >
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >|||Chris, thanks. I can not go higher than 2.1 GB. Please help.
"Chris Wood" wrote:
> George,
> On server 2003 the switches should be set by the O/S. You will need to
> configure AWE using sp_configure and then set max and min memory. I would
> try with 2.5Gb first before you see if you can move up to 3Gb.
> Chris
> "George" <George@.discussions.microsoft.com> wrote in message
> news:26F43606-A322-484B-84B7-7B339139B3A0@.microsoft.com...
> > 4GB on the server and 3GB to SQL
> >
> > "Chris Wood" wrote:
> >
> >> George,
> >>
> >> How much memory do you have on the server? How much do you want to give
> >> to
> >> SQL?
> >>
> >> Chris
> >>
> >> "George" <George@.discussions.microsoft.com> wrote in message
> >> news:4DA093A5-D72C-4276-A19A-385B81975C1F@.microsoft.com...
> >> > Please help. Just installed the SQL 2005 enterprise on 2003 standard
> >> > server
> >> > with /3GB switch.
> >> > Now, in SQL there is an AWE option. Do I need to check that off and
> >> > where
> >> > do
> >> > I set the memory manualy and not dinamicly?
> >> > Also, do I boost SQL priority in processors?
> >> > Can you please help and give some other tunning pointers? I set couple
> >> > of
> >> > boxes with 2000 SQL and always used 3GB switch but forgot the priority
> >> > on
> >> > processors and other fine tunning steps. Thanks in advance.
> >> >
> >>
> >>
> >>
>
>|||George,
Have you seen this:- http://support.microsoft.com/kb/283037/en-us
Chris
"George" <George@.discussions.microsoft.com> wrote in message
news:F90DAA9C-1226-4E6F-9445-D4C3696A4556@.microsoft.com...
> Chris, thanks. I can not go higher than 2.1 GB. Please help.
> "Chris Wood" wrote:
>> George,
>> On server 2003 the switches should be set by the O/S. You will need to
>> configure AWE using sp_configure and then set max and min memory. I would
>> try with 2.5Gb first before you see if you can move up to 3Gb.
>> Chris
>> "George" <George@.discussions.microsoft.com> wrote in message
>> news:26F43606-A322-484B-84B7-7B339139B3A0@.microsoft.com...
>> > 4GB on the server and 3GB to SQL
>> >
>> > "Chris Wood" wrote:
>> >
>> >> George,
>> >>
>> >> How much memory do you have on the server? How much do you want to
>> >> give
>> >> to
>> >> SQL?
>> >>
>> >> Chris
>> >>
>> >> "George" <George@.discussions.microsoft.com> wrote in message
>> >> news:4DA093A5-D72C-4276-A19A-385B81975C1F@.microsoft.com...
>> >> > Please help. Just installed the SQL 2005 enterprise on 2003 standard
>> >> > server
>> >> > with /3GB switch.
>> >> > Now, in SQL there is an AWE option. Do I need to check that off and
>> >> > where
>> >> > do
>> >> > I set the memory manualy and not dinamicly?
>> >> > Also, do I boost SQL priority in processors?
>> >> > Can you please help and give some other tunning pointers? I set
>> >> > couple
>> >> > of
>> >> > boxes with 2000 SQL and always used 3GB switch but forgot the
>> >> > priority
>> >> > on
>> >> > processors and other fine tunning steps. Thanks in advance.
>> >> >
>> >>
>> >>
>> >>
>>|||Yes. To be honest, it is vague as some other Microsoft documents. Not sure
how to interpret the doc 100%.
So far, I understand that as long as I have no more than 4GB, I can dedicate
3 GB to SQL and 1GB to Windows and other apps by using only /3GB switch in
Boot.ini. My understanding was not to use PAE or AWE.
"Chris Wood" wrote:
> George,
> Have you seen this:- http://support.microsoft.com/kb/283037/en-us
> Chris
> "George" <George@.discussions.microsoft.com> wrote in message
> news:F90DAA9C-1226-4E6F-9445-D4C3696A4556@.microsoft.com...
> > Chris, thanks. I can not go higher than 2.1 GB. Please help.
> >
> > "Chris Wood" wrote:
> >
> >> George,
> >>
> >> On server 2003 the switches should be set by the O/S. You will need to
> >> configure AWE using sp_configure and then set max and min memory. I would
> >> try with 2.5Gb first before you see if you can move up to 3Gb.
> >>
> >> Chris
> >>
> >> "George" <George@.discussions.microsoft.com> wrote in message
> >> news:26F43606-A322-484B-84B7-7B339139B3A0@.microsoft.com...
> >> > 4GB on the server and 3GB to SQL
> >> >
> >> > "Chris Wood" wrote:
> >> >
> >> >> George,
> >> >>
> >> >> How much memory do you have on the server? How much do you want to
> >> >> give
> >> >> to
> >> >> SQL?
> >> >>
> >> >> Chris
> >> >>
> >> >> "George" <George@.discussions.microsoft.com> wrote in message
> >> >> news:4DA093A5-D72C-4276-A19A-385B81975C1F@.microsoft.com...
> >> >> > Please help. Just installed the SQL 2005 enterprise on 2003 standard
> >> >> > server
> >> >> > with /3GB switch.
> >> >> > Now, in SQL there is an AWE option. Do I need to check that off and
> >> >> > where
> >> >> > do
> >> >> > I set the memory manualy and not dinamicly?
> >> >> > Also, do I boost SQL priority in processors?
> >> >> > Can you please help and give some other tunning pointers? I set
> >> >> > couple
> >> >> > of
> >> >> > boxes with 2000 SQL and always used 3GB switch but forgot the
> >> >> > priority
> >> >> > on
> >> >> > processors and other fine tunning steps. Thanks in advance.
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||George,
You are indicating that 2 Gig is the ceiling on your machine. This will
always be the case if you have the Standard Edition as opposed to the
Enterprise Edition of SQL Server 2000. You didn't indicate you were running
2005 which does not have this limitation.
I made this mistake myself, adding 6 GIG to a Windows 2000 Advanced Server.
We still run at 2 GIG with the Standard Edition. We are using the boot.ini
switch to enable Windows 2000 Advanced to access above the 4 Gig mark which
is the limit on Windows 2000 without the switch. 2003 server has similar
requirements, but I think it starts at 16 gig so there should not be a
problem on the boot.ini for a 4 GIG machine. It is only the fact that you
may be using the Standard Edition.
--
Regards,
Jamie
"George" wrote:
> Chris, thanks. I can not go higher than 2.1 GB. Please help.
> "Chris Wood" wrote:
> > George,
> >
> > On server 2003 the switches should be set by the O/S. You will need to
> > configure AWE using sp_configure and then set max and min memory. I would
> > try with 2.5Gb first before you see if you can move up to 3Gb.
> >
> > Chris
> >
> > "George" <George@.discussions.microsoft.com> wrote in message
> > news:26F43606-A322-484B-84B7-7B339139B3A0@.microsoft.com...
> > > 4GB on the server and 3GB to SQL
> > >
> > > "Chris Wood" wrote:
> > >
> > >> George,
> > >>
> > >> How much memory do you have on the server? How much do you want to give
> > >> to
> > >> SQL?
> > >>
> > >> Chris
> > >>
> > >> "George" <George@.discussions.microsoft.com> wrote in message
> > >> news:4DA093A5-D72C-4276-A19A-385B81975C1F@.microsoft.com...
> > >> > Please help. Just installed the SQL 2005 enterprise on 2003 standard
> > >> > server
> > >> > with /3GB switch.
> > >> > Now, in SQL there is an AWE option. Do I need to check that off and
> > >> > where
> > >> > do
> > >> > I set the memory manualy and not dinamicly?
> > >> > Also, do I boost SQL priority in processors?
> > >> > Can you please help and give some other tunning pointers? I set couple
> > >> > of
> > >> > boxes with 2000 SQL and always used 3GB switch but forgot the priority
> > >> > on
> > >> > processors and other fine tunning steps. Thanks in advance.
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >|||/3GB is a boot.ini setting. Not need to configure anything inside SQL Server
for this.
Linchi
"George" wrote:
> Thank you. I checked my prvious installations and you are right. I don't have
> it on.
> Now, as far as 3GB goes, do I keep it in the boot.ini and do I need some
> other switch in addition?
> If I put same valuses in both min and max memory, would that mean it is now
> FIXED MEMORY SIZE assigned vs dynamically when there is a range?
> SQL 2000 had a slider and a check box to choose from fixed and dynamyc (I am
> sure you know of that just making a point)
> "Linchi Shea" wrote:
> > Yes, checking and applying the 'Use AWE to allocate memory' checkbox is the
> > same as executing the following T-SQL code:
> >
> > EXEC sys.sp_configure N'awe enabled', N'1'
> > RECONFIGURE WITH OVERRIDE
> >
> > BTW, you almost never should boost the SQL process priority. In fact, Books
> > Online comes with the following warning:
> >
> > "Raising the priority too high may drain resources from essential operating
> > system and network functions, resulting in problems shutting down SQL Server
> > or using other operating system tasks on the server."
> >
> > Linchi
> >
> > "George" wrote:
> >
> > > Ok. So
> > > 1. Set the 3GB switch in Boot.ini (did this before SQL installation)
> > > 2. Run the sp_configure and set it to 2.5
> > >
> > > Please confirm. Also, is that check for AWE in GUI same as the sp command
> > > via QA?
> > >
> > > "Chris Wood" wrote:
> > >
> > > > George,
> > > >
> > > > On server 2003 the switches should be set by the O/S. You will need to
> > > > configure AWE using sp_configure and then set max and min memory. I would
> > > > try with 2.5Gb first before you see if you can move up to 3Gb.
> > > >
> > > > Chris
> > > >
> > > > "George" <George@.discussions.microsoft.com> wrote in message
> > > > news:26F43606-A322-484B-84B7-7B339139B3A0@.microsoft.com...
> > > > > 4GB on the server and 3GB to SQL
> > > > >
> > > > > "Chris Wood" wrote:
> > > > >
> > > > >> George,
> > > > >>
> > > > >> How much memory do you have on the server? How much do you want to give
> > > > >> to
> > > > >> SQL?
> > > > >>
> > > > >> Chris
> > > > >>
> > > > >> "George" <George@.discussions.microsoft.com> wrote in message
> > > > >> news:4DA093A5-D72C-4276-A19A-385B81975C1F@.microsoft.com...
> > > > >> > Please help. Just installed the SQL 2005 enterprise on 2003 standard
> > > > >> > server
> > > > >> > with /3GB switch.
> > > > >> > Now, in SQL there is an AWE option. Do I need to check that off and
> > > > >> > where
> > > > >> > do
> > > > >> > I set the memory manualy and not dinamicly?
> > > > >> > Also, do I boost SQL priority in processors?
> > > > >> > Can you please help and give some other tunning pointers? I set couple
> > > > >> > of
> > > > >> > boxes with 2000 SQL and always used 3GB switch but forgot the priority
> > > > >> > on
> > > > >> > processors and other fine tunning steps. Thanks in advance.
> > > > >> >
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> > > >|||I have some empirical data on how much buffer pool mmeory a SQL instance
could consume with various settings of 3GB and AWE
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/11/awe-and-3gb-an-empirical-picture.aspx
Linchi
"George" wrote:
> Yes. To be honest, it is vague as some other Microsoft documents. Not sure
> how to interpret the doc 100%.
> So far, I understand that as long as I have no more than 4GB, I can dedicate
> 3 GB to SQL and 1GB to Windows and other apps by using only /3GB switch in
> Boot.ini. My understanding was not to use PAE or AWE.
> "Chris Wood" wrote:
> > George,
> >
> > Have you seen this:- http://support.microsoft.com/kb/283037/en-us
> >
> > Chris
> >
> > "George" <George@.discussions.microsoft.com> wrote in message
> > news:F90DAA9C-1226-4E6F-9445-D4C3696A4556@.microsoft.com...
> > > Chris, thanks. I can not go higher than 2.1 GB. Please help.
> > >
> > > "Chris Wood" wrote:
> > >
> > >> George,
> > >>
> > >> On server 2003 the switches should be set by the O/S. You will need to
> > >> configure AWE using sp_configure and then set max and min memory. I would
> > >> try with 2.5Gb first before you see if you can move up to 3Gb.
> > >>
> > >> Chris
> > >>
> > >> "George" <George@.discussions.microsoft.com> wrote in message
> > >> news:26F43606-A322-484B-84B7-7B339139B3A0@.microsoft.com...
> > >> > 4GB on the server and 3GB to SQL
> > >> >
> > >> > "Chris Wood" wrote:
> > >> >
> > >> >> George,
> > >> >>
> > >> >> How much memory do you have on the server? How much do you want to
> > >> >> give
> > >> >> to
> > >> >> SQL?
> > >> >>
> > >> >> Chris
> > >> >>
> > >> >> "George" <George@.discussions.microsoft.com> wrote in message
> > >> >> news:4DA093A5-D72C-4276-A19A-385B81975C1F@.microsoft.com...
> > >> >> > Please help. Just installed the SQL 2005 enterprise on 2003 standard
> > >> >> > server
> > >> >> > with /3GB switch.
> > >> >> > Now, in SQL there is an AWE option. Do I need to check that off and
> > >> >> > where
> > >> >> > do
> > >> >> > I set the memory manualy and not dinamicly?
> > >> >> > Also, do I boost SQL priority in processors?
> > >> >> > Can you please help and give some other tunning pointers? I set
> > >> >> > couple
> > >> >> > of
> > >> >> > boxes with 2000 SQL and always used 3GB switch but forgot the
> > >> >> > priority
> > >> >> > on
> > >> >> > processors and other fine tunning steps. Thanks in advance.
> > >> >> >
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

AWE, 3GB and SQL Boost

What is Query Analyzer's replacement in SQL Server 2005?

Both Query Analyzer and Enterprise Manager are gone from SQL Server 2005. In their place is a single tool, SQL Server Management Studio. This tool has most of the features of its predecessors, but with an upgraded UI and a lot of improved functionality. I think that most DBAs will find it to be a good upgrade. If you'd like more information on some of the new features with this tool, read my article on SQL Server Management Studio client tool enhancements.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1146801,00.htmlPAE allows the OS to use more than 4GB of memory. AWE allows the
application to use more than 4GB.
Andrew J. Kelly SQL MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
> guys,
> what is the difference between AWE and PAE?
|||for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
"Andrew J. Kelly" wrote:

> PAE allows the OS to use more than 4GB of memory. AWE allows the
> application to use more than 4GB.
> --
> Andrew J. Kelly SQL MVP
>
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
>
>
|||AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
want SQL Server to utilize > 4GB memory, you need both settings.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...[vbcol=seagreen]
> for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
> "Andrew J. Kelly" wrote:
|||in that case...in a server with 5G of ram
should i put the /AWE /PAE swith in the same line in the boot.ini?
For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
how do i check it has 5G? Also, is there any significant on it? The
performance shd be better i suppose
Thank you
"Tibor Karaszi" wrote:

> AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
> want SQL Server to utilize > 4GB memory, you need both settings.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...
>
>
|||boot.ini should have something like this:
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windo ws Server 2003,
Enterprise" /fastdetect /pae /3gb
(The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
provide a little more to the apps, i.e. SQL Server.)
To check the amount of physical RAM the OS is seeing you can just check
the Performance tab in task manager.
To turn on AWE memory for SQL Server you use the sp_configure stored
proc (in Query Analyzer for example):
exec sp_configure 'awe enabled', 1
reconfigure
go
Then you have to restart the SQL instance as the AWE setting only takes
affect on server startup. Also, when SQL Server is using AWE memory, it
cannot use dynamic memory management. It *will not swap pages out of
memory *if another app requests memory and the OS doesn't have enough to
satisfy the request (unlike the SQL dynamic memory manager). So you
should specify a "max server memory" amount with sp_configure. For
example, on your 5GB box, if you wanted to allocate 4GB to SQL and the
remaining 1GB to the OS & other apps, you would do this in QA:
exec sp_configure "max server memory", 5120
reconfigure
go
All this is documented in SQL BOL:
http://msdn.microsoft.com/library/de...onfig_3stg.asp
To see how much memory SQL Server is currently consuming you can open
the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff on
AWE & memory management.
HTH
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
rupart wrote:
[vbcol=seagreen]
>in that case...in a server with 5G of ram
>should i put the /AWE /PAE swith in the same line in the boot.ini?
>For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
>how do i check it has 5G? Also, is there any significant on it? The
>performance shd be better i suppose
>Thank you
>"Tibor Karaszi" wrote:
>
|||does /3g means the system will allocate 3g for system and the rest for
sql(that is after enabling thru AWE, rite?)?
yeah, good link...thank you
"Mike Hodgson" wrote:

> boot.ini should have something like this:
> multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windo ws Server 2003,
> Enterprise" /fastdetect /pae /3gb
> (The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
> provide a little more to the apps, i.e. SQL Server.)
> To check the amount of physical RAM the OS is seeing you can just check
> the Performance tab in task manager.
> To turn on AWE memory for SQL Server you use the sp_configure stored
> proc (in Query Analyzer for example):
> exec sp_configure 'awe enabled', 1
> reconfigure
> go
> Then you have to restart the SQL instance as the AWE setting only takes
> affect on server startup. Also, when SQL Server is using AWE memory, it
> cannot use dynamic memory management. It *will not swap pages out of
> memory *if another app requests memory and the OS doesn't have enough to
> satisfy the request (unlike the SQL dynamic memory manager). So you
> should specify a "max server memory" amount with sp_configure. For
> example, on your 5GB box, if you wanted to allocate 4GB to SQL and the
> remaining 1GB to the OS & other apps, you would do this in QA:
> exec sp_configure "max server memory", 5120
> reconfigure
> go
> All this is documented in SQL BOL:
> http://msdn.microsoft.com/library/de...onfig_3stg.asp
> To see how much memory SQL Server is currently consuming you can open
> the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
> Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff on
> AWE & memory management.
> HTH
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> rupart wrote:
>
|||The other way around. 3 GB for the application and 1 GB for the system.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:4944B057-1DC7-4638-BB88-9040FDCC9B9F@.microsoft.com...[vbcol=seagreen]
> does /3g means the system will allocate 3g for system and the rest for
> sql(that is after enabling thru AWE, rite?)?
> yeah, good link...thank you
> "Mike Hodgson" wrote:
|||Oops - slight typo in my "max server memory" statement. To set a max
server memory of 4GB you would run:
exec sp_configure "max server memory", 4096
reconfigure
go
The 5120 figure I included in my previous post would try to set it at
5GB (not 4GB).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Mike Hodgson wrote:
[vbcol=seagreen]
> boot.ini should have something like this:
> multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windo ws Server 2003,
> Enterprise" /fastdetect /pae /3gb
> (The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
> provide a little more to the apps, i.e. SQL Server.)
> To check the amount of physical RAM the OS is seeing you can just
> check the Performance tab in task manager.
> To turn on AWE memory for SQL Server you use the sp_configure stored
> proc (in Query Analyzer for example):
> exec sp_configure 'awe enabled', 1
> reconfigure
> go
> Then you have to restart the SQL instance as the AWE setting only
> takes affect on server startup. Also, when SQL Server is using AWE
> memory, it cannot use dynamic memory management. It *will not swap
> pages out of memory *if another app requests memory and the OS doesn't
> have enough to satisfy the request (unlike the SQL dynamic memory
> manager). So you should specify a "max server memory" amount with
> sp_configure. For example, on your 5GB box, if you wanted to allocate
> 4GB to SQL and the remaining 1GB to the OS & other apps, you would do
> this in QA:
> exec sp_configure "max server memory", 5120
> reconfigure
> go
> All this is documented in SQL BOL:
> http://msdn.microsoft.com/library/de...nfig_3stg..asp
> To see how much memory SQL Server is currently consuming you can open
> the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
> Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff
> on AWE & memory management.
> HTH
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> rupart wrote:

Tuesday, March 27, 2012

Backing up SQL databases

Backing up SQL databases

Backing up SQL databases

Sunday, March 25, 2012

Backing Up databases with Enterprise Manager

Tuesday, March 20, 2012

backing up

Monday, March 19, 2012

Back or restore operation terminating abnormally

Sunday, March 11, 2012

AWE, SQL Server Enterprise, W2k STANDARD + 4GB RAM

AWE, 3GB and SQL Boost

AWE, 3GB and SQL Boost

AWE, 3GB and SQL Boost

What is Query Analyzer's replacement in SQL Server 2005?

Autonumber field

Blog Archive

About Me