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.
>>
>