Requsting advice on nightly backup

This is our current schedule for SQL backup:
Backups created by Enterprise Manager
Nightly backup 12:00midnight - Complete backup checked, Transaction Log Backup not checked
Weekly optimization on Saturday - Optimization and Ingetrigy checked.
Transaction Logs Every hour 8am to 9pm - Transaction Log Backup checked.
Is it overkill to include the Transaction Log backup on our Nightly backup ?
Thanks
--
Message posted via http://www.sqlmonster.comHi Jeffrey,
If you are ending transaction log backup at 9 PM and want to take
transaction log backup again at midnight, it will have 3 hrs of transactions.
But you are anyways taking full back up at midnight. I do not understand why
you want to take nightly transaction log backup.
If my understanding is wrong, please update me with more information.
--
Thanks
GYK|||Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this backup plan. You confirmed what I was second guessing that if I have 3 hours of transactions between 9pm and 12 (which is rare) they would be part of the complete backup at midnight with no need for the TRN backup.
Thanks
Jeff
--
Message posted via http://www.sqlmonster.com|||Keep in mind that the FULL and DIFFERNTIAL database backups are independent
from the LOG transaction log backups.
The transaction log will not remove transactions from the files until after
a transaction log backup is performed if you are running any recovery mode
except simple. A FULL nor a DIFFERENTIAL database backup does NOT remove
transactions from the transaction log regardless of the recovery model.
Sincerely,
Anthony Thomas
"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:418f0f5db2de404da1b2043e344372d5@.SQLMonster.com...
Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this
backup plan. You confirmed what I was second guessing that if I have 3
hours of transactions between 9pm and 12 (which is rare) they would be part
of the complete backup at midnight with no need for the TRN backup.
Thanks
Jeff
--
Message posted via http://www.sqlmonster.com

Requsting advice on nightly backup

This is our current schedule for SQL backup:
Backups created by Enterprise Manager
Nightly backup 12:00midnight - Complete backup checked, Transaction Log Backup not checked
Weekly optimization on Saturday - Optimization and Ingetrigy checked.
Transaction Logs Every hour 8am to 9pm - Transaction Log Backup checked.
Is it overkill to include the Transaction Log backup on our Nightly backup ?
Thanks
Message posted via http://www.sqlmonster.com
Hi Jeffrey,
If you are ending transaction log backup at 9 PM and want to take
transaction log backup again at midnight, it will have 3 hrs of transactions.
But you are anyways taking full back up at midnight. I do not understand why
you want to take nightly transaction log backup.
If my understanding is wrong, please update me with more information.
Thanks
GYK
|||Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this backup plan. You confirmed what I was second guessing that if I have 3 hours of transactions between 9pm and 12 (which is rare) they would be part of the complete backup at midnight
with no need for the TRN backup.
Thanks
Jeff
Message posted via http://www.sqlmonster.com
|||Keep in mind that the FULL and DIFFERNTIAL database backups are independent
from the LOG transaction log backups.
The transaction log will not remove transactions from the files until after
a transaction log backup is performed if you are running any recovery mode
except simple. A FULL nor a DIFFERENTIAL database backup does NOT remove
transactions from the transaction log regardless of the recovery model.
Sincerely,
Anthony Thomas

"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:418f0f5db2de404da1b2043e344372d5@.SQLMonster.c om...
Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this
backup plan. You confirmed what I was second guessing that if I have 3
hours of transactions between 9pm and 12 (which is rare) they would be part
of the complete backup at midnight with no need for the TRN backup.
Thanks
Jeff
Message posted via http://www.sqlmonster.com
|||Don't think of tr log backups as being connected to the full / diff
backups.
One of the good things about tr log backups is that if you have the full
sequence you can retore even if you have a corrupt full backup by going
back to the previous full backup.
Are you testing backups? Until you restore a backup you don't know
whether it is good or not. On important databases I do test restores on
every full backup. You also might consider log shipping the tr log
backups and applying them to the restored full.
I don't like doing integrity checks on production databases but do them
on the restored backup.
Have a look at
http://www.mindsdoor.net/#Administration
http://www.mindsdoor.net/SQLAdmin/Ba...Databases.html
http://www.mindsdoor.net/SQLAdmin/s_TestRestore.html
http://www.mindsdoor.net/SQLAdmin/s_nrSyncDir.html
There's stuff there to backup databases, copy backups and do test
restores and to copy the log backups - I'll at the sp to restores to
complete the log shipping sometime.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Requsting advice on nightly backup

