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