Larger (100gig+) SQl database backup time

We have some databases (100-130 gig) that take 30 plus hours to backup. Is
this normal and is there a better way to do this than using the built in
backup tools? Or a bteer way to use the tools?
I know we can do patial backups but I am specifically looking for
information on the full backup procedure.
OS server 2003, 4gig ram, database sql2000.
Thanks!Hi
You don't say what you are backing up to! You could use multiple backup
devices to cut down this time. Make sure that you do not have contention for
resources when backing up i.e. split data files from backup files
John
"Stedak" wrote:

> We have some databases (100-130 gig) that take 30 plus hours to backup. Is
> this normal and is there a better way to do this than using the built in
> backup tools? Or a bteer way to use the tools?
> I know we can do patial backups but I am specifically looking for
> information on the full backup procedure.
> OS server 2003, 4gig ram, database sql2000.
> Thanks!|||Stedak wrote:
> We have some databases (100-130 gig) that take 30 plus hours to backup. Is
> this normal and is there a better way to do this than using the built in
> backup tools? Or a bteer way to use the tools?
> I know we can do patial backups but I am specifically looking for
> information on the full backup procedure.
> OS server 2003, 4gig ram, database sql2000.
> Thanks!
Something is wrong... We have multiple databases here that are nearly
200GB each, and none of them takes longer than 45 minutes to backup to
disk. Are you backing up to disk or tape?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Stedak wrote:
> We have some databases (100-130 gig) that take 30 plus hours to backup. Is
> this normal and is there a better way to do this than using the built in
> backup tools? Or a bteer way to use the tools?
> I know we can do patial backups but I am specifically looking for
> information on the full backup procedure.
> OS server 2003, 4gig ram, database sql2000.
> Thanks!
In addition to my other post, make sure you don't have anti-virus
software that is scanning the backup file as you're writing it. Also
check for disk fragmentation.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||We are backing up to an external USB hard drive.
"Tracy McKibben" wrote:

> Stedak wrote:
> Something is wrong... We have multiple databases here that are nearly
> 200GB each, and none of them takes longer than 45 minutes to backup to
> disk. Are you backing up to disk or tape?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Probably an IDE 7200 RPM?
Yes, that will take a whole lot longert than an internal SCSI RAID set of
drives.
30 hours seems excessive. have you done a local backup to compare, or is
the reason for the external that you don't have enough drive space locally?
Also, there are backup compression utilities on teh market...LiteSpeed from
Quest and SQL Backup from Red Gate are the two I know well.
Kevin Hill
3NF Consulting
www.3nf-inc.com
"Stedak" <Stedak@.discussions.microsoft.com> wrote in message
news:617BD7F1-D85D-46D6-979E-B9090BA795DE@.microsoft.com...[vbcol=seagreen]
> We are backing up to an external USB hard drive.
> "Tracy McKibben" wrote:
>|||Stedak wrote:
> We are backing up to an external USB hard drive.
>
Probably not the best choice for high-speed SQL Server backups...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Stedak" <Stedak@.discussions.microsoft.com> wrote in message
news:617BD7F1-D85D-46D6-979E-B9090BA795DE@.microsoft.com...
> We are backing up to an external USB hard drive.
>
Not a great choice.
I like the idea of backing up to disk though.
I use Snap Servers (specifically the 4200 right now) and get 100+ gig of
backups done in about 4+ hours.
And there's other tricks one can do to get even faster backups.
(for example writing to TWO snap servers would cut that about in 1/2.)
[vbcol=seagreen]
> "Tracy McKibben" wrote:
>

Larger (100gig+) SQl database backup time

