Backing Up SQL Server

I currently have two SQL servers, one of which is a replicator, and I have decided that it is long past time to start backing up properly. I have looked at a number of solutions but Veritas Backup Exec seems to be a decent product. I was wondering if I could get you opinions on this and any other products there might be out there (keeping in mind I am a small company on a budget). THXWhat types of backups are you talking about? Are you wanting to do database backups, or file backups to a tape device or something? Database backups can be done reliably using the sql tools, tape backups are a different matter.|||If you already have a server with a backup device you can configure the SQL Server to perform its backups to a share on that server. The files will be backed up along with the existing backup on the existing server (hopefully this is still clear).|||Im talking about doing a backup of my database and of the system databases. I was going to backup to a tape (AIT3) drive. I was looking at using Server's backup tool but I didn't know if it could backup hot or not. Once again thanks.|||SQL Server has actually made this very simple for you. Open up Enterprise Manager, and expand the Management folder. Then Right Click on the Database Maintenance Plans icon and select new maintenance plan. Then follow it through. SQL can back up any and all of its database while still serving data and filling requests. Then you can back up the .bak or .trn files dumped to tape after the maintenance plan has completed. Read up on maintenance plans in books online. There is alot of info in there.|||Yea I saw that but then I got to wondering if there was something else to that because I see companies like Veritas and commVolt making these backup packages and I kinda figured if they did that there must be somthing missing from Microsoft's backup, or is there? Thanks, SQLhack|||I have only come across one problem that I couldnt solve with the bundled SQL tools. We had a transaction log that was corrupted and couldnt apply it to our standby server. We actually had to get Lumigent Log Explorer to script it out and then played it forward. But that is another story. The other reason SQL backup solution may not work is for larger databases (100 Gig +) the restore times can be slow. Especially if your site is down during the restore. For the rest of my 6 years of SQL Server experience, database and log dumps copied off the server and backed up to tape have always gotten me out of any problem that hase come along.|||We used Veritas Backup Exec on many NT4 servers, and we upgraded our servers to Win2k, we decided to use Microsoft Backup instead, cause many tech of these sites complains about complexity to manage backup, etc.

Backing up databases and tlogs properly?

As I am learning more and more, I wanted to ask what most people think is the proper way to backup SQL DB and Tlogs.

Right now, I have one DB on the server (all the default DB's as well) and I have FULL recovery mode set.

Do most people use a script to backup the DB and TLogs?
Do they use enterprise manager?
Do they use maintenance plans?

I am planning on doing tlog backups every hour as that suits oure need, and full backups nightly.

What are some recommended approaches to doing this properly?

Thank you,
JasonHi Jason

Just so I know where you are coming from - are you unclear about some of the advice you got from SQLTeam or are you looking to get even more opinions on this? :)|||I use maintenance plans. Some pro's and some con's. Although I'm not a big fan of click-clack-done (I prefer scripting, like most DBA's do) this is a reasonable implementation by MS.
Biggest advantage in my humble opinion is that you can create backups with a pretty clear name, because the date/time is included by MS SQL. And still you can cleanup the files older than a certain time without having to script some kind of algorithm that decides what files to delete. It's not the most flexible sollution, but it works for me.

HTH a little.

Gr,
Yveau|||Hi Jason

Just so I know where you are coming from - are you unclear about some of the advice you got from SQLTeam or are you looking to get even more opinions on this? :)

Looking for more opinions to be honest.

I've heard so many things on this topic that it is confusing.

For example:

Don't use Maintenance wizard.
Use the maintenance wizard.
Don't use the Enterprise Manager. Use a script.
Don't use a script. Use EM.

Since I am very new, I am trying to compile all the information I can, experiences from others so I can make the best decision on what to do.

Sometimes I get really vague answers. Sometimes I get no answers. :S

In the end, it is about learning everything that I can. I want to be perfect and don't like to make mistakes.

Thanks.|||You'll get the same answers here too. I know posters that roll their own and others that use the maintenance plan here. Neither is a mistake and neither is perfect - you must make the decision as to what is best for your environment. The most important thing is you have thought about it, sought opinions, evaluated the repsonses, understood the issues and can explain your decision.