This is our current schedule for SQL backup:
Backups created by Enterprise Manager
Nightly backup 12:00midnight - Complete backup checked, Transaction Log Back
up not checked
Weekly optimization on Saturday - Optimization and Ingetrigy checked.
Transaction Logs Every hour 8am to 9pm - Transaction Log Backup checked.
Is it overkill to include the Transaction Log backup on our Nightly backup ?
Thanks
Message posted via http://www.droptable.comHi Jeffrey,
If you are ending transaction log backup at 9 PM and want to take
transaction log backup again at midnight, it will have 3 hrs of transactions
.
But you are anyways taking full back up at midnight. I do not understand why
you want to take nightly transaction log backup.
If my understanding is wrong, please update me with more information.
Thanks
GYK|||Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this bac
kup plan. You confirmed what I was second guessing that if I have 3 hours o
f transactions between 9pm and 12 (which is rare) they would be part of the
complete backup at midnight
with no need for the TRN backup.
Thanks
Jeff
Message posted via http://www.droptable.com|||Keep in mind that the FULL and DIFFERNTIAL database backups are independent
from the LOG transaction log backups.
The transaction log will not remove transactions from the files until after
a transaction log backup is performed if you are running any recovery mode
except simple. A FULL nor a DIFFERENTIAL database backup does NOT remove
transactions from the transaction log regardless of the recovery model.
Sincerely,
Anthony Thomas
"Jeffrey Sheldon via droptable.com" <forum@.droptable.com> wrote in message
news:418f0f5db2de404da1b2043e344372d5@.SQ
droptable.com...
Hi GYK,
I don't want to do transaction logs again. Sorry a bit dizzy doing this
backup plan. You confirmed what I was second guessing that if I have 3
hours of transactions between 9pm and 12 (which is rare) they would be part
of the complete backup at midnight with no need for the TRN backup.
Thanks
Jeff
Message posted via http://www.droptable.com|||Don't think of tr log backups as being connected to the full / diff
backups.
One of the good things about tr log backups is that if you have the full
sequence you can retore even if you have a corrupt full backup by going
back to the previous full backup.
Are you testing backups? Until you restore a backup you don't know
whether it is good or not. On important databases I do test restores on
every full backup. You also might consider log shipping the tr log
backups and applying them to the restored full.
I don't like doing integrity checks on production databases but do them
on the restored backup.
Have a look at
http://www.mindsdoor.net/#Administration
http://www.mindsdoor.net/SQLAdmin/B...lDatabases.html
http://www.mindsdoor.net/SQLAdmin/s_TestRestore.html
http://www.mindsdoor.net/SQLAdmin/s_nrSyncDir.html
There's stuff there to backup databases, copy backups and do test
restores and to copy the log backups - I'll at the sp to restores to
complete the log shipping sometime.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

request advice on judging memory usage/allocation