We have some databases (100-130 gig) that take 30 plus hours to backup. Is
this normal and is there a better way to do this than using the built in
backup tools? Or a bteer way to use the tools?
I know we can do patial backups but I am specifically looking for
information on the full backup procedure.
OS server 2003, 4gig ram, database sql2000.
Thanks!Hi
You don't say what you are backing up to! You could use multiple backup
devices to cut down this time. Make sure that you do not have contention for
resources when backing up i.e. split data files from backup files
John
"Stedak" wrote:
> We have some databases (100-130 gig) that take 30 plus hours to backup. Is
> this normal and is there a better way to do this than using the built in
> backup tools? Or a bteer way to use the tools?
> I know we can do patial backups but I am specifically looking for
> information on the full backup procedure.
> OS server 2003, 4gig ram, database sql2000.
> Thanks!|||Stedak wrote:
> We have some databases (100-130 gig) that take 30 plus hours to backup. Is
> this normal and is there a better way to do this than using the built in
> backup tools? Or a bteer way to use the tools?
> I know we can do patial backups but I am specifically looking for
> information on the full backup procedure.
> OS server 2003, 4gig ram, database sql2000.
> Thanks!
Something is wrong... We have multiple databases here that are nearly
200GB each, and none of them takes longer than 45 minutes to backup to
disk. Are you backing up to disk or tape?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Stedak wrote:
> We have some databases (100-130 gig) that take 30 plus hours to backup. Is
> this normal and is there a better way to do this than using the built in
> backup tools? Or a bteer way to use the tools?
> I know we can do patial backups but I am specifically looking for
> information on the full backup procedure.
> OS server 2003, 4gig ram, database sql2000.
> Thanks!
In addition to my other post, make sure you don't have anti-virus
software that is scanning the backup file as you're writing it. Also
check for disk fragmentation.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||We are backing up to an external USB hard drive.
"Tracy McKibben" wrote:
> Stedak wrote:
> > We have some databases (100-130 gig) that take 30 plus hours to backup. Is
> > this normal and is there a better way to do this than using the built in
> > backup tools? Or a bteer way to use the tools?
> >
> > I know we can do patial backups but I am specifically looking for
> > information on the full backup procedure.
> >
> > OS server 2003, 4gig ram, database sql2000.
> >
> > Thanks!
> Something is wrong... We have multiple databases here that are nearly
> 200GB each, and none of them takes longer than 45 minutes to backup to
> disk. Are you backing up to disk or tape?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Probably an IDE 7200 RPM?
Yes, that will take a whole lot longert than an internal SCSI RAID set of
drives.
30 hours seems excessive. have you done a local backup to compare, or is
the reason for the external that you don't have enough drive space locally?
Also, there are backup compression utilities on teh market...LiteSpeed from
Quest and SQL Backup from Red Gate are the two I know well.
--
Kevin Hill
3NF Consulting
www.3nf-inc.com
"Stedak" <Stedak@.discussions.microsoft.com> wrote in message
news:617BD7F1-D85D-46D6-979E-B9090BA795DE@.microsoft.com...
> We are backing up to an external USB hard drive.
> "Tracy McKibben" wrote:
>> Stedak wrote:
>> > We have some databases (100-130 gig) that take 30 plus hours to backup.
>> > Is
>> > this normal and is there a better way to do this than using the built
>> > in
>> > backup tools? Or a bteer way to use the tools?
>> >
>> > I know we can do patial backups but I am specifically looking for
>> > information on the full backup procedure.
>> >
>> > OS server 2003, 4gig ram, database sql2000.
>> >
>> > Thanks!
>> Something is wrong... We have multiple databases here that are nearly
>> 200GB each, and none of them takes longer than 45 minutes to backup to
>> disk. Are you backing up to disk or tape?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||Stedak wrote:
> We are backing up to an external USB hard drive.
>
Probably not the best choice for high-speed SQL Server backups...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Stedak" <Stedak@.discussions.microsoft.com> wrote in message
news:617BD7F1-D85D-46D6-979E-B9090BA795DE@.microsoft.com...
> We are backing up to an external USB hard drive.
>
Not a great choice.
I like the idea of backing up to disk though.
I use Snap Servers (specifically the 4200 right now) and get 100+ gig of
backups done in about 4+ hours.
And there's other tricks one can do to get even faster backups.
(for example writing to TWO snap servers would cut that about in 1/2.)
> "Tracy McKibben" wrote:
>> Stedak wrote:
>> > We have some databases (100-130 gig) that take 30 plus hours to backup.
>> > Is
>> > this normal and is there a better way to do this than using the built
>> > in
>> > backup tools? Or a bteer way to use the tools?
>> >
>> > I know we can do patial backups but I am specifically looking for
>> > information on the full backup procedure.
>> >
>> > OS server 2003, 4gig ram, database sql2000.
>> >
>> > Thanks!
>> Something is wrong... We have multiple databases here that are nearly
>> 200GB each, and none of them takes longer than 45 minutes to backup to
>> disk. Are you backing up to disk or tape?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com

large updatestatement