HTH|||You'll get the same answers here too. I know posters that roll their own and others that use the maintenance plan here. Neither is a mistake and neither is perfect - you must make the decision as to what is best for your environment. The most important thing is you have thought about it, sought opinions, evaluated the repsonses, understood the issues and can explain your decision.

HTH

Thanks. I appreciate it very much.

I just don't like making mistakes. :D I tend to be as thorough as I can be.

Right now, I am evaluating so many options, but one thing I have noticed immediately is that we need more space on our SQL server. :S

One question though:
After you initially backup a LARGE TLOG, will the following backups be smaller, assuming you back it up more frequently?

Just wanted to make sure.
Thanks,|||Assuming a constant rate of activity on your database ... yes. After a translog has been backed up, it starts from scratch again. So more frequent backups will give you more files, but smaller. Doing the backups less frequent will give you less files, but larger. In the end, the amount of space needed for only the backups is the same ...

If everything (MDF, LDF and backups are on the same discs (not recommended though), you're better of with frequent backups, because the LDF file can be seen as a 'copy' of the yet not made last translog backup. Keeping that small gives you some space ... but I don't think we're talking huge amounts of GB's here ...

When you've go yourself a db that does not have a constant rate of activity, there is a different story, because the LDF file grows along with the activity on the db.

Gr,
Yveau|||Another small issue I've been bumping into ... run the backup jobs as a SQL login, not a domain account ... you do need mixed mode security for this option.

When you have a network failure, the credentials for the domain account can't be checked and the backups will fail !!! Running under a SQL login (and please, do NOT use the 'sa' !!!) will create the backups, even when the domain controllers are not reachable for the SQL server.

Just a small issue to take into consideration ...

Gr,
Yveau

Hmm, reading it I think this might have been better placed in your 'Backing up databases and tlogs properly?' thread. Sorry ...

PatP: Ask for a move, and ye shall receive!|||Another small issue I've been bumping into ... run the backup jobs as a SQL login, not a domain account ... you do need mixed mode security for this option.

When you have a network failure, the credentials for the domain account can't be checked and the backups will fail !!! Running under a SQL login (and please, do NOT use the 'sa' !!!) will create the backups, even when the domain controllers are not reachable for the SQL server.

Just a small issue to take into consideration ...

Gr,
Yveau

Hmm, reading it I think this might have been better placed in your 'Backing up databases and tlogs properly?' thread. Sorry ...

PatP: Ask for a move, and ye shall receive!

One of the things I did was create a specific new account for SQL to run under. Right now, because my SQL boxes are NOT in the Domain, I created the accounts locally, and then mirroed them on the file servers. I made sure to include them in the 'administrators' account. During my test, it seems to work.

Is that ok?

No way would i use the 'sa' account. I come from a pretty strong security background (Security is a huge priority for me), so I stayed away from that route. :beer:|||That's one of many good options.
I usually create a SQL login (not a Windows account) something like 'BackupUser' and give it the 'db_backupoperator' database role. I tend to use one BackupUser for each server, but if you insist, you can use one per database. That login will only be able to do backup related statements. Then create a (set of) job(s) that run the backups at a predefined schedule and have the BackupUser run this job. The backup files will be written to the (file)server using the account that is running the SQL Server service, so set the permissions for that account on the backup disk location. Note that the BackupUser SQL login has nothing to do with the Windows account that is used to run the SQL Server.

In my humble opinion this is a pretty simple (no Windows related issues) an secure way (BackupUser can do only Backup related stuff in your db's) of setting up a backup scenario.

Good job avoiding the tempting 'sa' sollution ! Don't forget to test if you can restore the backups you create, otherwise you might be in for an unpleasant suprise when disaster strikes ...

Happy backup and restoring !

Gr,
Yveau

back up job not backing up properly?

I had created 7 jobs to back up our database every day, the files were
created, and the modified dates have been updating on the files that
were created, so I never bothered to check the data, as it was only a
temporary solution anyway. However, somebody at work recently deleted a
lot of data, so we went to restore last nights back-up, however the
back-ups apparently weren't working as intended. The data is old, here
is one of my scripts. Does anyone notice anything in there that might
'cause the files not to be updated properly with fresh data?
USE msdb
EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
@.enabled = 1,
@.description = 'Tuesday_MagStats_Backup',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'email name'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_MagStats_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE magstats TO DISK = ''c:\backups\Tuesday_databasebackup.bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_Magstats_Log_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG magstats TO DISK = ''c:\backups\Tuesday_log_databasebackup.log''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
@.server_name = N'(local)'
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
@.name = 'ScheduledBackup_MagStats_DB',
@.freq_type = 8, --weekly
@.freq_interval = 4, --Tuesday
@.freq_recurrence_factor = 1, --weekly inbetween
@.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
goApparently, this method will keep many back-ups within one file, and
when trying to restore through command line with osql, it restores the
frist one on the file. I was able to restore the correct database using
MS SQL Server Management Studio Express.|||If you don't use the WITH INIT option of the BACKUP command all new backups
are appended to the existing backup device. In order to restore from this
you need to tell SQL Server which backup in the device you need. Please
refer to RESTORE in BOL for more details.
--
Andrew J. Kelly SQL MVP
"KBuser" <sakred@.element9media.com> wrote in message
news:1137002233.526102.65220@.o13g2000cwo.googlegroups.com...
>I had created 7 jobs to back up our database every day, the files were
> created, and the modified dates have been updating on the files that
> were created, so I never bothered to check the data, as it was only a
> temporary solution anyway. However, somebody at work recently deleted a
> lot of data, so we went to restore last nights back-up, however the
> back-ups apparently weren't working as intended. The data is old, here
> is one of my scripts. Does anyone notice anything in there that might
> 'cause the files not to be updated properly with fresh data?
> USE msdb
> EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
> @.enabled = 1,
> @.description = 'Tuesday_MagStats_Backup',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 2,
> @.notify_level_email = 2,
> @.notify_level_netsend =2,
> @.notify_level_page = 2
> -- @.notify_email_operator_name = 'email name'
> go
> -- Add job step (backup data).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_MagStats_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP DATABASE magstats TO DISK => ''c:\backups\Tuesday_databasebackup.bak''',
> @.on_success_action = 3,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add job step (backup log).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_Magstats_Log_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP LOG magstats TO DISK => ''c:\backups\Tuesday_log_databasebackup.log''',
> @.on_success_action = 1,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add the target servers.
> USE msdb
> EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
> @.server_name = N'(local)'
> -- Schedule job.
> USE msdb
> EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
> @.name = 'ScheduledBackup_MagStats_DB',
> @.freq_type = 8, --weekly
> @.freq_interval = 4, --Tuesday
> @.freq_recurrence_factor = 1, --weekly inbetween
> @.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
> go
>

back up job not backing up properly?

I had created 7 jobs to back up our database every day, the files were
created, and the modified dates have been updating on the files that
were created, so I never bothered to check the data, as it was only a
temporary solution anyway. However, somebody at work recently deleted a
lot of data, so we went to restore last nights back-up, however the
back-ups apparently weren't working as intended. The data is old, here
is one of my scripts. Does anyone notice anything in there that might
'cause the files not to be updated properly with fresh data?
USE msdb
EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
@.enabled = 1,
@.description = 'Tuesday_MagStats_Backup',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'email name'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_MagStats_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE magstats TO DISK =
''c:\backups\Tuesday_databasebackup.bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_Magstats_Log_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG magstats TO DISK =
''c:\backups\Tuesday_log_databasebackup.log''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
@.server_name = N'(local)'
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
@.name = 'ScheduledBackup_MagStats_DB',
@.freq_type = 8, --weekly
@.freq_interval = 4, --Tuesday
@.freq_recurrence_factor = 1, --weekly inbetween
@.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
go
Apparently, this method will keep many back-ups within one file, and
when trying to restore through command line with osql, it restores the
frist one on the file. I was able to restore the correct database using
MS SQL Server Management Studio Express.
|||If you don't use the WITH INIT option of the BACKUP command all new backups
are appended to the existing backup device. In order to restore from this
you need to tell SQL Server which backup in the device you need. Please
refer to RESTORE in BOL for more details.
Andrew J. Kelly SQL MVP
"KBuser" <sakred@.element9media.com> wrote in message
news:1137002233.526102.65220@.o13g2000cwo.googlegro ups.com...
>I had created 7 jobs to back up our database every day, the files were
> created, and the modified dates have been updating on the files that
> were created, so I never bothered to check the data, as it was only a
> temporary solution anyway. However, somebody at work recently deleted a
> lot of data, so we went to restore last nights back-up, however the
> back-ups apparently weren't working as intended. The data is old, here
> is one of my scripts. Does anyone notice anything in there that might
> 'cause the files not to be updated properly with fresh data?
> USE msdb
> EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
> @.enabled = 1,
> @.description = 'Tuesday_MagStats_Backup',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 2,
> @.notify_level_email = 2,
> @.notify_level_netsend =2,
> @.notify_level_page = 2
> -- @.notify_email_operator_name = 'email name'
> go
> -- Add job step (backup data).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_MagStats_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP DATABASE magstats TO DISK =
> ''c:\backups\Tuesday_databasebackup.bak''',
> @.on_success_action = 3,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add job step (backup log).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_Magstats_Log_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP LOG magstats TO DISK =
> ''c:\backups\Tuesday_log_databasebackup.log''',
> @.on_success_action = 1,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add the target servers.
> USE msdb
> EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
> @.server_name = N'(local)'
> -- Schedule job.
> USE msdb
> EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
> @.name = 'ScheduledBackup_MagStats_DB',
> @.freq_type = 8, --weekly
> @.freq_interval = 4, --Tuesday
> @.freq_recurrence_factor = 1, --weekly inbetween
> @.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
> go
>

back up job not backing up properly?

I had created 7 jobs to back up our database every day, the files were
created, and the modified dates have been updating on the files that
were created, so I never bothered to check the data, as it was only a
temporary solution anyway. However, somebody at work recently deleted a
lot of data, so we went to restore last nights back-up, however the
back-ups apparently weren't working as intended. The data is old, here
is one of my scripts. Does anyone notice anything in there that might
'cause the files not to be updated properly with fresh data?
USE msdb
EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
@.enabled = 1,
@.description = 'Tuesday_MagStats_Backup',
@.owner_login_name = 'sa',
@.notify_level_eventlog = 2,
@.notify_level_email = 2,
@.notify_level_netsend =2,
@.notify_level_page = 2
-- @.notify_email_operator_name = 'email name'
go
-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_MagStats_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP DATABASE magstats TO DISK =
''c:\backups\Tuesday_databasebackup.bak''',
@.on_success_action = 3,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
@.step_name = 'Tuesday_Magstats_Log_Backup',
@.subsystem = 'TSQL',
@.command = 'BACKUP LOG magstats TO DISK =
''c:\backups\Tuesday_log_databasebackup.log''',
@.on_success_action = 1,
@.retry_attempts = 5,
@.retry_interval = 5
go
-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
@.server_name = N'(local)'
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
@.name = 'ScheduledBackup_MagStats_DB',
@.freq_type = 8, --weekly
@.freq_interval = 4, --Tuesday
@.freq_recurrence_factor = 1, --weekly inbetween
@.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
goApparently, this method will keep many back-ups within one file, and
when trying to restore through command line with osql, it restores the
frist one on the file. I was able to restore the correct database using
MS SQL Server Management Studio Express.|||If you don't use the WITH INIT option of the BACKUP command all new backups
are appended to the existing backup device. In order to restore from this
you need to tell SQL Server which backup in the device you need. Please
refer to RESTORE in BOL for more details.
Andrew J. Kelly SQL MVP
"KBuser" <sakred@.element9media.com> wrote in message
news:1137002233.526102.65220@.o13g2000cwo.googlegroups.com...
>I had created 7 jobs to back up our database every day, the files were
> created, and the modified dates have been updating on the files that
> were created, so I never bothered to check the data, as it was only a
> temporary solution anyway. However, somebody at work recently deleted a
> lot of data, so we went to restore last nights back-up, however the
> back-ups apparently weren't working as intended. The data is old, here
> is one of my scripts. Does anyone notice anything in there that might
> 'cause the files not to be updated properly with fresh data?
> USE msdb
> EXEC sp_add_job @.job_name = 'Tuesday_MagStats_Backup',
> @.enabled = 1,
> @.description = 'Tuesday_MagStats_Backup',
> @.owner_login_name = 'sa',
> @.notify_level_eventlog = 2,
> @.notify_level_email = 2,
> @.notify_level_netsend =2,
> @.notify_level_page = 2
> -- @.notify_email_operator_name = 'email name'
> go
> -- Add job step (backup data).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_MagStats_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP DATABASE magstats TO DISK =
> ''c:\backups\Tuesday_databasebackup.bak''',
> @.on_success_action = 3,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add job step (backup log).
> USE msdb
> EXEC sp_add_jobstep @.job_name = 'Tuesday_MagStats_Backup',
> @.step_name = 'Tuesday_Magstats_Log_Backup',
> @.subsystem = 'TSQL',
> @.command = 'BACKUP LOG magstats TO DISK =
> ''c:\backups\Tuesday_log_databasebackup.log''',
> @.on_success_action = 1,
> @.retry_attempts = 5,
> @.retry_interval = 5
> go
> -- Add the target servers.
> USE msdb
> EXEC sp_add_jobserver @.job_name = 'Tuesday_MagStats_Backup',
> @.server_name = N'(local)'
> -- Schedule job.
> USE msdb
> EXEC sp_add_jobschedule @.job_name = 'Tuesday_MagStats_Backup',
> @.name = 'ScheduledBackup_MagStats_DB',
> @.freq_type = 8, --weekly
> @.freq_interval = 4, --Tuesday
> @.freq_recurrence_factor = 1, --weekly inbetween
> @.active_start_time = '043000' -- (043000 = 4:30 AM) 24hr HHMMSS.
> go
>

AWE and Max Server Memory (MB)

What purpose does Max Server Memory (MB) serve, if I have 8 GB of RAM:
1. I have the switch properly set in the boot.ini
2. AWE Enabled is set to 1
3. Max Server Memory (MB) set to 2147483647
In other words, if I want to reserve 2 GB for my OS, then would not the Max
Server Memory (MB) set to 2147483647 allow SQL Server to use 6 GB?
Message posted via http://www.droptable.comYou want to set the max server memory to 6 GB = 6 * 1,204 MB = 7,224 MB.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:65e80daa1f3f8@.uwe...
What purpose does Max Server Memory (MB) serve, if I have 8 GB of RAM:
1. I have the switch properly set in the boot.ini
2. AWE Enabled is set to 1
3. Max Server Memory (MB) set to 2147483647
In other words, if I want to reserve 2 GB for my OS, then would not the Max
Server Memory (MB) set to 2147483647 allow SQL Server to use 6 GB?
Message posted via http://www.droptable.com|||You should also see my reply to your original post on this subject.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:65e80daa1f3f8@.uwe...
> What purpose does Max Server Memory (MB) serve, if I have 8 GB of RAM:
> 1. I have the switch properly set in the boot.ini
> 2. AWE Enabled is set to 1
> 3. Max Server Memory (MB) set to 2147483647
> In other words, if I want to reserve 2 GB for my OS, then would not the
> Max
> Server Memory (MB) set to 2147483647 allow SQL Server to use 6 GB?
> --
> Message posted via http://www.droptable.com
>

AWE and Max Server Memory (MB)

What purpose does Max Server Memory (MB) serve, if I have 8 GB of RAM:
1. I have the switch properly set in the boot.ini
2. AWE Enabled is set to 1
3. Max Server Memory (MB) set to 2147483647
In other words, if I want to reserve 2 GB for my OS, then would not the Max
Server Memory (MB) set to 2147483647 allow SQL Server to use 6 GB?
--
Message posted via http://www.sqlmonster.comYou want to set the max server memory to 6 GB = 6 * 1,204 MB = 7,224 MB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:65e80daa1f3f8@.uwe...
What purpose does Max Server Memory (MB) serve, if I have 8 GB of RAM:
1. I have the switch properly set in the boot.ini
2. AWE Enabled is set to 1
3. Max Server Memory (MB) set to 2147483647
In other words, if I want to reserve 2 GB for my OS, then would not the Max
Server Memory (MB) set to 2147483647 allow SQL Server to use 6 GB?
--
Message posted via http://www.sqlmonster.com|||You should also see my reply to your original post on this subject.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:65e80daa1f3f8@.uwe...
> What purpose does Max Server Memory (MB) serve, if I have 8 GB of RAM:
> 1. I have the switch properly set in the boot.ini
> 2. AWE Enabled is set to 1
> 3. Max Server Memory (MB) set to 2147483647
> In other words, if I want to reserve 2 GB for my OS, then would not the
> Max
> Server Memory (MB) set to 2147483647 allow SQL Server to use 6 GB?
> --
> Message posted via http://www.sqlmonster.com
>