My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of any
other place to look. Any Ideas? Thanks.
Run DBCC OPENTRAN against the DB. It should point you to the SPID of an
open transaction. You may have to kill the SPID.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
other place to look. Any Ideas? Thanks.
|||Do you have another server you can copy the databases and test the backups
there. Judging by the size, you can even test it in a laptop.
Are you backing up locally or to a network share?
"AkAlan" wrote:
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what are
> really bothersome. They used to take less than a minute but now are going on
> 40 minutes. The databases are not that large. One is 108MB and the other is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> other place to look. Any Ideas? Thanks.
|||Hi,
Just take a look into the old transaction log backup files and new
ones..Probaly you will be having huge Transaction log backup files.
As well as monitor the server processes using SP_WHO and see if there is any
blocks. Also ensure that 2 backups are not running in parallel.
Thanks
Hari
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what
> are
> really bothersome. They used to take less than a minute but now are going
> on
> 40 minutes. The databases are not that large. One is 108MB and the other
> is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
> any
> other place to look. Any Ideas? Thanks.
|||Thanks for everyones help. I do have a development copy on another server and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =
N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:
> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>
>
|||Thanks Tibor, The two backups running are from different databases, is that
an issue?
"Tibor Karaszi" wrote:
> In 2000 and earlier, a database backup will block a transaction log backup (and vice versa).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
>
|||Why are you using NO_TRUNCATE? This will cause your log to grow.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
Thanks for everyones help. I do have a development copy on another server
and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =
N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:
> In addition tot he other posts, please show us your BACKUP LOG command. (I
> just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>
>
Showing posts with label tlogs. Show all posts
Showing posts with label tlogs. Show all posts
Thursday, March 8, 2012
Backups taking progressively longer
My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of an
y
other place to look. Any Ideas? Thanks.Run DBCC OPENTRAN against the DB. It should point you to the SPID of an
open transaction. You may have to kill the SPID.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
other place to look. Any Ideas? Thanks.|||Do you have another server you can copy the databases and test the backups
there. Judging by the size, you can even test it in a laptop.
Are you backing up locally or to a network share?
"AkAlan" wrote:
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what a
re
> really bothersome. They used to take less than a minute but now are going
on
> 40 minutes. The databases are not that large. One is 108MB and the other i
s
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
> other place to look. Any Ideas? Thanks.|||Hi,
Just take a look into the old transaction log backup files and new
ones..Probaly you will be having huge Transaction log backup files.
As well as monitor the server processes using SP_WHO and see if there is any
blocks. Also ensure that 2 backups are not running in parallel.
Thanks
Hari
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what
> are
> really bothersome. They used to take less than a minute but now are going
> on
> 40 minutes. The databases are not that large. One is 108MB and the other
> is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
> any
> other place to look. Any Ideas? Thanks.|||In addition tot he other posts, please show us your BACKUP LOG command. (I j
ust want to verify that
don't use NO_TRUNCATE or COPY_ONLY option for these.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what a
re
> really bothersome. They used to take less than a minute but now are going
on
> 40 minutes. The databases are not that large. One is 108MB and the other i
s
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
> other place to look. Any Ideas? Thanks.|||Thanks for everyones help. I do have a development copy on another server an
d
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =
N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:
> In addition tot he other posts, please show us your BACKUP LOG command. (I
just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>
>|||In 2000 and earlier, a database backup will block a transaction log backup (
and vice versa).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...[vbcol=seagreen]
> Thanks for everyones help. I do have a development copy on another server
and
> will give that a try today. There are no Running Transactions when I run t
he
> DBCC OPENTRAN . There are occasions of parallel backups running, I start o
ne
> database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedu
le
> at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> Here is the command I use to run the backup on one database.
> BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =
> N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>
> I'm going to research defragmentation today and see what is going on with
> that. I haven't run any maintenance on the indexes since converting from a
n
> mdb 10 months ago. I'm wearing both an administrator and developer hat and
> have been slacking in the admin department. Thanks again for all the help.
>
> "Tibor Karaszi" wrote:
>|||Thanks Tibor, The two backups running are from different databases, is that
an issue?
"Tibor Karaszi" wrote:
> In 2000 and earlier, a database backup will block a transaction log backup
(and vice versa).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
>|||That should not be an issue, blocking only occurs on the same database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:39CED623-D1D8-495B-A79A-34EF26510754@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor, The two backups running are from different databases, is tha
t
> an issue?
> "Tibor Karaszi" wrote:
>|||Why are you using NO_TRUNCATE? This will cause your log to grow.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
Thanks for everyones help. I do have a development copy on another server
and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =
N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:
> In addition tot he other posts, please show us your BACKUP LOG command. (I
> just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>
>
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of an
y
other place to look. Any Ideas? Thanks.Run DBCC OPENTRAN against the DB. It should point you to the SPID of an
open transaction. You may have to kill the SPID.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
other place to look. Any Ideas? Thanks.|||Do you have another server you can copy the databases and test the backups
there. Judging by the size, you can even test it in a laptop.
Are you backing up locally or to a network share?
"AkAlan" wrote:
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what a
re
> really bothersome. They used to take less than a minute but now are going
on
> 40 minutes. The databases are not that large. One is 108MB and the other i
s
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
> other place to look. Any Ideas? Thanks.|||Hi,
Just take a look into the old transaction log backup files and new
ones..Probaly you will be having huge Transaction log backup files.
As well as monitor the server processes using SP_WHO and see if there is any
blocks. Also ensure that 2 backups are not running in parallel.
Thanks
Hari
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what
> are
> really bothersome. They used to take less than a minute but now are going
> on
> 40 minutes. The databases are not that large. One is 108MB and the other
> is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
> any
> other place to look. Any Ideas? Thanks.|||In addition tot he other posts, please show us your BACKUP LOG command. (I j
ust want to verify that
don't use NO_TRUNCATE or COPY_ONLY option for these.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what a
re
> really bothersome. They used to take less than a minute but now are going
on
> 40 minutes. The databases are not that large. One is 108MB and the other i
s
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
> other place to look. Any Ideas? Thanks.|||Thanks for everyones help. I do have a development copy on another server an
d
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =
N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:
> In addition tot he other posts, please show us your BACKUP LOG command. (I
just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>
>|||In 2000 and earlier, a database backup will block a transaction log backup (
and vice versa).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...[vbcol=seagreen]
> Thanks for everyones help. I do have a development copy on another server
and
> will give that a try today. There are no Running Transactions when I run t
he
> DBCC OPENTRAN . There are occasions of parallel backups running, I start o
ne
> database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedu
le
> at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> Here is the command I use to run the backup on one database.
> BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =
> N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>
> I'm going to research defragmentation today and see what is going on with
> that. I haven't run any maintenance on the indexes since converting from a
n
> mdb 10 months ago. I'm wearing both an administrator and developer hat and
> have been slacking in the admin department. Thanks again for all the help.
>
> "Tibor Karaszi" wrote:
>|||Thanks Tibor, The two backups running are from different databases, is that
an issue?
"Tibor Karaszi" wrote:
> In 2000 and earlier, a database backup will block a transaction log backup
(and vice versa).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
>|||That should not be an issue, blocking only occurs on the same database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:39CED623-D1D8-495B-A79A-34EF26510754@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor, The two backups running are from different databases, is tha
t
> an issue?
> "Tibor Karaszi" wrote:
>|||Why are you using NO_TRUNCATE? This will cause your log to grow.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
Thanks for everyones help. I do have a development copy on another server
and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =
N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:
> In addition tot he other posts, please show us your BACKUP LOG command. (I
> just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>
>
Backups taking progressively longer
My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of any
other place to look. Any Ideas? Thanks.Run DBCC OPENTRAN against the DB. It should point you to the SPID of an
open transaction. You may have to kill the SPID.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
other place to look. Any Ideas? Thanks.|||Do you have another server you can copy the databases and test the backups
there. Judging by the size, you can even test it in a laptop.
Are you backing up locally or to a network share?
"AkAlan" wrote:
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what are
> really bothersome. They used to take less than a minute but now are going on
> 40 minutes. The databases are not that large. One is 108MB and the other is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> other place to look. Any Ideas? Thanks.|||Hi,
Just take a look into the old transaction log backup files and new
ones..Probaly you will be having huge Transaction log backup files.
As well as monitor the server processes using SP_WHO and see if there is any
blocks. Also ensure that 2 backups are not running in parallel.
Thanks
Hari
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what
> are
> really bothersome. They used to take less than a minute but now are going
> on
> 40 minutes. The databases are not that large. One is 108MB and the other
> is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
> any
> other place to look. Any Ideas? Thanks.|||In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify that
don't use NO_TRUNCATE or COPY_ONLY option for these.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what are
> really bothersome. They used to take less than a minute but now are going on
> 40 minutes. The databases are not that large. One is 108MB and the other is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> other place to look. Any Ideas? Thanks.|||Thanks for everyones help. I do have a development copy on another server and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:
> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> > My Backups are taking progressively longer to complete. I run Full once a
> > week, Differential once a night and TLogs every hour. The TLogs are what are
> > really bothersome. They used to take less than a minute but now are going on
> > 40 minutes. The databases are not that large. One is 108MB and the other is
> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> > other place to look. Any Ideas? Thanks.
>
>|||In 2000 and earlier, a database backup will block a transaction log backup (and vice versa).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
> Thanks for everyones help. I do have a development copy on another server and
> will give that a try today. There are no Running Transactions when I run the
> DBCC OPENTRAN . There are occasions of parallel backups running, I start one
> database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
> at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> Here is the command I use to run the backup on one database.
> BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME => N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>
> I'm going to research defragmentation today and see what is going on with
> that. I haven't run any maintenance on the indexes since converting from an
> mdb 10 months ago. I'm wearing both an administrator and developer hat and
> have been slacking in the admin department. Thanks again for all the help.
>
> "Tibor Karaszi" wrote:
>> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify
>> that
>> don't use NO_TRUNCATE or COPY_ONLY option for these.)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>> > My Backups are taking progressively longer to complete. I run Full once a
>> > week, Differential once a night and TLogs every hour. The TLogs are what are
>> > really bothersome. They used to take less than a minute but now are going on
>> > 40 minutes. The databases are not that large. One is 108MB and the other is
>> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
>> > other place to look. Any Ideas? Thanks.
>>|||Thanks Tibor, The two backups running are from different databases, is that
an issue?
"Tibor Karaszi" wrote:
> In 2000 and earlier, a database backup will block a transaction log backup (and vice versa).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
> > Thanks for everyones help. I do have a development copy on another server and
> > will give that a try today. There are no Running Transactions when I run the
> > DBCC OPENTRAN . There are occasions of parallel backups running, I start one
> > database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
> > at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> > Here is the command I use to run the backup on one database.
> > BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME => > N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
> >
> >
> > I'm going to research defragmentation today and see what is going on with
> > that. I haven't run any maintenance on the indexes since converting from an
> > mdb 10 months ago. I'm wearing both an administrator and developer hat and
> > have been slacking in the admin department. Thanks again for all the help.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify
> >> that
> >> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> >> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> >> > My Backups are taking progressively longer to complete. I run Full once a
> >> > week, Differential once a night and TLogs every hour. The TLogs are what are
> >> > really bothersome. They used to take less than a minute but now are going on
> >> > 40 minutes. The databases are not that large. One is 108MB and the other is
> >> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> >> > other place to look. Any Ideas? Thanks.
> >>
> >>
> >>
>|||That should not be an issue, blocking only occurs on the same database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:39CED623-D1D8-495B-A79A-34EF26510754@.microsoft.com...
> Thanks Tibor, The two backups running are from different databases, is that
> an issue?
> "Tibor Karaszi" wrote:
>> In 2000 and earlier, a database backup will block a transaction log backup (and vice versa).
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
>> > Thanks for everyones help. I do have a development copy on another server and
>> > will give that a try today. There are no Running Transactions when I run the
>> > DBCC OPENTRAN . There are occasions of parallel backups running, I start one
>> > database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
>> > at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
>> > Here is the command I use to run the backup on one database.
>> > BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
>> > Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =>> > N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>> >
>> >
>> > I'm going to research defragmentation today and see what is going on with
>> > that. I haven't run any maintenance on the indexes since converting from an
>> > mdb 10 months ago. I'm wearing both an administrator and developer hat and
>> > have been slacking in the admin department. Thanks again for all the help.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify
>> >> that
>> >> don't use NO_TRUNCATE or COPY_ONLY option for these.)
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> >> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>> >> > My Backups are taking progressively longer to complete. I run Full once a
>> >> > week, Differential once a night and TLogs every hour. The TLogs are what are
>> >> > really bothersome. They used to take less than a minute but now are going on
>> >> > 40 minutes. The databases are not that large. One is 108MB and the other is
>> >> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
>> >> > other place to look. Any Ideas? Thanks.
>> >>
>> >>
>> >>
>>|||Why are you using NO_TRUNCATE? This will cause your log to grow.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
Thanks for everyones help. I do have a development copy on another server
and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:
> In addition tot he other posts, please show us your BACKUP LOG command. (I
> just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> > My Backups are taking progressively longer to complete. I run Full once
> > a
> > week, Differential once a night and TLogs every hour. The TLogs are what
> > are
> > really bothersome. They used to take less than a minute but now are
> > going on
> > 40 minutes. The databases are not that large. One is 108MB and the other
> > is
> > 80. I am lookng at defragmenting and Re-Indexing options but not aware
> > of any
> > other place to look. Any Ideas? Thanks.
>
>|||Darn... The reason I asked for the BACKUP command in the first place was to spot if there is a
NO_TRUNCATE or COPY_ONLY option in there. So the command was posted but I didn't see the option when
reading the command. Time for a visit with the eye doctor methinks.
NO_TRUNCATE is the reason the log backup takes progressively longer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23VI1JrdCHHA.4832@.TK2MSFTNGP06.phx.gbl...
> Why are you using NO_TRUNCATE? This will cause your log to grow.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
> Thanks for everyones help. I do have a development copy on another server
> and
> will give that a try today. There are no Running Transactions when I run the
> DBCC OPENTRAN . There are occasions of parallel backups running, I start one
> database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
> at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> Here is the command I use to run the backup on one database.
> BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME => N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>
> I'm going to research defragmentation today and see what is going on with
> that. I haven't run any maintenance on the indexes since converting from an
> mdb 10 months ago. I'm wearing both an administrator and developer hat and
> have been slacking in the admin department. Thanks again for all the help.
>
> "Tibor Karaszi" wrote:
>> In addition tot he other posts, please show us your BACKUP LOG command. (I
>> just want to verify that
>> don't use NO_TRUNCATE or COPY_ONLY option for these.)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>> > My Backups are taking progressively longer to complete. I run Full once
>> > a
>> > week, Differential once a night and TLogs every hour. The TLogs are what
>> > are
>> > really bothersome. They used to take less than a minute but now are
>> > going on
>> > 40 minutes. The databases are not that large. One is 108MB and the other
>> > is
>> > 80. I am lookng at defragmenting and Re-Indexing options but not aware
>> > of any
>> > other place to look. Any Ideas? Thanks.
>>
>
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of any
other place to look. Any Ideas? Thanks.Run DBCC OPENTRAN against the DB. It should point you to the SPID of an
open transaction. You may have to kill the SPID.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
other place to look. Any Ideas? Thanks.|||Do you have another server you can copy the databases and test the backups
there. Judging by the size, you can even test it in a laptop.
Are you backing up locally or to a network share?
"AkAlan" wrote:
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what are
> really bothersome. They used to take less than a minute but now are going on
> 40 minutes. The databases are not that large. One is 108MB and the other is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> other place to look. Any Ideas? Thanks.|||Hi,
Just take a look into the old transaction log backup files and new
ones..Probaly you will be having huge Transaction log backup files.
As well as monitor the server processes using SP_WHO and see if there is any
blocks. Also ensure that 2 backups are not running in parallel.
Thanks
Hari
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what
> are
> really bothersome. They used to take less than a minute but now are going
> on
> 40 minutes. The databases are not that large. One is 108MB and the other
> is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
> any
> other place to look. Any Ideas? Thanks.|||In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify that
don't use NO_TRUNCATE or COPY_ONLY option for these.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what are
> really bothersome. They used to take less than a minute but now are going on
> 40 minutes. The databases are not that large. One is 108MB and the other is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> other place to look. Any Ideas? Thanks.|||Thanks for everyones help. I do have a development copy on another server and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:
> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> > My Backups are taking progressively longer to complete. I run Full once a
> > week, Differential once a night and TLogs every hour. The TLogs are what are
> > really bothersome. They used to take less than a minute but now are going on
> > 40 minutes. The databases are not that large. One is 108MB and the other is
> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> > other place to look. Any Ideas? Thanks.
>
>|||In 2000 and earlier, a database backup will block a transaction log backup (and vice versa).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
> Thanks for everyones help. I do have a development copy on another server and
> will give that a try today. There are no Running Transactions when I run the
> DBCC OPENTRAN . There are occasions of parallel backups running, I start one
> database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
> at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> Here is the command I use to run the backup on one database.
> BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME => N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>
> I'm going to research defragmentation today and see what is going on with
> that. I haven't run any maintenance on the indexes since converting from an
> mdb 10 months ago. I'm wearing both an administrator and developer hat and
> have been slacking in the admin department. Thanks again for all the help.
>
> "Tibor Karaszi" wrote:
>> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify
>> that
>> don't use NO_TRUNCATE or COPY_ONLY option for these.)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>> > My Backups are taking progressively longer to complete. I run Full once a
>> > week, Differential once a night and TLogs every hour. The TLogs are what are
>> > really bothersome. They used to take less than a minute but now are going on
>> > 40 minutes. The databases are not that large. One is 108MB and the other is
>> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
>> > other place to look. Any Ideas? Thanks.
>>|||Thanks Tibor, The two backups running are from different databases, is that
an issue?
"Tibor Karaszi" wrote:
> In 2000 and earlier, a database backup will block a transaction log backup (and vice versa).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
> > Thanks for everyones help. I do have a development copy on another server and
> > will give that a try today. There are no Running Transactions when I run the
> > DBCC OPENTRAN . There are occasions of parallel backups running, I start one
> > database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
> > at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> > Here is the command I use to run the backup on one database.
> > BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME => > N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
> >
> >
> > I'm going to research defragmentation today and see what is going on with
> > that. I haven't run any maintenance on the indexes since converting from an
> > mdb 10 months ago. I'm wearing both an administrator and developer hat and
> > have been slacking in the admin department. Thanks again for all the help.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify
> >> that
> >> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> >> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> >> > My Backups are taking progressively longer to complete. I run Full once a
> >> > week, Differential once a night and TLogs every hour. The TLogs are what are
> >> > really bothersome. They used to take less than a minute but now are going on
> >> > 40 minutes. The databases are not that large. One is 108MB and the other is
> >> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> >> > other place to look. Any Ideas? Thanks.
> >>
> >>
> >>
>|||That should not be an issue, blocking only occurs on the same database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:39CED623-D1D8-495B-A79A-34EF26510754@.microsoft.com...
> Thanks Tibor, The two backups running are from different databases, is that
> an issue?
> "Tibor Karaszi" wrote:
>> In 2000 and earlier, a database backup will block a transaction log backup (and vice versa).
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
>> > Thanks for everyones help. I do have a development copy on another server and
>> > will give that a try today. There are no Running Transactions when I run the
>> > DBCC OPENTRAN . There are occasions of parallel backups running, I start one
>> > database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
>> > at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
>> > Here is the command I use to run the backup on one database.
>> > BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
>> > Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =>> > N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>> >
>> >
>> > I'm going to research defragmentation today and see what is going on with
>> > that. I haven't run any maintenance on the indexes since converting from an
>> > mdb 10 months ago. I'm wearing both an administrator and developer hat and
>> > have been slacking in the admin department. Thanks again for all the help.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify
>> >> that
>> >> don't use NO_TRUNCATE or COPY_ONLY option for these.)
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> >> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>> >> > My Backups are taking progressively longer to complete. I run Full once a
>> >> > week, Differential once a night and TLogs every hour. The TLogs are what are
>> >> > really bothersome. They used to take less than a minute but now are going on
>> >> > 40 minutes. The databases are not that large. One is 108MB and the other is
>> >> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
>> >> > other place to look. Any Ideas? Thanks.
>> >>
>> >>
>> >>
>>|||Why are you using NO_TRUNCATE? This will cause your log to grow.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
Thanks for everyones help. I do have a development copy on another server
and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:
> In addition tot he other posts, please show us your BACKUP LOG command. (I
> just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> > My Backups are taking progressively longer to complete. I run Full once
> > a
> > week, Differential once a night and TLogs every hour. The TLogs are what
> > are
> > really bothersome. They used to take less than a minute but now are
> > going on
> > 40 minutes. The databases are not that large. One is 108MB and the other
> > is
> > 80. I am lookng at defragmenting and Re-Indexing options but not aware
> > of any
> > other place to look. Any Ideas? Thanks.
>
>|||Darn... The reason I asked for the BACKUP command in the first place was to spot if there is a
NO_TRUNCATE or COPY_ONLY option in there. So the command was posted but I didn't see the option when
reading the command. Time for a visit with the eye doctor methinks.
NO_TRUNCATE is the reason the log backup takes progressively longer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23VI1JrdCHHA.4832@.TK2MSFTNGP06.phx.gbl...
> Why are you using NO_TRUNCATE? This will cause your log to grow.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
> Thanks for everyones help. I do have a development copy on another server
> and
> will give that a try today. There are no Running Transactions when I run the
> DBCC OPENTRAN . There are occasions of parallel backups running, I start one
> database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
> at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> Here is the command I use to run the backup on one database.
> BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME => N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>
> I'm going to research defragmentation today and see what is going on with
> that. I haven't run any maintenance on the indexes since converting from an
> mdb 10 months ago. I'm wearing both an administrator and developer hat and
> have been slacking in the admin department. Thanks again for all the help.
>
> "Tibor Karaszi" wrote:
>> In addition tot he other posts, please show us your BACKUP LOG command. (I
>> just want to verify that
>> don't use NO_TRUNCATE or COPY_ONLY option for these.)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>> > My Backups are taking progressively longer to complete. I run Full once
>> > a
>> > week, Differential once a night and TLogs every hour. The TLogs are what
>> > are
>> > really bothersome. They used to take less than a minute but now are
>> > going on
>> > 40 minutes. The databases are not that large. One is 108MB and the other
>> > is
>> > 80. I am lookng at defragmenting and Re-Indexing options but not aware
>> > of any
>> > other place to look. Any Ideas? Thanks.
>>
>
Sunday, February 19, 2012
Backup/Restore scenario
Hi,
I'm confused about the role of tLogs in a restore scenario. Imagine the
following:
Database running 24/7 with a lot of user activity. All backups at midnight
Fri - Full backup
Mon - tLog backup
Tue - tLog backup
Wed - tLog backup
Thurs - tLog backup
Imagine the server blows up one Thursday morning.
Q1. If I restore the full backup from the previous Friday, and then the
tLogs for Mon, Tue, Wed, does that mean I've got everything except the last
few user updates early Thursday morning?
Q2. What if the developers had added a new table on Tuesday? Would this new
table exist in the restored version?
--
Gerry HickmanQ1 - When you apply the transaction log backups you will have all the
modifications that have happened up to the point of the most recent
transaction log that you applied.
Q2 - Yes, the table that is created on Tuesday will be capured within
Tuesday night's t-log backup. It will be "created" when you restore that
log backup.
If you have "lots" of user activity as you say you might want to issue
transaction log backups more often than once per day.
--
Keith Kratochvil
"Gerry Hickman" <gerry1uk@.netscape.net> wrote in message
news:u6ZvvygPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm confused about the role of tLogs in a restore scenario. Imagine the
> following:
> Database running 24/7 with a lot of user activity. All backups at midnight
> Fri - Full backup
> Mon - tLog backup
> Tue - tLog backup
> Wed - tLog backup
> Thurs - tLog backup
> Imagine the server blows up one Thursday morning.
> Q1. If I restore the full backup from the previous Friday, and then the
> tLogs for Mon, Tue, Wed, does that mean I've got everything except the
> last
> few user updates early Thursday morning?
> Q2. What if the developers had added a new table on Tuesday? Would this
> new
> table exist in the restored version?
> --
> Gerry Hickman
>|||Hi Keith,
Thanks, this is very helpful and this was how I originally understood it
would work, but recently I wasn't sure. It's interesting the new table
gets carried over.
I have another question now!
If the tLogs are storing all the changes since the last backup, do they
get "emptied" next time you do a full backup?
As I understand it, you can choose to "truncate" the log as part of the
backup procedure, but I currently DON'T have this checked, so how do I
know my full backup really is "full" and will my tLogs keep growing
forever? All my backup settings are on the defaults. Can you advise the
correct backup settings to use for our simple setup?
(Point taken about doing full backup each day instead of each week)
Keith Kratochvil wrote:
> Q1 - When you apply the transaction log backups you will have all the
> modifications that have happened up to the point of the most recent
> transaction log that you applied.
> Q2 - Yes, the table that is created on Tuesday will be capured within
> Tuesday night's t-log backup. It will be "created" when you restore that
> log backup.
>
> If you have "lots" of user activity as you say you might want to issue
> transaction log backups more often than once per day.
>
Gerry Hickman (London UK)|||Gerry Hickman wrote:
> Hi Keith,
> Thanks, this is very helpful and this was how I originally understood it
> would work, but recently I wasn't sure. It's interesting the new table
> gets carried over.
> I have another question now!
> If the tLogs are storing all the changes since the last backup, do they
> get "emptied" next time you do a full backup?
> As I understand it, you can choose to "truncate" the log as part of the
> backup procedure, but I currently DON'T have this checked, so how do I
> know my full backup really is "full" and will my tLogs keep growing
> forever? All my backup settings are on the defaults. Can you advise the
> correct backup settings to use for our simple setup?
> (Point taken about doing full backup each day instead of each week)
> Keith Kratochvil wrote:
>> Q1 - When you apply the transaction log backups you will have all the
>> modifications that have happened up to the point of the most recent
>> transaction log that you applied.
>> Q2 - Yes, the table that is created on Tuesday will be capured within
>> Tuesday night's t-log backup. It will be "created" when you restore
>> that log backup.
>>
>> If you have "lots" of user activity as you say you might want to issue
>> transaction log backups more often than once per day.
>
Hi Gary
The FULL backup will not do anything to the logfiles. It's only a log
backup that will "touch" the logfile (Actually a FULL backup will take a
little part of the logfile, but that's only what it needs to be able to
perform a RESTORE).
When you do a log backup, it will mark the transactions that it has
backed up and the space can then be reused (TRUNCATE). Now the space can
be reused by new transactions, so your physical logfile doesn't need to
grow to contain the transactions.
Try to look up "Transaction Log Backups" in Books On Line. That chapter
gives a fairly good description of how it works.
Regards
Steen
I'm confused about the role of tLogs in a restore scenario. Imagine the
following:
Database running 24/7 with a lot of user activity. All backups at midnight
Fri - Full backup
Mon - tLog backup
Tue - tLog backup
Wed - tLog backup
Thurs - tLog backup
Imagine the server blows up one Thursday morning.
Q1. If I restore the full backup from the previous Friday, and then the
tLogs for Mon, Tue, Wed, does that mean I've got everything except the last
few user updates early Thursday morning?
Q2. What if the developers had added a new table on Tuesday? Would this new
table exist in the restored version?
--
Gerry HickmanQ1 - When you apply the transaction log backups you will have all the
modifications that have happened up to the point of the most recent
transaction log that you applied.
Q2 - Yes, the table that is created on Tuesday will be capured within
Tuesday night's t-log backup. It will be "created" when you restore that
log backup.
If you have "lots" of user activity as you say you might want to issue
transaction log backups more often than once per day.
--
Keith Kratochvil
"Gerry Hickman" <gerry1uk@.netscape.net> wrote in message
news:u6ZvvygPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm confused about the role of tLogs in a restore scenario. Imagine the
> following:
> Database running 24/7 with a lot of user activity. All backups at midnight
> Fri - Full backup
> Mon - tLog backup
> Tue - tLog backup
> Wed - tLog backup
> Thurs - tLog backup
> Imagine the server blows up one Thursday morning.
> Q1. If I restore the full backup from the previous Friday, and then the
> tLogs for Mon, Tue, Wed, does that mean I've got everything except the
> last
> few user updates early Thursday morning?
> Q2. What if the developers had added a new table on Tuesday? Would this
> new
> table exist in the restored version?
> --
> Gerry Hickman
>|||Hi Keith,
Thanks, this is very helpful and this was how I originally understood it
would work, but recently I wasn't sure. It's interesting the new table
gets carried over.
I have another question now!
If the tLogs are storing all the changes since the last backup, do they
get "emptied" next time you do a full backup?
As I understand it, you can choose to "truncate" the log as part of the
backup procedure, but I currently DON'T have this checked, so how do I
know my full backup really is "full" and will my tLogs keep growing
forever? All my backup settings are on the defaults. Can you advise the
correct backup settings to use for our simple setup?
(Point taken about doing full backup each day instead of each week)
Keith Kratochvil wrote:
> Q1 - When you apply the transaction log backups you will have all the
> modifications that have happened up to the point of the most recent
> transaction log that you applied.
> Q2 - Yes, the table that is created on Tuesday will be capured within
> Tuesday night's t-log backup. It will be "created" when you restore that
> log backup.
>
> If you have "lots" of user activity as you say you might want to issue
> transaction log backups more often than once per day.
>
Gerry Hickman (London UK)|||Gerry Hickman wrote:
> Hi Keith,
> Thanks, this is very helpful and this was how I originally understood it
> would work, but recently I wasn't sure. It's interesting the new table
> gets carried over.
> I have another question now!
> If the tLogs are storing all the changes since the last backup, do they
> get "emptied" next time you do a full backup?
> As I understand it, you can choose to "truncate" the log as part of the
> backup procedure, but I currently DON'T have this checked, so how do I
> know my full backup really is "full" and will my tLogs keep growing
> forever? All my backup settings are on the defaults. Can you advise the
> correct backup settings to use for our simple setup?
> (Point taken about doing full backup each day instead of each week)
> Keith Kratochvil wrote:
>> Q1 - When you apply the transaction log backups you will have all the
>> modifications that have happened up to the point of the most recent
>> transaction log that you applied.
>> Q2 - Yes, the table that is created on Tuesday will be capured within
>> Tuesday night's t-log backup. It will be "created" when you restore
>> that log backup.
>>
>> If you have "lots" of user activity as you say you might want to issue
>> transaction log backups more often than once per day.
>
Hi Gary
The FULL backup will not do anything to the logfiles. It's only a log
backup that will "touch" the logfile (Actually a FULL backup will take a
little part of the logfile, but that's only what it needs to be able to
perform a RESTORE).
When you do a log backup, it will mark the transactions that it has
backed up and the space can then be reused (TRUNCATE). Now the space can
be reused by new transactions, so your physical logfile doesn't need to
grow to contain the transactions.
Try to look up "Transaction Log Backups" in Books On Line. That chapter
gives a fairly good description of how it works.
Regards
Steen
Backup/Restore scenario
Hi,
I'm confused about the role of tLogs in a restore scenario. Imagine the
following:
Database running 24/7 with a lot of user activity. All backups at midnight
Fri - Full backup
Mon - tLog backup
Tue - tLog backup
Wed - tLog backup
Thurs - tLog backup
Imagine the server blows up one Thursday morning.
Q1. If I restore the full backup from the previous Friday, and then the
tLogs for Mon, Tue, Wed, does that mean I've got everything except the last
few user updates early Thursday morning?
Q2. What if the developers had added a new table on Tuesday? Would this new
table exist in the restored version?
Gerry Hickman
Q1 - When you apply the transaction log backups you will have all the
modifications that have happened up to the point of the most recent
transaction log that you applied.
Q2 - Yes, the table that is created on Tuesday will be capured within
Tuesday night's t-log backup. It will be "created" when you restore that
log backup.
If you have "lots" of user activity as you say you might want to issue
transaction log backups more often than once per day.
Keith Kratochvil
"Gerry Hickman" <gerry1uk@.netscape.net> wrote in message
news:u6ZvvygPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm confused about the role of tLogs in a restore scenario. Imagine the
> following:
> Database running 24/7 with a lot of user activity. All backups at midnight
> Fri - Full backup
> Mon - tLog backup
> Tue - tLog backup
> Wed - tLog backup
> Thurs - tLog backup
> Imagine the server blows up one Thursday morning.
> Q1. If I restore the full backup from the previous Friday, and then the
> tLogs for Mon, Tue, Wed, does that mean I've got everything except the
> last
> few user updates early Thursday morning?
> Q2. What if the developers had added a new table on Tuesday? Would this
> new
> table exist in the restored version?
> --
> Gerry Hickman
>
|||Hi Keith,
Thanks, this is very helpful and this was how I originally understood it
would work, but recently I wasn't sure. It's interesting the new table
gets carried over.
I have another question now!
If the tLogs are storing all the changes since the last backup, do they
get "emptied" next time you do a full backup?
As I understand it, you can choose to "truncate" the log as part of the
backup procedure, but I currently DON'T have this checked, so how do I
know my full backup really is "full" and will my tLogs keep growing
forever? All my backup settings are on the defaults. Can you advise the
correct backup settings to use for our simple setup?
(Point taken about doing full backup each day instead of each week)
Keith Kratochvil wrote:
> Q1 - When you apply the transaction log backups you will have all the
> modifications that have happened up to the point of the most recent
> transaction log that you applied.
> Q2 - Yes, the table that is created on Tuesday will be capured within
> Tuesday night's t-log backup. It will be "created" when you restore that
> log backup.
>
> If you have "lots" of user activity as you say you might want to issue
> transaction log backups more often than once per day.
>
Gerry Hickman (London UK)
|||Gerry Hickman wrote:
> Hi Keith,
> Thanks, this is very helpful and this was how I originally understood it
> would work, but recently I wasn't sure. It's interesting the new table
> gets carried over.
> I have another question now!
> If the tLogs are storing all the changes since the last backup, do they
> get "emptied" next time you do a full backup?
> As I understand it, you can choose to "truncate" the log as part of the
> backup procedure, but I currently DON'T have this checked, so how do I
> know my full backup really is "full" and will my tLogs keep growing
> forever? All my backup settings are on the defaults. Can you advise the
> correct backup settings to use for our simple setup?
> (Point taken about doing full backup each day instead of each week)
> Keith Kratochvil wrote:
>
Hi Gary
The FULL backup will not do anything to the logfiles. It's only a log
backup that will "touch" the logfile (Actually a FULL backup will take a
little part of the logfile, but that's only what it needs to be able to
perform a RESTORE).
When you do a log backup, it will mark the transactions that it has
backed up and the space can then be reused (TRUNCATE). Now the space can
be reused by new transactions, so your physical logfile doesn't need to
grow to contain the transactions.
Try to look up "Transaction Log Backups" in Books On Line. That chapter
gives a fairly good description of how it works.
Regards
Steen
I'm confused about the role of tLogs in a restore scenario. Imagine the
following:
Database running 24/7 with a lot of user activity. All backups at midnight
Fri - Full backup
Mon - tLog backup
Tue - tLog backup
Wed - tLog backup
Thurs - tLog backup
Imagine the server blows up one Thursday morning.
Q1. If I restore the full backup from the previous Friday, and then the
tLogs for Mon, Tue, Wed, does that mean I've got everything except the last
few user updates early Thursday morning?
Q2. What if the developers had added a new table on Tuesday? Would this new
table exist in the restored version?
Gerry Hickman
Q1 - When you apply the transaction log backups you will have all the
modifications that have happened up to the point of the most recent
transaction log that you applied.
Q2 - Yes, the table that is created on Tuesday will be capured within
Tuesday night's t-log backup. It will be "created" when you restore that
log backup.
If you have "lots" of user activity as you say you might want to issue
transaction log backups more often than once per day.
Keith Kratochvil
"Gerry Hickman" <gerry1uk@.netscape.net> wrote in message
news:u6ZvvygPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm confused about the role of tLogs in a restore scenario. Imagine the
> following:
> Database running 24/7 with a lot of user activity. All backups at midnight
> Fri - Full backup
> Mon - tLog backup
> Tue - tLog backup
> Wed - tLog backup
> Thurs - tLog backup
> Imagine the server blows up one Thursday morning.
> Q1. If I restore the full backup from the previous Friday, and then the
> tLogs for Mon, Tue, Wed, does that mean I've got everything except the
> last
> few user updates early Thursday morning?
> Q2. What if the developers had added a new table on Tuesday? Would this
> new
> table exist in the restored version?
> --
> Gerry Hickman
>
|||Hi Keith,
Thanks, this is very helpful and this was how I originally understood it
would work, but recently I wasn't sure. It's interesting the new table
gets carried over.
I have another question now!
If the tLogs are storing all the changes since the last backup, do they
get "emptied" next time you do a full backup?
As I understand it, you can choose to "truncate" the log as part of the
backup procedure, but I currently DON'T have this checked, so how do I
know my full backup really is "full" and will my tLogs keep growing
forever? All my backup settings are on the defaults. Can you advise the
correct backup settings to use for our simple setup?
(Point taken about doing full backup each day instead of each week)
Keith Kratochvil wrote:
> Q1 - When you apply the transaction log backups you will have all the
> modifications that have happened up to the point of the most recent
> transaction log that you applied.
> Q2 - Yes, the table that is created on Tuesday will be capured within
> Tuesday night's t-log backup. It will be "created" when you restore that
> log backup.
>
> If you have "lots" of user activity as you say you might want to issue
> transaction log backups more often than once per day.
>
Gerry Hickman (London UK)
|||Gerry Hickman wrote:
> Hi Keith,
> Thanks, this is very helpful and this was how I originally understood it
> would work, but recently I wasn't sure. It's interesting the new table
> gets carried over.
> I have another question now!
> If the tLogs are storing all the changes since the last backup, do they
> get "emptied" next time you do a full backup?
> As I understand it, you can choose to "truncate" the log as part of the
> backup procedure, but I currently DON'T have this checked, so how do I
> know my full backup really is "full" and will my tLogs keep growing
> forever? All my backup settings are on the defaults. Can you advise the
> correct backup settings to use for our simple setup?
> (Point taken about doing full backup each day instead of each week)
> Keith Kratochvil wrote:
>
Hi Gary
The FULL backup will not do anything to the logfiles. It's only a log
backup that will "touch" the logfile (Actually a FULL backup will take a
little part of the logfile, but that's only what it needs to be able to
perform a RESTORE).
When you do a log backup, it will mark the transactions that it has
backed up and the space can then be reused (TRUNCATE). Now the space can
be reused by new transactions, so your physical logfile doesn't need to
grow to contain the transactions.
Try to look up "Transaction Log Backups" in Books On Line. That chapter
gives a fairly good description of how it works.
Regards
Steen
Backup/Restore scenario
Hi,
I'm confused about the role of tLogs in a restore scenario. Imagine the
following:
Database running 24/7 with a lot of user activity. All backups at midnight
Fri - Full backup
Mon - tLog backup
Tue - tLog backup
Wed - tLog backup
Thurs - tLog backup
Imagine the server blows up one Thursday morning.
Q1. If I restore the full backup from the previous Friday, and then the
tLogs for Mon, Tue, Wed, does that mean I've got everything except the last
few user updates early Thursday morning?
Q2. What if the developers had added a new table on Tuesday? Would this new
table exist in the restored version?
Gerry HickmanQ1 - When you apply the transaction log backups you will have all the
modifications that have happened up to the point of the most recent
transaction log that you applied.
Q2 - Yes, the table that is created on Tuesday will be capured within
Tuesday night's t-log backup. It will be "created" when you restore that
log backup.
If you have "lots" of user activity as you say you might want to issue
transaction log backups more often than once per day.
Keith Kratochvil
"Gerry Hickman" <gerry1uk@.netscape.net> wrote in message
news:u6ZvvygPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm confused about the role of tLogs in a restore scenario. Imagine the
> following:
> Database running 24/7 with a lot of user activity. All backups at midnight
> Fri - Full backup
> Mon - tLog backup
> Tue - tLog backup
> Wed - tLog backup
> Thurs - tLog backup
> Imagine the server blows up one Thursday morning.
> Q1. If I restore the full backup from the previous Friday, and then the
> tLogs for Mon, Tue, Wed, does that mean I've got everything except the
> last
> few user updates early Thursday morning?
> Q2. What if the developers had added a new table on Tuesday? Would this
> new
> table exist in the restored version?
> --
> Gerry Hickman
>|||Hi Keith,
Thanks, this is very helpful and this was how I originally understood it
would work, but recently I wasn't sure. It's interesting the new table
gets carried over.
I have another question now!
If the tLogs are storing all the changes since the last backup, do they
get "emptied" next time you do a full backup?
As I understand it, you can choose to "truncate" the log as part of the
backup procedure, but I currently DON'T have this checked, so how do I
know my full backup really is "full" and will my tLogs keep growing
forever? All my backup settings are on the defaults. Can you advise the
correct backup settings to use for our simple setup?
(Point taken about doing full backup each day instead of each week)
Keith Kratochvil wrote:
> Q1 - When you apply the transaction log backups you will have all the
> modifications that have happened up to the point of the most recent
> transaction log that you applied.
> Q2 - Yes, the table that is created on Tuesday will be capured within
> Tuesday night's t-log backup. It will be "created" when you restore that
> log backup.
>
> If you have "lots" of user activity as you say you might want to issue
> transaction log backups more often than once per day.
>
Gerry Hickman (London UK)|||Gerry Hickman wrote:
> Hi Keith,
> Thanks, this is very helpful and this was how I originally understood it
> would work, but recently I wasn't sure. It's interesting the new table
> gets carried over.
> I have another question now!
> If the tLogs are storing all the changes since the last backup, do they
> get "emptied" next time you do a full backup?
> As I understand it, you can choose to "truncate" the log as part of the
> backup procedure, but I currently DON'T have this checked, so how do I
> know my full backup really is "full" and will my tLogs keep growing
> forever? All my backup settings are on the defaults. Can you advise the
> correct backup settings to use for our simple setup?
> (Point taken about doing full backup each day instead of each week)
> Keith Kratochvil wrote:
>
Hi Gary
The FULL backup will not do anything to the logfiles. It's only a log
backup that will "touch" the logfile (Actually a FULL backup will take a
little part of the logfile, but that's only what it needs to be able to
perform a RESTORE).
When you do a log backup, it will mark the transactions that it has
backed up and the space can then be reused (TRUNCATE). Now the space can
be reused by new transactions, so your physical logfile doesn't need to
grow to contain the transactions.
Try to look up "Transaction Log Backups" in Books On Line. That chapter
gives a fairly good description of how it works.
Regards
Steen
I'm confused about the role of tLogs in a restore scenario. Imagine the
following:
Database running 24/7 with a lot of user activity. All backups at midnight
Fri - Full backup
Mon - tLog backup
Tue - tLog backup
Wed - tLog backup
Thurs - tLog backup
Imagine the server blows up one Thursday morning.
Q1. If I restore the full backup from the previous Friday, and then the
tLogs for Mon, Tue, Wed, does that mean I've got everything except the last
few user updates early Thursday morning?
Q2. What if the developers had added a new table on Tuesday? Would this new
table exist in the restored version?
Gerry HickmanQ1 - When you apply the transaction log backups you will have all the
modifications that have happened up to the point of the most recent
transaction log that you applied.
Q2 - Yes, the table that is created on Tuesday will be capured within
Tuesday night's t-log backup. It will be "created" when you restore that
log backup.
If you have "lots" of user activity as you say you might want to issue
transaction log backups more often than once per day.
Keith Kratochvil
"Gerry Hickman" <gerry1uk@.netscape.net> wrote in message
news:u6ZvvygPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm confused about the role of tLogs in a restore scenario. Imagine the
> following:
> Database running 24/7 with a lot of user activity. All backups at midnight
> Fri - Full backup
> Mon - tLog backup
> Tue - tLog backup
> Wed - tLog backup
> Thurs - tLog backup
> Imagine the server blows up one Thursday morning.
> Q1. If I restore the full backup from the previous Friday, and then the
> tLogs for Mon, Tue, Wed, does that mean I've got everything except the
> last
> few user updates early Thursday morning?
> Q2. What if the developers had added a new table on Tuesday? Would this
> new
> table exist in the restored version?
> --
> Gerry Hickman
>|||Hi Keith,
Thanks, this is very helpful and this was how I originally understood it
would work, but recently I wasn't sure. It's interesting the new table
gets carried over.
I have another question now!
If the tLogs are storing all the changes since the last backup, do they
get "emptied" next time you do a full backup?
As I understand it, you can choose to "truncate" the log as part of the
backup procedure, but I currently DON'T have this checked, so how do I
know my full backup really is "full" and will my tLogs keep growing
forever? All my backup settings are on the defaults. Can you advise the
correct backup settings to use for our simple setup?
(Point taken about doing full backup each day instead of each week)
Keith Kratochvil wrote:
> Q1 - When you apply the transaction log backups you will have all the
> modifications that have happened up to the point of the most recent
> transaction log that you applied.
> Q2 - Yes, the table that is created on Tuesday will be capured within
> Tuesday night's t-log backup. It will be "created" when you restore that
> log backup.
>
> If you have "lots" of user activity as you say you might want to issue
> transaction log backups more often than once per day.
>
Gerry Hickman (London UK)|||Gerry Hickman wrote:
> Hi Keith,
> Thanks, this is very helpful and this was how I originally understood it
> would work, but recently I wasn't sure. It's interesting the new table
> gets carried over.
> I have another question now!
> If the tLogs are storing all the changes since the last backup, do they
> get "emptied" next time you do a full backup?
> As I understand it, you can choose to "truncate" the log as part of the
> backup procedure, but I currently DON'T have this checked, so how do I
> know my full backup really is "full" and will my tLogs keep growing
> forever? All my backup settings are on the defaults. Can you advise the
> correct backup settings to use for our simple setup?
> (Point taken about doing full backup each day instead of each week)
> Keith Kratochvil wrote:
>
Hi Gary
The FULL backup will not do anything to the logfiles. It's only a log
backup that will "touch" the logfile (Actually a FULL backup will take a
little part of the logfile, but that's only what it needs to be able to
perform a RESTORE).
When you do a log backup, it will mark the transactions that it has
backed up and the space can then be reused (TRUNCATE). Now the space can
be reused by new transactions, so your physical logfile doesn't need to
grow to contain the transactions.
Try to look up "Transaction Log Backups" in Books On Line. That chapter
gives a fairly good description of how it works.
Regards
Steen
Subscribe to:
Posts (Atom)