Hi,
Is there a way to update a table without specifying the columns to be
updated?
I have a backup of the table which i join on a id. All columns are the
same, but there are more than 100. So if i can create a statement like
you can for an insert, it would be great.
The backup table resides on a different server, so i also use the
openquery method to get the records.
Is this possible what i want?>Is there a way to update a table without specifying the columns to be
>updated?
NO.
You can probably use some smart T-SQL to create the satements
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23X04bLcmFHA.3120@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is there a way to update a table without specifying the columns to be
> updated?
> I have a backup of the table which i join on a id. All columns are the
> same, but there are more than 100. So if i can create a statement like you
> can for an insert, it would be great.
> The backup table resides on a different server, so i also use the
> openquery method to get the records.
> Is this possible what i want?|||You don't need to use OPENQUERY if both servers are SQL Server. Just
use the four-part name:
server.database.dbo.table
You have to list the columns in a UPDATE statement. You should do in an
INSERT too - in the long term it makes your code much more reliable and
easier to support. Really, it isn't a problem because you can drag the
list of columns from the Object Browser in Query Analyzer without
typing a thing. It should only be the work of a few seconds to create
your UPDATE statement.
David Portas
SQL Server MVP
--|||Hi David,
I thought it couldn't be done. Anyway i used the four-part name method,
but that didn't work for that specifically database. All other databases
on that server will work. Do you know why, all permissions and other
stuff are correct. The only thing i can see, is the logic filename
differs from the databasename.
David Portas wrote:
> You don't need to use OPENQUERY if both servers are SQL Server. Just
> use the four-part name:
> server.database.dbo.table
> You have to list the columns in a UPDATE statement. You should do in an
> INSERT too - in the long term it makes your code much more reliable and
> easier to support. Really, it isn't a problem because you can drag the
> list of columns from the Object Browser in Query Analyzer without
> typing a thing. It should only be the work of a few seconds to create
> your UPDATE statement.
>|||>>but that didn't work for that specifically database
Means what?
Are you getting an Error back?
Then please post the error.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:u0sNoxcmFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hi David,
> I thought it couldn't be done. Anyway i used the four-part name method,
> but that didn't work for that specifically database. All other databases
> on that server will work. Do you know why, all permissions and other stuff
> are correct. The only thing i can see, is the logic filename differs from
> the databasename.
> David Portas wrote:|||Hi,
This is the errormessage:
Invalid schema or catalog specified for provider 'SQLOLEDB'.

Large transaction log backups

I have a maintenance plans set up for a few of my databases where I am
running a full backup on Sundays, followed by transaction log backups each
day of the week. As far as I understand it, each transaction log should
then be backing up transactions made since the last backup (whether that's a
full or transaction).
What I'm actually seeing though is that the first transaction log backup
after each full one is far larger than I'm expecting (in comparison to the
size of the database anyway).
For example, one of those is producing backup files of these sizes:
06/05/2007 02:12 857,955,840 ADM_db_200705060200.BAK
07/05/2007 00:12 1,313,012,224 ADM_tlog_200705070000.TRN
08/05/2007 00:03 611,840 ADM_tlog_200705080001.TRN
09/05/2007 00:03 5,133,824 ADM_tlog_200705090001.TRN
10/05/2007 00:02 6,510,080 ADM_tlog_200705100000.TRN
11/05/2007 00:03 10,966,528 ADM_tlog_200705110001.TRN
12/05/2007 00:02 10,376,704 ADM_tlog_200705120000.TRN
13/05/2007 02:20 860,951,040 ADM_db_200705130201.BAK
14/05/2007 00:06 428,621,312 ADM_tlog_200705140002.TRN
15/05/2007 00:02 9,046,528 ADM_tlog_200705150000.TRN
16/05/2007 00:02 5,048,832 ADM_tlog_200705160000.TRN
17/05/2007 00:03 13,699,584 ADM_tlog_200705170000.TRN
18/05/2007 00:04 92,212,736 ADM_tlog_200705180000.TRN
19/05/2007 00:03 32,824,832 ADM_tlog_200705190002.TRN
20/05/2007 02:06 864,737,792 ADM_db_200705200202.BAK
21/05/2007 00:05 393,709,056 ADM_tlog_200705210002.TRN
22/05/2007 00:04 17,836,544 ADM_tlog_200705220003.TRN
I'm think I'm misunderstanding the process somewhere. Could anyone clarify?"Rob Oldfield" <blah@.blah.com> wrote in message
news:%23mRHK8EnHHA.3520@.TK2MSFTNGP04.phx.gbl...
>I have a maintenance plans set up for a few of my databases where I am
<Snip>
Please ignore. I've found lots of answers by just Googling. Apologies... I
should have done that first.|||In a Full or Bulk logged recovery model you should plan at regular interval a
clearing of the transaction log, because the full backup in this model
does'nt automatically truncate the log.
The Backup LOG backs up the current consistency of the transaction log
starting from the last successful backup log.
Gilberto Zampatti
"Rob Oldfield" wrote:
> I have a maintenance plans set up for a few of my databases where I am
> running a full backup on Sundays, followed by transaction log backups each
> day of the week. As far as I understand it, each transaction log should
> then be backing up transactions made since the last backup (whether that's a
> full or transaction).
> What I'm actually seeing though is that the first transaction log backup
> after each full one is far larger than I'm expecting (in comparison to the
> size of the database anyway).
> For example, one of those is producing backup files of these sizes:
> 06/05/2007 02:12 857,955,840 ADM_db_200705060200.BAK
> 07/05/2007 00:12 1,313,012,224 ADM_tlog_200705070000.TRN
> 08/05/2007 00:03 611,840 ADM_tlog_200705080001.TRN
> 09/05/2007 00:03 5,133,824 ADM_tlog_200705090001.TRN
> 10/05/2007 00:02 6,510,080 ADM_tlog_200705100000.TRN
> 11/05/2007 00:03 10,966,528 ADM_tlog_200705110001.TRN
> 12/05/2007 00:02 10,376,704 ADM_tlog_200705120000.TRN
> 13/05/2007 02:20 860,951,040 ADM_db_200705130201.BAK
> 14/05/2007 00:06 428,621,312 ADM_tlog_200705140002.TRN
> 15/05/2007 00:02 9,046,528 ADM_tlog_200705150000.TRN
> 16/05/2007 00:02 5,048,832 ADM_tlog_200705160000.TRN
> 17/05/2007 00:03 13,699,584 ADM_tlog_200705170000.TRN
> 18/05/2007 00:04 92,212,736 ADM_tlog_200705180000.TRN
> 19/05/2007 00:03 32,824,832 ADM_tlog_200705190002.TRN
> 20/05/2007 02:06 864,737,792 ADM_db_200705200202.BAK
> 21/05/2007 00:05 393,709,056 ADM_tlog_200705210002.TRN
> 22/05/2007 00:04 17,836,544 ADM_tlog_200705220003.TRN
> I'm think I'm misunderstanding the process somewhere. Could anyone clarify?
>
>

