Hi
Silly question - can I just confirm that it is not possible to do
backup database and backup log concurrently (on 2 separate
connections), even if the backup db doesn't truncate log.
If backup kicked off after the DB backup, the log seems to block
waiting for the backup db (to tape) to complete before the Backup Log
even starts.
Thanks in Advance
Stuart
No, it is not possible to do database backup and log backup concurrently in
SQL Server 2000. Here is the supporting statement from BOL (BACKUP LOG):
'The transaction log cannot be backed up during a full database backup or a
differential database backup. However, the transaction log can be backed up
while a file backup is running.'
"NonNB" wrote:
> Hi
> Silly question - can I just confirm that it is not possible to do
> backup database and backup log concurrently (on 2 separate
> connections), even if the backup db doesn't truncate log.
> If backup kicked off after the DB backup, the log seems to block
> waiting for the backup db (to tape) to complete before the Backup Log
> even starts.
> Thanks in Advance
> Stuart
>
|||Thanks. Any idea whether file backup implies a local SQL backup to DUMP
to a disk or meaning OS backup of files. Reason I ask is that a remote
(Veritas) tape SQL backup seems to be preventing our log shipping
working (which has otherwise worked for 3 years). It seemed to be more
"I/O" efficient going direct SQL -> tape, instead of SQL -> Dump ->
Tape but direct to tape is taking much longer than anticipated. The
DUMP usually finished quickly, so never really had a problem with LOG
and DB backups overlapping before.
Stuart
|||The term 'file backup' in my previous reply means backup of database files in
sql server. It is just another type of backup for databases in sql server.
For example, if a particular database has 2 database files, they can be
backed up separately. Generally, we use only following backups in sql server:
Database Backup (also called Full Backup), Differential Backup and Log
Backup.
Now a bit about your backup to tape. I understand that you have been backing
up the database directly to tape, and it is now taking more time. Perhaps,
the database size has increased over the time and hence it is taking more
time. What is the size of your database? When do you take the Full Backup?
How much time the Full Backup takes? Is lots of user activities going on in
your database all the time, or there is less user activity during the night
time?
"NonNB" wrote:
> Thanks. Any idea whether file backup implies a local SQL backup to DUMP
> to a disk or meaning OS backup of files. Reason I ask is that a remote
> (Veritas) tape SQL backup seems to be preventing our log shipping
> working (which has otherwise worked for 3 years). It seemed to be more
> "I/O" efficient going direct SQL -> tape, instead of SQL -> Dump ->
> Tape but direct to tape is taking much longer than anticipated. The
> DUMP usually finished quickly, so never really had a problem with LOG
> and DB backups overlapping before.
> Stuart
|||Found the issue - some clot plugged the 1 Ghz NIC into a PCI 100 slot
on the remote box with the tape drive - this geared the NIC down to
100Mbps and limited throughput to about 4MBps instead of 15-20MBps
Have confirmed that backup log is blocked (but does not fail) until the
tape BACKUP DATABASE has completed..
Thx again
Showing posts with label concurrently. Show all posts
Showing posts with label concurrently. Show all posts
Sunday, March 11, 2012
Backup Database and Backup Log concurrently
Hi
Silly question - can I just confirm that it is not possible to do
backup database and backup log concurrently (on 2 separate
connections), even if the backup db doesn't truncate log.
If backup kicked off after the DB backup, the log seems to block
waiting for the backup db (to tape) to complete before the Backup Log
even starts.
Thanks in Advance
StuartNo, it is not possible to do database backup and log backup concurrently in
SQL Server 2000. Here is the supporting statement from BOL (BACKUP LOG):
'The transaction log cannot be backed up during a full database backup or a
differential database backup. However, the transaction log can be backed up
while a file backup is running.'
"NonNB" wrote:
> Hi
> Silly question - can I just confirm that it is not possible to do
> backup database and backup log concurrently (on 2 separate
> connections), even if the backup db doesn't truncate log.
> If backup kicked off after the DB backup, the log seems to block
> waiting for the backup db (to tape) to complete before the Backup Log
> even starts.
> Thanks in Advance
> Stuart
>|||Thanks. Any idea whether file backup implies a local SQL backup to DUMP
to a disk or meaning OS backup of files. Reason I ask is that a remote
(Veritas) tape SQL backup seems to be preventing our log shipping
working (which has otherwise worked for 3 years). It seemed to be more
"I/O" efficient going direct SQL -> tape, instead of SQL -> Dump ->
Tape but direct to tape is taking much longer than anticipated. The
DUMP usually finished quickly, so never really had a problem with LOG
and DB backups overlapping before.
Stuart|||The term 'file backup' in my previous reply means backup of database files in
sql server. It is just another type of backup for databases in sql server.
For example, if a particular database has 2 database files, they can be
backed up separately. Generally, we use only following backups in sql server:
Database Backup (also called Full Backup), Differential Backup and Log
Backup.
Now a bit about your backup to tape. I understand that you have been backing
up the database directly to tape, and it is now taking more time. Perhaps,
the database size has increased over the time and hence it is taking more
time. What is the size of your database? When do you take the Full Backup?
How much time the Full Backup takes? Is lots of user activities going on in
your database all the time, or there is less user activity during the night
time?
"NonNB" wrote:
> Thanks. Any idea whether file backup implies a local SQL backup to DUMP
> to a disk or meaning OS backup of files. Reason I ask is that a remote
> (Veritas) tape SQL backup seems to be preventing our log shipping
> working (which has otherwise worked for 3 years). It seemed to be more
> "I/O" efficient going direct SQL -> tape, instead of SQL -> Dump ->
> Tape but direct to tape is taking much longer than anticipated. The
> DUMP usually finished quickly, so never really had a problem with LOG
> and DB backups overlapping before.
> Stuart|||Found the issue - some clot plugged the 1 Ghz NIC into a PCI 100 slot
on the remote box with the tape drive - this geared the NIC down to
100Mbps and limited throughput to about 4MBps instead of 15-20MBps
Have confirmed that backup log is blocked (but does not fail) until the
tape BACKUP DATABASE has completed..
Thx again
Silly question - can I just confirm that it is not possible to do
backup database and backup log concurrently (on 2 separate
connections), even if the backup db doesn't truncate log.
If backup kicked off after the DB backup, the log seems to block
waiting for the backup db (to tape) to complete before the Backup Log
even starts.
Thanks in Advance
StuartNo, it is not possible to do database backup and log backup concurrently in
SQL Server 2000. Here is the supporting statement from BOL (BACKUP LOG):
'The transaction log cannot be backed up during a full database backup or a
differential database backup. However, the transaction log can be backed up
while a file backup is running.'
"NonNB" wrote:
> Hi
> Silly question - can I just confirm that it is not possible to do
> backup database and backup log concurrently (on 2 separate
> connections), even if the backup db doesn't truncate log.
> If backup kicked off after the DB backup, the log seems to block
> waiting for the backup db (to tape) to complete before the Backup Log
> even starts.
> Thanks in Advance
> Stuart
>|||Thanks. Any idea whether file backup implies a local SQL backup to DUMP
to a disk or meaning OS backup of files. Reason I ask is that a remote
(Veritas) tape SQL backup seems to be preventing our log shipping
working (which has otherwise worked for 3 years). It seemed to be more
"I/O" efficient going direct SQL -> tape, instead of SQL -> Dump ->
Tape but direct to tape is taking much longer than anticipated. The
DUMP usually finished quickly, so never really had a problem with LOG
and DB backups overlapping before.
Stuart|||The term 'file backup' in my previous reply means backup of database files in
sql server. It is just another type of backup for databases in sql server.
For example, if a particular database has 2 database files, they can be
backed up separately. Generally, we use only following backups in sql server:
Database Backup (also called Full Backup), Differential Backup and Log
Backup.
Now a bit about your backup to tape. I understand that you have been backing
up the database directly to tape, and it is now taking more time. Perhaps,
the database size has increased over the time and hence it is taking more
time. What is the size of your database? When do you take the Full Backup?
How much time the Full Backup takes? Is lots of user activities going on in
your database all the time, or there is less user activity during the night
time?
"NonNB" wrote:
> Thanks. Any idea whether file backup implies a local SQL backup to DUMP
> to a disk or meaning OS backup of files. Reason I ask is that a remote
> (Veritas) tape SQL backup seems to be preventing our log shipping
> working (which has otherwise worked for 3 years). It seemed to be more
> "I/O" efficient going direct SQL -> tape, instead of SQL -> Dump ->
> Tape but direct to tape is taking much longer than anticipated. The
> DUMP usually finished quickly, so never really had a problem with LOG
> and DB backups overlapping before.
> Stuart|||Found the issue - some clot plugged the 1 Ghz NIC into a PCI 100 slot
on the remote box with the tape drive - this geared the NIC down to
100Mbps and limited throughput to about 4MBps instead of 15-20MBps
Have confirmed that backup log is blocked (but does not fail) until the
tape BACKUP DATABASE has completed..
Thx again
Backup Database and Backup Log concurrently
Hi
Silly question - can I just confirm that it is not possible to do
backup database and backup log concurrently (on 2 separate
connections), even if the backup db doesn't truncate log.
If backup kicked off after the DB backup, the log seems to block
waiting for the backup db (to tape) to complete before the Backup Log
even starts.
Thanks in Advance
StuartNo, it is not possible to do database backup and log backup concurrently in
SQL Server 2000. Here is the supporting statement from BOL (BACKUP LOG):
'The transaction log cannot be backed up during a full database backup or a
differential database backup. However, the transaction log can be backed up
while a file backup is running.'
"NonNB" wrote:
> Hi
> Silly question - can I just confirm that it is not possible to do
> backup database and backup log concurrently (on 2 separate
> connections), even if the backup db doesn't truncate log.
> If backup kicked off after the DB backup, the log seems to block
> waiting for the backup db (to tape) to complete before the Backup Log
> even starts.
> Thanks in Advance
> Stuart
>|||Thanks. Any idea whether file backup implies a local SQL backup to DUMP
to a disk or meaning OS backup of files. Reason I ask is that a remote
(Veritas) tape SQL backup seems to be preventing our log shipping
working (which has otherwise worked for 3 years). It seemed to be more
"I/O" efficient going direct SQL -> tape, instead of SQL -> Dump ->
Tape but direct to tape is taking much longer than anticipated. The
DUMP usually finished quickly, so never really had a problem with LOG
and DB backups overlapping before.
Stuart|||The term 'file backup' in my previous reply means backup of database files i
n
sql server. It is just another type of backup for databases in sql server.
For example, if a particular database has 2 database files, they can be
backed up separately. Generally, we use only following backups in sql server
:
Database Backup (also called Full Backup), Differential Backup and Log
Backup.
Now a bit about your backup to tape. I understand that you have been backing
up the database directly to tape, and it is now taking more time. Perhaps,
the database size has increased over the time and hence it is taking more
time. What is the size of your database? When do you take the Full Backup?
How much time the Full Backup takes? Is lots of user activities going on in
your database all the time, or there is less user activity during the night
time?
"NonNB" wrote:
> Thanks. Any idea whether file backup implies a local SQL backup to DUMP
> to a disk or meaning OS backup of files. Reason I ask is that a remote
> (Veritas) tape SQL backup seems to be preventing our log shipping
> working (which has otherwise worked for 3 years). It seemed to be more
> "I/O" efficient going direct SQL -> tape, instead of SQL -> Dump ->
> Tape but direct to tape is taking much longer than anticipated. The
> DUMP usually finished quickly, so never really had a problem with LOG
> and DB backups overlapping before.
> Stuart|||Found the issue - some clot plugged the 1 Ghz NIC into a PCI 100 slot
on the remote box with the tape drive - this geared the NIC down to
100Mbps and limited throughput to about 4MBps instead of 15-20MBps
Have confirmed that backup log is blocked (but does not fail) until the
tape BACKUP DATABASE has completed..
Thx again
Silly question - can I just confirm that it is not possible to do
backup database and backup log concurrently (on 2 separate
connections), even if the backup db doesn't truncate log.
If backup kicked off after the DB backup, the log seems to block
waiting for the backup db (to tape) to complete before the Backup Log
even starts.
Thanks in Advance
StuartNo, it is not possible to do database backup and log backup concurrently in
SQL Server 2000. Here is the supporting statement from BOL (BACKUP LOG):
'The transaction log cannot be backed up during a full database backup or a
differential database backup. However, the transaction log can be backed up
while a file backup is running.'
"NonNB" wrote:
> Hi
> Silly question - can I just confirm that it is not possible to do
> backup database and backup log concurrently (on 2 separate
> connections), even if the backup db doesn't truncate log.
> If backup kicked off after the DB backup, the log seems to block
> waiting for the backup db (to tape) to complete before the Backup Log
> even starts.
> Thanks in Advance
> Stuart
>|||Thanks. Any idea whether file backup implies a local SQL backup to DUMP
to a disk or meaning OS backup of files. Reason I ask is that a remote
(Veritas) tape SQL backup seems to be preventing our log shipping
working (which has otherwise worked for 3 years). It seemed to be more
"I/O" efficient going direct SQL -> tape, instead of SQL -> Dump ->
Tape but direct to tape is taking much longer than anticipated. The
DUMP usually finished quickly, so never really had a problem with LOG
and DB backups overlapping before.
Stuart|||The term 'file backup' in my previous reply means backup of database files i
n
sql server. It is just another type of backup for databases in sql server.
For example, if a particular database has 2 database files, they can be
backed up separately. Generally, we use only following backups in sql server
:
Database Backup (also called Full Backup), Differential Backup and Log
Backup.
Now a bit about your backup to tape. I understand that you have been backing
up the database directly to tape, and it is now taking more time. Perhaps,
the database size has increased over the time and hence it is taking more
time. What is the size of your database? When do you take the Full Backup?
How much time the Full Backup takes? Is lots of user activities going on in
your database all the time, or there is less user activity during the night
time?
"NonNB" wrote:
> Thanks. Any idea whether file backup implies a local SQL backup to DUMP
> to a disk or meaning OS backup of files. Reason I ask is that a remote
> (Veritas) tape SQL backup seems to be preventing our log shipping
> working (which has otherwise worked for 3 years). It seemed to be more
> "I/O" efficient going direct SQL -> tape, instead of SQL -> Dump ->
> Tape but direct to tape is taking much longer than anticipated. The
> DUMP usually finished quickly, so never really had a problem with LOG
> and DB backups overlapping before.
> Stuart|||Found the issue - some clot plugged the 1 Ghz NIC into a PCI 100 slot
on the remote box with the tape drive - this geared the NIC down to
100Mbps and limited throughput to about 4MBps instead of 15-20MBps
Have confirmed that backup log is blocked (but does not fail) until the
tape BACKUP DATABASE has completed..
Thx again
Thursday, March 8, 2012
Backup Behaviour During Full/Diff/Log Backups Running Concurrently
If log backups are started while a full backup is already running, what if
any effect occurs to the full backup? In SQL Server 2000 I think that all
log backups are paused if a full is already running. I can't find any MS
articles that explains explicitly what the interaction/behaviour or result is
if full/diff/log backup jobs for the same database should overlap. Thanx.
-CqlboyThe backup that started first will block other backups. for example if you
start the log backup and then database full backup(log backup still running)
the full database will be blocked on log backup to finish and vice
versa...also same behavior true for differential backup.
"Cqlboy" wrote:
> If log backups are started while a full backup is already running, what if
> any effect occurs to the full backup? In SQL Server 2000 I think that all
> log backups are paused if a full is already running. I can't find any MS
> articles that explains explicitly what the interaction/behaviour or result is
> if full/diff/log backup jobs for the same database should overlap. Thanx.
> -Cqlboy|||Thanx. I've heard that if you execute a DIFF while FULL is running it will
error out. This has been fixed in 2005. I'm more interested in finding out
what if a FULL can maintain transactional consistency if a LOG backup was
performed during execution of the FULL. Log backups truncate the inactive
portion of the log. A FULL uses the transaction log as part of it's strategy
to compile the FULL backup.
"harvinder" wrote:
> The backup that started first will block other backups. for example if you
> start the log backup and then database full backup(log backup still running)
> the full database will be blocked on log backup to finish and vice
> versa...also same behavior true for differential backup.
> "Cqlboy" wrote:
> > If log backups are started while a full backup is already running, what if
> > any effect occurs to the full backup? In SQL Server 2000 I think that all
> > log backups are paused if a full is already running. I can't find any MS
> > articles that explains explicitly what the interaction/behaviour or result is
> > if full/diff/log backup jobs for the same database should overlap. Thanx.
> > -Cqlboy
any effect occurs to the full backup? In SQL Server 2000 I think that all
log backups are paused if a full is already running. I can't find any MS
articles that explains explicitly what the interaction/behaviour or result is
if full/diff/log backup jobs for the same database should overlap. Thanx.
-CqlboyThe backup that started first will block other backups. for example if you
start the log backup and then database full backup(log backup still running)
the full database will be blocked on log backup to finish and vice
versa...also same behavior true for differential backup.
"Cqlboy" wrote:
> If log backups are started while a full backup is already running, what if
> any effect occurs to the full backup? In SQL Server 2000 I think that all
> log backups are paused if a full is already running. I can't find any MS
> articles that explains explicitly what the interaction/behaviour or result is
> if full/diff/log backup jobs for the same database should overlap. Thanx.
> -Cqlboy|||Thanx. I've heard that if you execute a DIFF while FULL is running it will
error out. This has been fixed in 2005. I'm more interested in finding out
what if a FULL can maintain transactional consistency if a LOG backup was
performed during execution of the FULL. Log backups truncate the inactive
portion of the log. A FULL uses the transaction log as part of it's strategy
to compile the FULL backup.
"harvinder" wrote:
> The backup that started first will block other backups. for example if you
> start the log backup and then database full backup(log backup still running)
> the full database will be blocked on log backup to finish and vice
> versa...also same behavior true for differential backup.
> "Cqlboy" wrote:
> > If log backups are started while a full backup is already running, what if
> > any effect occurs to the full backup? In SQL Server 2000 I think that all
> > log backups are paused if a full is already running. I can't find any MS
> > articles that explains explicitly what the interaction/behaviour or result is
> > if full/diff/log backup jobs for the same database should overlap. Thanx.
> > -Cqlboy
Backup Behaviour During Full/Diff/Log Backups Running Concurrently
If log backups are started while a full backup is already running, what if
any effect occurs to the full backup? In SQL Server 2000 I think that all
log backups are paused if a full is already running. I can't find any MS
articles that explains explicitly what the interaction/behaviour or result i
s
if full/diff/log backup jobs for the same database should overlap. Thanx.
-CqlboyThe backup that started first will block other backups. for example if you
start the log backup and then database full backup(log backup still running)
the full database will be blocked on log backup to finish and vice
versa...also same behavior true for differential backup.
"Cqlboy" wrote:
> If log backups are started while a full backup is already running, what if
> any effect occurs to the full backup? In SQL Server 2000 I think that all
> log backups are paused if a full is already running. I can't find any MS
> articles that explains explicitly what the interaction/behaviour or result
is
> if full/diff/log backup jobs for the same database should overlap. Thanx.
> -Cqlboy
any effect occurs to the full backup? In SQL Server 2000 I think that all
log backups are paused if a full is already running. I can't find any MS
articles that explains explicitly what the interaction/behaviour or result i
s
if full/diff/log backup jobs for the same database should overlap. Thanx.
-CqlboyThe backup that started first will block other backups. for example if you
start the log backup and then database full backup(log backup still running)
the full database will be blocked on log backup to finish and vice
versa...also same behavior true for differential backup.
"Cqlboy" wrote:
> If log backups are started while a full backup is already running, what if
> any effect occurs to the full backup? In SQL Server 2000 I think that all
> log backups are paused if a full is already running. I can't find any MS
> articles that explains explicitly what the interaction/behaviour or result
is
> if full/diff/log backup jobs for the same database should overlap. Thanx.
> -Cqlboy
Backup Behaviour During Full/Diff/Log Backups Running Concurrently
If log backups are started while a full backup is already running, what if
any effect occurs to the full backup? In SQL Server 2000 I think that all
log backups are paused if a full is already running. I can't find any MS
articles that explains explicitly what the interaction/behaviour or result is
if full/diff/log backup jobs for the same database should overlap. Thanx.
-Cqlboy
The backup that started first will block other backups. for example if you
start the log backup and then database full backup(log backup still running)
the full database will be blocked on log backup to finish and vice
versa...also same behavior true for differential backup.
"Cqlboy" wrote:
> If log backups are started while a full backup is already running, what if
> any effect occurs to the full backup? In SQL Server 2000 I think that all
> log backups are paused if a full is already running. I can't find any MS
> articles that explains explicitly what the interaction/behaviour or result is
> if full/diff/log backup jobs for the same database should overlap. Thanx.
> -Cqlboy
any effect occurs to the full backup? In SQL Server 2000 I think that all
log backups are paused if a full is already running. I can't find any MS
articles that explains explicitly what the interaction/behaviour or result is
if full/diff/log backup jobs for the same database should overlap. Thanx.
-Cqlboy
The backup that started first will block other backups. for example if you
start the log backup and then database full backup(log backup still running)
the full database will be blocked on log backup to finish and vice
versa...also same behavior true for differential backup.
"Cqlboy" wrote:
> If log backups are started while a full backup is already running, what if
> any effect occurs to the full backup? In SQL Server 2000 I think that all
> log backups are paused if a full is already running. I can't find any MS
> articles that explains explicitly what the interaction/behaviour or result is
> if full/diff/log backup jobs for the same database should overlap. Thanx.
> -Cqlboy
Subscribe to:
Posts (Atom)