Hello,
I'm relatively new to SQL Server, and have 'inherited' a SQL Server box
with the following specs:
Windows 2003 SP1, SQL Server 2000 SP 3 (or 3a?), single-processor
license
2.8 GHz Xeon, 2 GB RAM, about 2 years old
Great Plains 7.5 company DBs, about 8-9 users during the day, total DB
size < 5 GB? I don't know exactly.
Other apps are the OS, some Access MDBs accessed by Access front ends
via Citrix, and an Abra Suite DB (FoxPro tables, eventually to be
migrated to SQL Server, the Abra client is on Citrix servers.
We have a monitoring system through our support consultant. This system
tells us now and then that SQL has used up all its physical memory, but
these alerts occur at odd times like midnight on a Sunday (to be fair,
this is around the time when SQL does its maintenance, backup, etc), or
540 pm when the main GP users have all gone home and are not doing
anything in GP. I don't know yet if SQL is paging a lot, but no
specific end user complaints have appeared yet, so we have time to be
pro-active.
I've recently found some information on which PerfMon items to check,
and will try these.
My questions:
1. If we install 4 GB of RAM do I need to learn about doing anything
special like set PAE or AWE parameters or boot.ini parameters or
anything' Most of the MS links I saw on the list are for Windows 2000
Server, not Windows 2003 Server.
2. If we put in 4 GB of RAM, it seems to me that setting SQL to 2.5 GB
max and leaving the remainder for the OS and Abra Suite would work
until Abra Suite's DB is upgraded to SQL 2005, in which case I would
change it to 3 GB for SQL and the remaining 1 GB for the OS and IIS,
which is our most likely upgrade path. Does this make sense'
Right now SQL is set to dynamic memory allocation and I would like to
leave it this way if possible but I want to learn about preventing
future issues down the road related to virtual or physical memory,
especially to prevent paging, this server has to last a while!!
3. Any other forum threads or websites or advice would be most welcome.
Thank you for reading this,
Tom"tlyczko" <tlyczko@.gmail.com> wrote in message
news:1151518715.642430.142110@.x69g2000cwx.googlegroups.com...
> Hello,
> I'm relatively new to SQL Server, and have 'inherited' a SQL Server box
> with the following specs:
> Windows 2003 SP1, SQL Server 2000 SP 3 (or 3a?), single-processor
> license
> 2.8 GHz Xeon, 2 GB RAM, about 2 years old
> Great Plains 7.5 company DBs, about 8-9 users during the day, total DB
> size < 5 GB? I don't know exactly.
> Other apps are the OS, some Access MDBs accessed by Access front ends
> via Citrix, and an Abra Suite DB (FoxPro tables, eventually to be
> migrated to SQL Server, the Abra client is on Citrix servers.
> We have a monitoring system through our support consultant. This system
> tells us now and then that SQL has used up all its physical memory, but
> these alerts occur at odd times like midnight on a Sunday (to be fair,
> this is around the time when SQL does its maintenance, backup, etc), or
> 540 pm when the main GP users have all gone home and are not doing
> anything in GP. I don't know yet if SQL is paging a lot, but no
> specific end user complaints have appeared yet, so we have time to be
> pro-active.
> I've recently found some information on which PerfMon items to check,
> and will try these.
> My questions:
> 1. If we install 4 GB of RAM do I need to learn about doing anything
> special like set PAE or AWE parameters or boot.ini parameters or
> anything' Most of the MS links I saw on the list are for Windows 2000
> Server, not Windows 2003 Server.
No. In a small setup like this SQL Server will use < 2GB of ram (that's SQL
2000 Standard Ed.'s limit).
If you install more ram, the additional ram will be available for the other
applications, and they won't have to fight with SQL Server for memory.
SQL Server is designed to use up all the available memory for data cache,
which is appropriate only for a dedicated SQL Server. If you don't install
more ram, you should change SQL Server's max memory setting to leave enough
memory for the other applications to run..SQL Server will run just fine with
1G or 1.5G of ram. It will just use a smaller data cache and have to go to
disk more often.
David|||Thank you for responding...I put in two more short questions below.
David Browne wrote:
> No. In a small setup like this SQL Server will use < 2GB of ram (that's SQL
> 2000 Standard Ed.'s limit).
> If you install more ram, the additional ram will be available for the other
> applications, and they won't have to fight with SQL Server for memory.
Adding more RAM will not make any difference' Sigh. Thank you.
Does SQL Server 2005 Standard have the same limitation' One book I
read states that SQL Server 2005 Standard has no maximum RAM limit,
please correct me if I am wrong, so that seems like a good reason to
upgrade when we can do so, at which point it will only have SQL Server,
IIS, and the OS.
> SQL Server is designed to use up all the available memory for data cache,
> which is appropriate only for a dedicated SQL Server. If you don't install
> more ram, you should change SQL Server's max memory setting to leave enough
> memory for the other applications to run..SQL Server will run just fine with
> 1G or 1.5G of ram. It will just use a smaller data cache and have to go to
> disk more often.
Are there any *negative* consequences to SQL Server 2000 paging to disk
more often other than things possibly being "slower" and whatever wear
and tear could happen to the disk drives'
So far our server may not be paging very much at all, the alerts we
receive state that the server is using ~2% of virtual memory (paging).
Is any information available about judging how much RAM the OS (Windows
2003) should have available'
The pagefile BTW on this server I believe is 4 GB.
Thank you for your help so far.
Thank you, Tom

request advice on judging memory usage/allocation

Hello,
I'm relatively new to SQL Server, and have 'inherited' a SQL Server box
with the following specs:
Windows 2003 SP1, SQL Server 2000 SP 3 (or 3a?), single-processor
license
2.8 GHz Xeon, 2 GB RAM, about 2 years old
Great Plains 7.5 company DBs, about 8-9 users during the day, total DB
size < 5 GB? I don't know exactly.
Other apps are the OS, some Access MDBs accessed by Access front ends
via Citrix, and an Abra Suite DB (FoxPro tables, eventually to be
migrated to SQL Server, the Abra client is on Citrix servers.
We have a monitoring system through our support consultant. This system
tells us now and then that SQL has used up all its physical memory, but
these alerts occur at odd times like midnight on a Sunday (to be fair,
this is around the time when SQL does its maintenance, backup, etc), or
540 pm when the main GP users have all gone home and are not doing
anything in GP. I don't know yet if SQL is paging a lot, but no
specific end user complaints have appeared yet, so we have time to be
pro-active.
I've recently found some information on which PerfMon items to check,
and will try these.
My questions:
1. If we install 4 GB of RAM do I need to learn about doing anything
special like set PAE or AWE parameters or boot.ini parameters or
anything' Most of the MS links I saw on the list are for Windows 2000
Server, not Windows 2003 Server.
2. If we put in 4 GB of RAM, it seems to me that setting SQL to 2.5 GB
max and leaving the remainder for the OS and Abra Suite would work
until Abra Suite's DB is upgraded to SQL 2005, in which case I would
change it to 3 GB for SQL and the remaining 1 GB for the OS and IIS,
which is our most likely upgrade path. Does this make sense'
Right now SQL is set to dynamic memory allocation and I would like to
leave it this way if possible but I want to learn about preventing
future issues down the road related to virtual or physical memory,
especially to prevent paging, this server has to last a while!!
3. Any other forum threads or websites or advice would be most welcome.
Thank you for reading this,
Tom"tlyczko" <tlyczko@.gmail.com> wrote in message
news:1151518715.642430.142110@.x69g2000cwx.googlegroups.com...
> Hello,
> I'm relatively new to SQL Server, and have 'inherited' a SQL Server box
> with the following specs:
> Windows 2003 SP1, SQL Server 2000 SP 3 (or 3a?), single-processor
> license
> 2.8 GHz Xeon, 2 GB RAM, about 2 years old
> Great Plains 7.5 company DBs, about 8-9 users during the day, total DB
> size < 5 GB? I don't know exactly.
> Other apps are the OS, some Access MDBs accessed by Access front ends
> via Citrix, and an Abra Suite DB (FoxPro tables, eventually to be
> migrated to SQL Server, the Abra client is on Citrix servers.
> We have a monitoring system through our support consultant. This system
> tells us now and then that SQL has used up all its physical memory, but
> these alerts occur at odd times like midnight on a Sunday (to be fair,
> this is around the time when SQL does its maintenance, backup, etc), or
> 540 pm when the main GP users have all gone home and are not doing
> anything in GP. I don't know yet if SQL is paging a lot, but no
> specific end user complaints have appeared yet, so we have time to be
> pro-active.
> I've recently found some information on which PerfMon items to check,
> and will try these.
> My questions:
> 1. If we install 4 GB of RAM do I need to learn about doing anything
> special like set PAE or AWE parameters or boot.ini parameters or
> anything' Most of the MS links I saw on the list are for Windows 2000
> Server, not Windows 2003 Server.
No. In a small setup like this SQL Server will use < 2GB of ram (that's SQL
2000 Standard Ed.'s limit).
If you install more ram, the additional ram will be available for the other
applications, and they won't have to fight with SQL Server for memory.
SQL Server is designed to use up all the available memory for data cache,
which is appropriate only for a dedicated SQL Server. If you don't install
more ram, you should change SQL Server's max memory setting to leave enough
memory for the other applications to run..SQL Server will run just fine with
1G or 1.5G of ram. It will just use a smaller data cache and have to go to
disk more often.
David|||"tlyczko" <tlyczko@.gmail.com> wrote in message
news:1151518715.642430.142110@.x69g2000cwx.googlegroups.com...
> Hello,
> I'm relatively new to SQL Server, and have 'inherited' a SQL Server box
> with the following specs:
> Windows 2003 SP1, SQL Server 2000 SP 3 (or 3a?), single-processor
> license
> 2.8 GHz Xeon, 2 GB RAM, about 2 years old
> Great Plains 7.5 company DBs, about 8-9 users during the day, total DB
> size < 5 GB? I don't know exactly.
> Other apps are the OS, some Access MDBs accessed by Access front ends
> via Citrix, and an Abra Suite DB (FoxPro tables, eventually to be
> migrated to SQL Server, the Abra client is on Citrix servers.
> We have a monitoring system through our support consultant. This system
> tells us now and then that SQL has used up all its physical memory, but
> these alerts occur at odd times like midnight on a Sunday (to be fair,
> this is around the time when SQL does its maintenance, backup, etc), or
> 540 pm when the main GP users have all gone home and are not doing
> anything in GP. I don't know yet if SQL is paging a lot, but no
> specific end user complaints have appeared yet, so we have time to be
> pro-active.
> I've recently found some information on which PerfMon items to check,
> and will try these.
> My questions:
> 1. If we install 4 GB of RAM do I need to learn about doing anything
> special like set PAE or AWE parameters or boot.ini parameters or
> anything' Most of the MS links I saw on the list are for Windows 2000
> Server, not Windows 2003 Server.
No. In a small setup like this SQL Server will use < 2GB of ram (that's SQL
2000 Standard Ed.'s limit).
If you install more ram, the additional ram will be available for the other
applications, and they won't have to fight with SQL Server for memory.
SQL Server is designed to use up all the available memory for data cache,
which is appropriate only for a dedicated SQL Server. If you don't install
more ram, you should change SQL Server's max memory setting to leave enough
memory for the other applications to run..SQL Server will run just fine with
1G or 1.5G of ram. It will just use a smaller data cache and have to go to
disk more often.
David|||Thank you for responding...I put in two more short questions below.
David Browne wrote:

> No. In a small setup like this SQL Server will use < 2GB of ram (that's S
QL
> 2000 Standard Ed.'s limit).
> If you install more ram, the additional ram will be available for the othe
r
> applications, and they won't have to fight with SQL Server for memory.
Adding more RAM will not make any difference' Sigh. Thank you.
Does SQL Server 2005 Standard have the same limitation' One book I
read states that SQL Server 2005 Standard has no maximum RAM limit,
please correct me if I am wrong, so that seems like a good reason to
upgrade when we can do so, at which point it will only have SQL Server,
IIS, and the OS.

> SQL Server is designed to use up all the available memory for data cache,
> which is appropriate only for a dedicated SQL Server. If you don't instal
l
> more ram, you should change SQL Server's max memory setting to leave enoug
h
> memory for the other applications to run..SQL Server will run just fine wi
th
> 1G or 1.5G of ram. It will just use a smaller data cache and have to go t
o
> disk more often.
Are there any *negative* consequences to SQL Server 2000 paging to disk
more often other than things possibly being "slower" and whatever wear
and tear could happen to the disk drives'
So far our server may not be paging very much at all, the alerts we
receive state that the server is using ~2% of virtual memory (paging).
Is any information available about judging how much RAM the OS (Windows
2003) should have available'
The pagefile BTW on this server I believe is 4 GB.
Thank you for your help so far.
Thank you, Tom|||Thank you for responding...I put in two more short questions below.
David Browne wrote:

> No. In a small setup like this SQL Server will use < 2GB of ram (that's S
QL
> 2000 Standard Ed.'s limit).
> If you install more ram, the additional ram will be available for the othe
r
> applications, and they won't have to fight with SQL Server for memory.
Adding more RAM will not make any difference' Sigh. Thank you.
Does SQL Server 2005 Standard have the same limitation' One book I
read states that SQL Server 2005 Standard has no maximum RAM limit,
please correct me if I am wrong, so that seems like a good reason to
upgrade when we can do so, at which point it will only have SQL Server,
IIS, and the OS.

> SQL Server is designed to use up all the available memory for data cache,
> which is appropriate only for a dedicated SQL Server. If you don't instal
l
> more ram, you should change SQL Server's max memory setting to leave enoug
h
> memory for the other applications to run..SQL Server will run just fine wi
th
> 1G or 1.5G of ram. It will just use a smaller data cache and have to go t
o
> disk more often.
Are there any *negative* consequences to SQL Server 2000 paging to disk
more often other than things possibly being "slower" and whatever wear
and tear could happen to the disk drives'
So far our server may not be paging very much at all, the alerts we
receive state that the server is using ~2% of virtual memory (paging).
Is any information available about judging how much RAM the OS (Windows
2003) should have available'
The pagefile BTW on this server I believe is 4 GB.
Thank you for your help so far.
Thank you, Tom