Large transaction log backups

I have a maintenance plans set up for a few of my databases where I am
running a full backup on Sundays, followed by transaction log backups each
day of the week. As far as I understand it, each transaction log should
then be backing up transactions made since the last backup (whether that's a
full or transaction).
What I'm actually seeing though is that the first transaction log backup
after each full one is far larger than I'm expecting (in comparison to the
size of the database anyway).
For example, one of those is producing backup files of these sizes:
06/05/2007 02:12 857,955,840 ADM_db_200705060200.BAK
07/05/2007 00:12 1,313,012,224 ADM_tlog_200705070000.TRN
08/05/2007 00:03 611,840 ADM_tlog_200705080001.TRN
09/05/2007 00:03 5,133,824 ADM_tlog_200705090001.TRN
10/05/2007 00:02 6,510,080 ADM_tlog_200705100000.TRN
11/05/2007 00:03 10,966,528 ADM_tlog_200705110001.TRN
12/05/2007 00:02 10,376,704 ADM_tlog_200705120000.TRN
13/05/2007 02:20 860,951,040 ADM_db_200705130201.BAK
14/05/2007 00:06 428,621,312 ADM_tlog_200705140002.TRN
15/05/2007 00:02 9,046,528 ADM_tlog_200705150000.TRN
16/05/2007 00:02 5,048,832 ADM_tlog_200705160000.TRN
17/05/2007 00:03 13,699,584 ADM_tlog_200705170000.TRN
18/05/2007 00:04 92,212,736 ADM_tlog_200705180000.TRN
19/05/2007 00:03 32,824,832 ADM_tlog_200705190002.TRN
20/05/2007 02:06 864,737,792 ADM_db_200705200202.BAK
21/05/2007 00:05 393,709,056 ADM_tlog_200705210002.TRN
22/05/2007 00:04 17,836,544 ADM_tlog_200705220003.TRN
I'm think I'm misunderstanding the process somewhere. Could anyone clarify?"Rob Oldfield" <blah@.blah.com> wrote in message
news:%23mRHK8EnHHA.3520@.TK2MSFTNGP04.phx.gbl...
>I have a maintenance plans set up for a few of my databases where I am
<Snip>
Please ignore. I've found lots of answers by just Googling. Apologies... I
should have done that first.|||In a Full or Bulk logged recovery model you should plan at regular interval
a
clearing of the transaction log, because the full backup in this model
does'nt automatically truncate the log.
The Backup LOG backs up the current consistency of the transaction log
starting from the last successful backup log.
Gilberto Zampatti
"Rob Oldfield" wrote:

> I have a maintenance plans set up for a few of my databases where I am
> running a full backup on Sundays, followed by transaction log backups each
> day of the week. As far as I understand it, each transaction log should
> then be backing up transactions made since the last backup (whether that's
a
> full or transaction).
> What I'm actually seeing though is that the first transaction log backup
> after each full one is far larger than I'm expecting (in comparison to the
> size of the database anyway).
> For example, one of those is producing backup files of these sizes:
> 06/05/2007 02:12 857,955,840 ADM_db_200705060200.BAK
> 07/05/2007 00:12 1,313,012,224 ADM_tlog_200705070000.TRN
> 08/05/2007 00:03 611,840 ADM_tlog_200705080001.TRN
> 09/05/2007 00:03 5,133,824 ADM_tlog_200705090001.TRN
> 10/05/2007 00:02 6,510,080 ADM_tlog_200705100000.TRN
> 11/05/2007 00:03 10,966,528 ADM_tlog_200705110001.TRN
> 12/05/2007 00:02 10,376,704 ADM_tlog_200705120000.TRN
> 13/05/2007 02:20 860,951,040 ADM_db_200705130201.BAK
> 14/05/2007 00:06 428,621,312 ADM_tlog_200705140002.TRN
> 15/05/2007 00:02 9,046,528 ADM_tlog_200705150000.TRN
> 16/05/2007 00:02 5,048,832 ADM_tlog_200705160000.TRN
> 17/05/2007 00:03 13,699,584 ADM_tlog_200705170000.TRN
> 18/05/2007 00:04 92,212,736 ADM_tlog_200705180000.TRN
> 19/05/2007 00:03 32,824,832 ADM_tlog_200705190002.TRN
> 20/05/2007 02:06 864,737,792 ADM_db_200705200202.BAK
> 21/05/2007 00:05 393,709,056 ADM_tlog_200705210002.TRN
> 22/05/2007 00:04 17,836,544 ADM_tlog_200705220003.TRN
> I'm think I'm misunderstanding the process somewhere. Could anyone clarif
y?
>
>

Large Transaction Log Backup after Database Backup

On SQL Server 2000 I have DB maintenance plans to backup databases and
transaction logs. The database is backed up daily at 02:00 hours and
transaction logs backed up every 2 hours (excluding 02:00 hours).The problem
I have is that the first backup of the transaction log after the database
backup is huge. Eg: normal transaction log backups are approx 100mb but the
abnormal one is approx 13gb. The database size is approx 20gb. I can shrink
the transaction log without problem but it expands again after the database
backup.
Does anyone have an explanation for this and is there a resolution?
Thanks
SteveBMy guess is that you either defrag the indexes and/or shrink the database fi
les. Both of these operations are
logged in the transaction log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SteveB" <SteveB@.discussions.microsoft.com> wrote in message
news:4BA25F24-0289-4808-B351-D26E2CB6C988@.microsoft.com...
> On SQL Server 2000 I have DB maintenance plans to backup databases and
> transaction logs. The database is backed up daily at 02:00 hours and
> transaction logs backed up every 2 hours (excluding 02:00 hours).The probl
em
> I have is that the first backup of the transaction log after the database
> backup is huge. Eg: normal transaction log backups are approx 100mb but th
e
> abnormal one is approx 13gb. The database size is approx 20gb. I can shrin
k
> the transaction log without problem but it expands again after the databas
e
> backup.
> Does anyone have an explanation for this and is there a resolution?
> Thanks
> SteveB|||Your database maintenance plan not only backs up the database, but it can
also reindex all the indexes on the tables or shrink the data file. If you
have either of these last two options ('Reorganize data and index pages' and
'Remove unused space from database files' respectively) checked on the
second page of the maintenance plan wizard, you might want to uncheck them
and create a maintenance plan that runs on a less regular schedule, like
once a week.
Shrinking the database is not useful in a production environment in general,
so you can start with unchecking that if you have it checked and see what
the result is.
Reindexing is something you should do on a regular basis, but as a rough
guide, once a week is enough for most moderately busy systems.
Jacco Schalkwijk
SQL Server MVP
"SteveB" <SteveB@.discussions.microsoft.com> wrote in message
news:4BA25F24-0289-4808-B351-D26E2CB6C988@.microsoft.com...
> On SQL Server 2000 I have DB maintenance plans to backup databases and
> transaction logs. The database is backed up daily at 02:00 hours and
> transaction logs backed up every 2 hours (excluding 02:00 hours).The
> problem
> I have is that the first backup of the transaction log after the database
> backup is huge. Eg: normal transaction log backups are approx 100mb but
> the
> abnormal one is approx 13gb. The database size is approx 20gb. I can
> shrink
> the transaction log without problem but it expands again after the
> database
> backup.
> Does anyone have an explanation for this and is there a resolution?
> Thanks
> SteveB|||In article <OjsO$X7fEHA.3928@.TK2MSFTNGP11.phx.gbl>,
jacco.please.reply@.to.newsgroups.mvps.org.invalid says...
> Your database maintenance plan not only backs up the database, but it can
> also reindex all the indexes on the tables or shrink the data file. If you
> have either of these last two options ('Reorganize data and index pages' a
nd
> 'Remove unused space from database files' respectively) checked on the
> second page of the maintenance plan wizard, you might want to uncheck them
> and create a maintenance plan that runs on a less regular schedule, like
> once a week.
> Shrinking the database is not useful in a production environment in genera
l,
> so you can start with unchecking that if you have it checked and see what
> the result is.
> Reindexing is something you should do on a regular basis, but as a rough
> guide, once a week is enough for most moderately busy systems.
What do you recommend for 24/7 systems? Just INDEXDEFRAG in place of
REINDEX?|||24/7 you have little choice but to use INDEXDEFRAG. DBREINDEX takes out an
exclusive lock on the table it is reindexing and makes it unavailable to
other users. In contrast, INDEXDEFRAG uses short transactions and doesn't
make the table unavailable.
Jacco Schalkwijk
SQL Server MVP
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b84088229f26a90989695@.news...
> In article <OjsO$X7fEHA.3928@.TK2MSFTNGP11.phx.gbl>,
> jacco.please.reply@.to.newsgroups.mvps.org.invalid says...
> What do you recommend for 24/7 systems? Just INDEXDEFRAG in place of
> REINDEX?|||Hi Guys
Thanks for the responses. I did have similar thoughts. I am doing some
re-indexing, but this takes place at midnight and completes well before the
backup at 02:00 so I would not expect a large log after the database backup.
Will do a trial run on test database with no maintenance activities tonite
and see what results.
"Tibor Karaszi" wrote:

> My guess is that you either defrag the indexes and/or shrink the database
files. Both of these operations are
> logged in the transaction log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SteveB" <SteveB@.discussions.microsoft.com> wrote in message
> news:4BA25F24-0289-4808-B351-D26E2CB6C988@.microsoft.com...
>
>|||The database and log backups are independent of each other. Backing up the
database doesn't have any effect the transaction log, so the extra
transaction log records that are created at midnight (after the 00:00
transaction log backup, I assume) are still there when you next backup the
transaction log at 04:00.
Jacco Schalkwijk
SQL Server MVP
"SteveB" <SteveB@.discussions.microsoft.com> wrote in message
news:413EC866-0D72-4FA1-8A83-A21DDB4656F3@.microsoft.com...[vbcol=seagreen]
> Hi Guys
> Thanks for the responses. I did have similar thoughts. I am doing some
> re-indexing, but this takes place at midnight and completes well before
> the
> backup at 02:00 so I would not expect a large log after the database
> backup.
> Will do a trial run on test database with no maintenance activities tonite
> and see what results.
> "Tibor Karaszi" wrote:
>|||Jacco
Thanks for response. But my understanding was that the transaction log was
truncated after a full database backup. So I would expect the transaction
log to be truncated at 02:00 hours. Whilst I don't expect the physical size
of the transaction log to change I do expect the size of the transaction log
backup to be minimal for the 04:00 backup.
"Jacco Schalkwijk" wrote:

> The database and log backups are independent of each other. Backing up the
> database doesn't have any effect the transaction log, so the extra
> transaction log records that are created at midnight (after the 00:00
> transaction log backup, I assume) are still there when you next backup the
> transaction log at 04:00.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "SteveB" <SteveB@.discussions.microsoft.com> wrote in message
> news:413EC866-0D72-4FA1-8A83-A21DDB4656F3@.microsoft.com...
>
>|||Steve,
I'm afraid your understanding of the matter was wrong. Try the following
script and see for yourself that the database backup doesn't truncate the
transaction log:
CREATE DATABASE steve
GO
ALTER DATABASE steve SET RECOVERY FULL
GO
USE steve
GO
BACKUP DATABASE steve TO DISK = 'steve.bak'
-- Need an initial backup to 'switch on' FULL RECOVERY
GO
DBCC SQLPERF ( LOGSPACE )
GO
SELECT s0.* INTO test_table
FROM sysobjects s0, sysobjects s1, sysobjects s2--, sysobjects s3
GO
DBCC SQLPERF ( LOGSPACE )
GO
BACKUP DATABASE steve TO DISK = 'steve.bak'
GO
DBCC SQLPERF ( LOGSPACE )
GO
USE master
GO
DROP DATABASE steve
Jacco Schalkwijk
SQL Server MVP
"SteveB" <SteveB@.discussions.microsoft.com> wrote in message
news:BC1602B4-DC3B-4E61-9477-7D61FE8055E7@.microsoft.com...[vbcol=seagreen]
> Jacco
> Thanks for response. But my understanding was that the transaction log was
> truncated after a full database backup. So I would expect the transaction
> log to be truncated at 02:00 hours. Whilst I don't expect the physical
> size
> of the transaction log to change I do expect the size of the transaction
> log
> backup to be minimal for the 04:00 backup.
> "Jacco Schalkwijk" wrote:
>

Large Transaction Log Backup after Database Backup

On SQL Server 2000 I have DB maintenance plans to backup databases and
transaction logs. The database is backed up daily at 02:00 hours and
transaction logs backed up every 2 hours (excluding 02:00 hours).The problem
I have is that the first backup of the transaction log after the database
backup is huge. Eg: normal transaction log backups are approx 100mb but the
abnormal one is approx 13gb. The database size is approx 20gb. I can shrink
the transaction log without problem but it expands again after the database
backup.
Does anyone have an explanation for this and is there a resolution?
Thanks
SteveB
My guess is that you either defrag the indexes and/or shrink the database files. Both of these operations are
logged in the transaction log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SteveB" <SteveB@.discussions.microsoft.com> wrote in message
news:4BA25F24-0289-4808-B351-D26E2CB6C988@.microsoft.com...
> On SQL Server 2000 I have DB maintenance plans to backup databases and
> transaction logs. The database is backed up daily at 02:00 hours and
> transaction logs backed up every 2 hours (excluding 02:00 hours).The problem
> I have is that the first backup of the transaction log after the database
> backup is huge. Eg: normal transaction log backups are approx 100mb but the
> abnormal one is approx 13gb. The database size is approx 20gb. I can shrink
> the transaction log without problem but it expands again after the database
> backup.
> Does anyone have an explanation for this and is there a resolution?
> Thanks
> SteveB
|||Your database maintenance plan not only backs up the database, but it can
also reindex all the indexes on the tables or shrink the data file. If you
have either of these last two options ('Reorganize data and index pages' and
'Remove unused space from database files' respectively) checked on the
second page of the maintenance plan wizard, you might want to uncheck them
and create a maintenance plan that runs on a less regular schedule, like
once a week.
Shrinking the database is not useful in a production environment in general,
so you can start with unchecking that if you have it checked and see what
the result is.
Reindexing is something you should do on a regular basis, but as a rough
guide, once a week is enough for most moderately busy systems.
Jacco Schalkwijk
SQL Server MVP
"SteveB" <SteveB@.discussions.microsoft.com> wrote in message
news:4BA25F24-0289-4808-B351-D26E2CB6C988@.microsoft.com...
> On SQL Server 2000 I have DB maintenance plans to backup databases and
> transaction logs. The database is backed up daily at 02:00 hours and
> transaction logs backed up every 2 hours (excluding 02:00 hours).The
> problem
> I have is that the first backup of the transaction log after the database
> backup is huge. Eg: normal transaction log backups are approx 100mb but
> the
> abnormal one is approx 13gb. The database size is approx 20gb. I can
> shrink
> the transaction log without problem but it expands again after the
> database
> backup.
> Does anyone have an explanation for this and is there a resolution?
> Thanks
> SteveB
|||In article <OjsO$X7fEHA.3928@.TK2MSFTNGP11.phx.gbl>,
jacco.please.reply@.to.newsgroups.mvps.org.invalid says...
> Your database maintenance plan not only backs up the database, but it can
> also reindex all the indexes on the tables or shrink the data file. If you
> have either of these last two options ('Reorganize data and index pages' and
> 'Remove unused space from database files' respectively) checked on the
> second page of the maintenance plan wizard, you might want to uncheck them
> and create a maintenance plan that runs on a less regular schedule, like
> once a week.
> Shrinking the database is not useful in a production environment in general,
> so you can start with unchecking that if you have it checked and see what
> the result is.
> Reindexing is something you should do on a regular basis, but as a rough
> guide, once a week is enough for most moderately busy systems.
What do you recommend for 24/7 systems? Just INDEXDEFRAG in place of
REINDEX?
|||24/7 you have little choice but to use INDEXDEFRAG. DBREINDEX takes out an
exclusive lock on the table it is reindexing and makes it unavailable to
other users. In contrast, INDEXDEFRAG uses short transactions and doesn't
make the table unavailable.
Jacco Schalkwijk
SQL Server MVP
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b84088229f26a90989695@.news...
> In article <OjsO$X7fEHA.3928@.TK2MSFTNGP11.phx.gbl>,
> jacco.please.reply@.to.newsgroups.mvps.org.invalid says...
> What do you recommend for 24/7 systems? Just INDEXDEFRAG in place of
> REINDEX?
|||Hi Guys
Thanks for the responses. I did have similar thoughts. I am doing some
re-indexing, but this takes place at midnight and completes well before the
backup at 02:00 so I would not expect a large log after the database backup.
Will do a trial run on test database with no maintenance activities tonite
and see what results.
"Tibor Karaszi" wrote:

> My guess is that you either defrag the indexes and/or shrink the database files. Both of these operations are
> logged in the transaction log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SteveB" <SteveB@.discussions.microsoft.com> wrote in message
> news:4BA25F24-0289-4808-B351-D26E2CB6C988@.microsoft.com...
>
>
|||The database and log backups are independent of each other. Backing up the
database doesn't have any effect the transaction log, so the extra
transaction log records that are created at midnight (after the 00:00
transaction log backup, I assume) are still there when you next backup the
transaction log at 04:00.
Jacco Schalkwijk
SQL Server MVP
"SteveB" <SteveB@.discussions.microsoft.com> wrote in message
news:413EC866-0D72-4FA1-8A83-A21DDB4656F3@.microsoft.com...[vbcol=seagreen]
> Hi Guys
> Thanks for the responses. I did have similar thoughts. I am doing some
> re-indexing, but this takes place at midnight and completes well before
> the
> backup at 02:00 so I would not expect a large log after the database
> backup.
> Will do a trial run on test database with no maintenance activities tonite
> and see what results.
> "Tibor Karaszi" wrote:
|||Jacco
Thanks for response. But my understanding was that the transaction log was
truncated after a full database backup. So I would expect the transaction
log to be truncated at 02:00 hours. Whilst I don't expect the physical size
of the transaction log to change I do expect the size of the transaction log
backup to be minimal for the 04:00 backup.
"Jacco Schalkwijk" wrote:

> The database and log backups are independent of each other. Backing up the
> database doesn't have any effect the transaction log, so the extra
> transaction log records that are created at midnight (after the 00:00
> transaction log backup, I assume) are still there when you next backup the
> transaction log at 04:00.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "SteveB" <SteveB@.discussions.microsoft.com> wrote in message
> news:413EC866-0D72-4FA1-8A83-A21DDB4656F3@.microsoft.com...
>
>
|||Steve,
I'm afraid your understanding of the matter was wrong. Try the following
script and see for yourself that the database backup doesn't truncate the
transaction log:
CREATE DATABASE steve
GO
ALTER DATABASE steve SET RECOVERY FULL
GO
USE steve
GO
BACKUP DATABASE steve TO DISK = 'steve.bak'
-- Need an initial backup to 'switch on' FULL RECOVERY
GO
DBCC SQLPERF ( LOGSPACE )
GO
SELECT s0.* INTO test_table
FROM sysobjects s0, sysobjects s1, sysobjects s2--, sysobjects s3
GO
DBCC SQLPERF ( LOGSPACE )
GO
BACKUP DATABASE steve TO DISK = 'steve.bak'
GO
DBCC SQLPERF ( LOGSPACE )
GO
USE master
GO
DROP DATABASE steve
Jacco Schalkwijk
SQL Server MVP
"SteveB" <SteveB@.discussions.microsoft.com> wrote in message
news:BC1602B4-DC3B-4E61-9477-7D61FE8055E7@.microsoft.com...[vbcol=seagreen]
> Jacco
> Thanks for response. But my understanding was that the transaction log was
> truncated after a full database backup. So I would expect the transaction
> log to be truncated at 02:00 hours. Whilst I don't expect the physical
> size
> of the transaction log to change I do expect the size of the transaction
> log
> backup to be minimal for the 04:00 backup.
> "Jacco Schalkwijk" wrote:

Large log backups

I noticed that my log backups are very very large after doing a full backup of my databases and was confused why that was.

I created 2 maintenance plans
- one for full backups and DB maintenance that runs nightly
- one for log backups that run at 4hr intervals

For whatever reason, the first transaction log backup that is performed after the full backup of the database is as large if not larger than my full backup, while subsequant log backups are more reasonable in size. Until the next full backup is performed, that is, then the next log backup is HUGE again...

Example:

05/04/2006 01:16 AM 35,801,484,800 TPRO_backup_200605032323.bak
05/04/2006 10:12 AM 31,236,731,392 TPRO_backup_200605040800.trn
05/04/2006 11:00 AM 159,274,496 TPRO_backup_200605041100.trn

No work is done in the database until after 8am, so nothing is going on in the database between the full backup (starts sometime after 10pm ) and the first transaction log backup (starts at 8AM), yet the first transaction log is nearly as large as the full backup that was done. Why?!? Sometimes the first log backup is even larger than the full backup.

If anyone can explain why this is the case and how I can fix it so that my log backups are more realistic, I would greatly appreciate it!

Thanks

Brandon

You mention DB maintenance that is run nightly but don't specify what that maintenance might be. If you're rebuilding indexes on all the tables it's quite conceivable your transaction logs are as large as the full backup file.|||

The order of tasks in the nightly maintenance job is as follows:

Check DB Integrity