Database Backups and Transaction Logs

Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks
You must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks
|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>
|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>
|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
was
>
|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set up
> that functionality as part of a job. I will add a nightly backup. We have to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> was
>

Database Backups and Transaction Logs

Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
ThanksYou must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
was[vbcol=seagreen]
>|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set u
p
> that functionality as part of a job. I will add a nightly backup. We have
to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> was
>

Database Backups and Transaction Logs

Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
ThanksYou must backup the log separately. Backing up the DB does NOT free up the
log. This is an online operation, so don't take the DB offline.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
Problem: Transaction log grew too big.
I took my database off line and then back online to ensure that no one was
connected to the database and then backed up the database.
I figured at this point I would be able to shrink the transaction log
because all of the 'pending' transactions would be clear - however I cannot.
Do I have to backup the transaction log? And in so doing will this solve my
problem?
Thanks|||Stephen,
Production database? If so, backup the transaction log then shrink the
transaction log file using DBCC SHRINKFILE statment.
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||Stephen,
Also see:
http://www.support.microsoft.com/?id=272318
HTH
Jerry
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Problem: Transaction log grew too big.
> I took my database off line and then back online to ensure that no one was
> connected to the database and then backed up the database.
> I figured at this point I would be able to shrink the transaction log
> because all of the 'pending' transactions would be clear - however I
> cannot.
> Do I have to backup the transaction log? And in so doing will this solve
> my
> problem?
> Thanks
>
>|||ok in the process of backing up the transaction log.
After that, I will run that command and then restrict the file growth.
Apparently the dba (which I am not nor am I a sql server dba) didn't set up
that functionality as part of a job. I will add a nightly backup. We have to
copy the database off that server to another box because there isn't
sufficient drive space available to do a backup.
Thanks for the quick response.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
> Stephen,
> Production database? If so, backup the transaction log then shrink the
> transaction log file using DBCC SHRINKFILE statment.
> HTH
> Jerry
> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
> > Problem: Transaction log grew too big.
> >
> > I took my database off line and then back online to ensure that no one
was
> > connected to the database and then backed up the database.
> >
> > I figured at this point I would be able to shrink the transaction log
> > because all of the 'pending' transactions would be clear - however I
> > cannot.
> >
> > Do I have to backup the transaction log? And in so doing will this solve
> > my
> > problem?
> >
> > Thanks
> >
> >
> >
>|||I suggest you read http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
news:emVLHa2zFHA.560@.TK2MSFTNGP12.phx.gbl...
> ok in the process of backing up the transaction log.
> After that, I will run that command and then restrict the file growth.
> Apparently the dba (which I am not nor am I a sql server dba) didn't set up
> that functionality as part of a job. I will add a nightly backup. We have to
> copy the database off that server to another box because there isn't
> sufficient drive space available to do a backup.
> Thanks for the quick response.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%232Cn$L2zFHA.720@.TK2MSFTNGP15.phx.gbl...
>> Stephen,
>> Production database? If so, backup the transaction log then shrink the
>> transaction log file using DBCC SHRINKFILE statment.
>> HTH
>> Jerry
>> "Stephen Costanzo" <sxcostanzo@.hotmail.com> wrote in message
>> news:%23CILEI2zFHA.3312@.TK2MSFTNGP09.phx.gbl...
>> > Problem: Transaction log grew too big.
>> >
>> > I took my database off line and then back online to ensure that no one
> was
>> > connected to the database and then backed up the database.
>> >
>> > I figured at this point I would be able to shrink the transaction log
>> > because all of the 'pending' transactions would be clear - however I
>> > cannot.
>> >
>> > Do I have to backup the transaction log? And in so doing will this solve
>> > my
>> > problem?
>> >
>> > Thanks
>> >
>> >
>> >
>>
>

Database backups

I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.
Tom,

> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restore
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:

> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>
|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model is
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Tom,
>
> - Full db backup (every night or once a week, depend the size of your db and
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say at
> noon. this will save time restoring the db because you do not have to restore
> all transaction log backups from the last full one, instead you restore the
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full,
> or from diff to diff if there is not a full between them, or between full to
> full if not diff), as many as you need to avoid it from growing and be save
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNCATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox should be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the exact same BACKUP
DATABASE command whether or not the checkbox is checked.

> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and also
http://www.karaszi.com/SQLServer/inf..._truncate.asp.

> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If you don't do log backups,
then run in simple recovery mode.

> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every ten minutes).
Or if you don't want to do log backups, put the db in simple recovery mode and only do db backups.
It is not very complicated. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gbl...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>

Database backups

I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.Tom,

> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restor
e
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:

> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time a
nd
> the log is not cleared for a long time and I do a restore and then play ba
ck
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model i
s
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Tom,
>
> - Full db backup (every night or once a week, depend the size of your db a
nd
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say a
t
> noon. this will save time restoring the db because you do not have to rest
ore
> all transaction log backups from the last full one, instead you restore th
e
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full
,
> or from diff to diff if there is not a full between them, or between full
to
> full if not diff), as many as you need to avoid it from growing and be sav
e
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
>|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNC
ATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox shou
ld be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the
exact same BACKUP
DATABASE command whether or not the checkbox is checked.

> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done
of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and als
o
http://www.karaszi.com/SQLServer/in...o_truncate.asp.

> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If y
ou don't do log backups,
then run in simple recovery mode.

> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every t
en minutes).
Or if you don't want to do log backups, put the db in simple recovery mode a
nd only do db backups.
It is not very complicated. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gb
l...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time a
nd
> the log is not cleared for a long time and I do a restore and then play ba
ck
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>sql

Database backups

I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.Tom,
> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restore
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model is
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
> Tom,
> > How should I do
> > backups. Should I backup the log first clear it then backup the full
> > database or db first then log? I am confused. Thanks.
> - Full db backup (every night or once a week, depend the size of your db and
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say at
> noon. this will save time restoring the db because you do not have to restore
> all transaction log backups from the last full one, instead you restore the
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full,
> or from diff to diff if there is not a full between them, or between full to
> full if not diff), as many as you need to avoid it from growing and be save
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
> > I have some confusion about backing up databases and transaction logs.
> > When I do a complete database backup it backs up the db and the log, but
> > when I have it set to clear out inactive entries in the log it does not.
> > When I do a log backup and set it to clear out inactive entries it does do
> > that. My question is then if I do complete database backups all the time and
> > the log is not cleared for a long time and I do a restore and then play back
> > the log from a long time ago will I corrupt the database? How should I do
> > backups. Should I backup the log first clear it then backup the full
> > database or db first then log? I am confused. Thanks.
> >
> >
> >|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNCATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox should be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the exact same BACKUP
DATABASE command whether or not the checkbox is checked.
> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and also
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp.
> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If you don't do log backups,
then run in simple recovery mode.
> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every ten minutes).
Or if you don't want to do log backups, put the db in simple recovery mode and only do db backups.
It is not very complicated. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gbl...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>

Database backup strategy questions....

I have been reading about the SQL Server transaction log and I'm still
confused about a lot of things. First of all, what is this file
actually used for? In the past when backing up my database, I only
backup and restore the actual database and skip the log file and this
has worked just fine in getting all my data back. Why do I have to
backup the log file and when would I ever use it?
I am having a problem now becuase my log file is over 4 GB for a 50 MB
database and I'm not sure why. My application is a POS system where
hundreds of transactions are entered daily and backups are done each
night of the database but not the log. I can restore the database just
fine without the log file, what is the advantage of backing up the log
file also? I can't backup that much data so I'll need to make that
much smaller if I did back it up. Based on my needs what is the best
backup strategy and what would I lose by not backing up the log?
Thanks.Ray,
When you restore the database file from a Full backup it actually restores
the log file along with it. The Log file holds the transactional
information to ensure consistency within the db and can not be used without
it. The log backups allow you to potentially restore to a particular point
in time under thright conditions. If all you care about is being able to
restore to the last Full backup (potentially loosing all transactions since
that last backup) you should place the database in SIMPLE recovery mode and
it will automatically truncate the log and not grow. See here for more
details:
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Andrew J. Kelly SQL MVP
"Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
news:d8653140.0401131950.120fda92@.posting.google.com...

quote:

---

> I have been reading about the SQL Server transaction log and I'm still
> confused about a lot of things. First of all, what is this file
> actually used for? In the past when backing up my database, I only
> backup and restore the actual database and skip the log file and this
> has worked just fine in getting all my data back. Why do I have to
> backup the log file and when would I ever use it?
> I am having a problem now becuase my log file is over 4 GB for a 50 MB
> database and I'm not sure why. My application is a POS system where
> hundreds of transactions are entered daily and backups are done each
> night of the database but not the log. I can restore the database just
> fine without the log file, what is the advantage of backing up the log
> file also? I can't backup that much data so I'll need to make that
> much smaller if I did back it up. Based on my needs what is the best
> backup strategy and what would I lose by not backing up the log?
> Thanks.

---

sql

Database backup strategy questions....

I have been reading about the SQL Server transaction log and I'm still
confused about a lot of things. First of all, what is this file
actually used for? In the past when backing up my database, I only
backup and restore the actual database and skip the log file and this
has worked just fine in getting all my data back. Why do I have to
backup the log file and when would I ever use it?
I am having a problem now becuase my log file is over 4 GB for a 50 MB
database and I'm not sure why. My application is a POS system where
hundreds of transactions are entered daily and backups are done each
night of the database but not the log. I can restore the database just
fine without the log file, what is the advantage of backing up the log
file also? I can't backup that much data so I'll need to make that
much smaller if I did back it up. Based on my needs what is the best
backup strategy and what would I lose by not backing up the log?
Thanks.Ray,
When you restore the database file from a Full backup it actually restores
the log file along with it. The Log file holds the transactional
information to ensure consistency within the db and can not be used without
it. The log backups allow you to potentially restore to a particular point
in time under thright conditions. If all you care about is being able to
restore to the last Full backup (potentially loosing all transactions since
that last backup) you should place the database in SIMPLE recovery mode and
it will automatically truncate the log and not grow. See here for more
details:
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Andrew J. Kelly SQL MVP
"Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
news:d8653140.0401131950.120fda92@.posting.google.com...
> I have been reading about the SQL Server transaction log and I'm still
> confused about a lot of things. First of all, what is this file
> actually used for? In the past when backing up my database, I only
> backup and restore the actual database and skip the log file and this
> has worked just fine in getting all my data back. Why do I have to
> backup the log file and when would I ever use it?
> I am having a problem now becuase my log file is over 4 GB for a 50 MB
> database and I'm not sure why. My application is a POS system where
> hundreds of transactions are entered daily and backups are done each
> night of the database but not the log. I can restore the database just
> fine without the log file, what is the advantage of backing up the log
> file also? I can't backup that much data so I'll need to make that
> much smaller if I did back it up. Based on my needs what is the best
> backup strategy and what would I lose by not backing up the log?
> Thanks.

database backup not clearing the transaction log...

Hi
I have a 150Gb 'time series' type database running in SQL 2000. every sunday
@. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
creates a big fat transaction log.
At 0030 Monday morning the database is backed up using
BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD ,
NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
NOFORMAT
which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is only
cleared at 0800 Monday morning when the trans log is backed up (and is
continually backed up every 10 minutes throughout the working day all week
until Saturday) using the following syntax:
surely the database backup at 0030 should clear down the size of the trans
log?

> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
Sure , it does not. You need to BACKUP LOG file in order to truncate
inactive portions in the LOG
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:11993BB5-D220-4AFF-99D1-F0C7FAE045F6@.microsoft.com...
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every
> sunday
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD
> ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>
|||Methodology wrote:
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every sunday
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>
A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
transaction log, only a log backup will do that (assuming Full or
Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
of the log file will not change when truncating. Shrinking is a
separate process, and there are many reasons why you don't want to do it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Here is an idea. I did this once at one job I had. I had a similarlarly
large sized databases whose transaction logs "blew out" on a reindex
(weekly). I had a lot of ugly clustered indexes that had to be rebuilt
and took a lot of space. SInce the database was not heavily used during
that period, I did an alter database and put the db on simple mode.
Then it won't blow out the log. I did the re-index on all necessary
tables and then did a full backup. Note, this is dangerous as you break
the transaction log sequence at that time, so you must do an immediate
full backup. In my case, this was ok as it was a mainly Mon-Sat db.
KR
Tracy McKibben wrote:
> Methodology wrote:
> A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
> transaction log, only a log backup will do that (assuming Full or
> Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
> of the log file will not change when truncating. Shrinking is a
> separate process, and there are many reasons why you don't want to do it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
sql

database backup not clearing the transaction log...

Hi
I have a 150Gb 'time series' type database running in SQL 2000. every sunday
@. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
creates a big fat transaction log.
At 0030 Monday morning the database is backed up using
BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NO
UNLOAD ,
NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
NOFORMAT
which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is only
cleared at 0800 Monday morning when the trans log is backed up (and is
continually backed up every 10 minutes throughout the working day all week
until Saturday) using the following syntax:
surely the database backup at 0030 should clear down the size of the trans
log?
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
Sure , it does not. You need to BACKUP LOG file in order to truncate
inactive portions in the LOG
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:11993BB5-D220-4AFF-99D1-F0C7FAE045F6@.microsoft.com...
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every
> sunday
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT ,
NOUNLOAD
> ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>|||Methodology wrote:
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every sund
ay
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT ,
NOUNLOAD ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is onl
y
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>
A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
transaction log, only a log backup will do that (assuming Full or
Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
of the log file will not change when truncating. Shrinking is a
separate process, and there are many reasons why you don't want to do it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Here is an idea. I did this once at one job I had. I had a similarlarly
large sized databases whose transaction logs "blew out" on a reindex
(weekly). I had a lot of ugly clustered indexes that had to be rebuilt
and took a lot of space. SInce the database was not heavily used during
that period, I did an alter database and put the db on simple mode.
Then it won't blow out the log. I did the re-index on all necessary
tables and then did a full backup. Note, this is dangerous as you break
the transaction log sequence at that time, so you must do an immediate
full backup. In my case, this was ok as it was a mainly Mon-Sat db.
KR
Tracy McKibben wrote:
> Methodology wrote:
> A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
> transaction log, only a log backup will do that (assuming Full or
> Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
> of the log file will not change when truncating. Shrinking is a
> separate process, and there are many reasons why you don't want to do it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Database Backup in 0.5 B and Transaction log backup in GBs

SQL gurus,
Simple question:
Our database is 470MB and transaction log has become 18GB! (Clustered). I
see local Fixed drives in both the SQL boxes from 'System Information' and
disk manager where data is being stored.
We are backing up database once in 24 hours and transaction logs every two
hours during business hours and full transaction log at night.
Simple Question is if we are backing full transaction log at night then
isn't supposed to make the transaction log to "zero" bytes or say clear the
transaction log automatically? After all when transaction logs have been
backup then why the transaction log continue to grow'
If the database is crashed what would be the sequence to recover:
Restore database in restore mode then restore first transaction log backup
taken after full backup of database and then restore the next backup taken
of transaction log and so on. (to apply in sequence starting from the first
one backup of transaction log taken).
Now question is :
Situation #1
1) if database is crashed and we still have transaction logs then can we
restore transaction logs (not backup of transaction logs) at the end of
procedure to restore as given above'
Situation #2
2) database is crashed and we do not have transaction logs then we can
restore as given in the procedure above except that in the last stage we
keep "no restore mode"'
Thanks
MeiNarendra
All of these issues are described on BOL very well.
Please refer to BOL.
"Narendra Talreja" <ntalreja@.no_spam_comcast.net> wrote in message
news:XpOcnbaESOUAmhiiXTWJhQ@.comcast.com...
> SQL gurus,
> Simple question:
> Our database is 470MB and transaction log has become 18GB! (Clustered). I
> see local Fixed drives in both the SQL boxes from 'System Information' and
> disk manager where data is being stored.
> We are backing up database once in 24 hours and transaction logs every two
> hours during business hours and full transaction log at night.
> Simple Question is if we are backing full transaction log at night then
> isn't supposed to make the transaction log to "zero" bytes or say clear
the
> transaction log automatically? After all when transaction logs have been
> backup then why the transaction log continue to grow'
> If the database is crashed what would be the sequence to recover:
> Restore database in restore mode then restore first transaction log backup
> taken after full backup of database and then restore the next backup taken
> of transaction log and so on. (to apply in sequence starting from the
first
> one backup of transaction log taken).
> Now question is :
> Situation #1
> 1) if database is crashed and we still have transaction logs then can we
> restore transaction logs (not backup of transaction logs) at the end of
> procedure to restore as given above'
> Situation #2
> 2) database is crashed and we do not have transaction logs then we can
> restore as given in the procedure above except that in the last stage we
> keep "no restore mode"'
> Thanks
> Mei
>|||see inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Narendra Talreja" <ntalreja@.no_spam_comcast.net> wrote in message
news:XpOcnbaESOUAmhiiXTWJhQ@.comcast.com...
> SQL gurus,
> Simple question:
> Our database is 470MB and transaction log has become 18GB! (Clustered). I
> see local Fixed drives in both the SQL boxes from 'System Information' and
> disk manager where data is being stored.
> We are backing up database once in 24 hours and transaction logs every two
> hours during business hours and full transaction log at night.
> Simple Question is if we are backing full transaction log at night then
> isn't supposed to make the transaction log to "zero" bytes or say clear
the
> transaction log automatically?
Backing up the log does truncate the log( making space available for
re-use), but it does not shrink the log. Long running transactions can cause
the log to grow larger than you intended. After backing up the log, you may
use dbcc shrinkfile on the log file to reduce its physical size.
After all when transaction logs have been
> backup then why the transaction log continue to grow'
The only explanation I can think of is long running transactions...
> If the database is crashed what would be the sequence to recover:
>
set the db to dbo use only and kick off all users
backup the log with truncate_only
Restore the last whole data backup
restore each transaction log in the proper sequence.
> Restore database in restore mode then restore first transaction log backup
> taken after full backup of database and then restore the next backup taken
> of transaction log and so on. (to apply in sequence starting from the
first
> one backup of transaction log taken).
> Now question is :
> Situation #1
> 1) if database is crashed and we still have transaction logs then can we
> restore transaction logs (not backup of transaction logs) at the end of
> procedure to restore as given above'
No, restore the log backups
> Situation #2
> 2) database is crashed and we do not have transaction logs then we can
> restore as given in the procedure above except that in the last stage we
> keep "no restore mode"'
Yes, you may restore the whole database backup and allow recovery to run.
The database will be available, but you will have lost all of the data which
was in the transaction logs which you did not restore.
> Thanks
> Mei
>

Database Backup has stopped working. Cannot tell why

Hi,

I had a Database maintenance plan setup to do complete backup of my SQL Server 2000 database. Same thing was done for the transaction logs as well. And they had resulted in successful backups sometime ago.

But I have noticed that Backups are no longer happening. I cannot find the backup files where they are supposed to land. Some how, I cannot find any error messages relating why the backups are not getting created.

I do not know where I can look up the logs/reports of what possibly is going wrong. I have looked at the usual places and they are not there, for the times I have deliberately tried to submit the jobs.

Any help will be appreciated.

Thanks

Sam

Are the maint. plan jobs erroring? A db maint plan is fired off by a SQL Server Job. You should be able to look at the history of the job for a description of the failure. If there is no failure, check to make sure the job is enabled. If it is enabled, try running it by hand to see if the files are created.
Tim|||Open up your SQL Server error log. Are you actually getting messages in the log that databases are either backing up or are failing?

Database Backup And Transaction Log - SQL Server 2000

I would like to find the cleanest way to do a backup and not have to backup
the log.
I have a number of DTS jobs that run as batch - loads data into SQL Server.
I know for fact that at a certain time after the batch, no one is in the
database and no jobs are running that affect the database of system
databases. I was thinking of just executing a CHECKPOINT to force any
completed records to disc, backing up the database, and then truncating the
log. Is this the correct order? I became when I saw an article that
did a backup, then checkpoint and then truncate log - in that condition the
backup would never cover the CHECKPOINTED data.
Any suggestions would be helpful. Database has full recovery mode.You will have to backup the log - or take the risk of going with the Simple
recovery model.
If you like, you can backup the log just prior to the start of the DTS jobs.
This will ensure that you have as much space as possible during the DTS run.
If your packages load a lot of data, you can backup the log more frequently
during the run than you would at other times. After the DTS run is done,
that would be a good time to schedule your full backup.
Another thing you may want to consider is to switch your recovery model to
Bulk-logged during the running of the DTS package(s).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"MG" <MG@.discussions.microsoft.com> wrote in message
news:5A388DAD-5C6A-414C-946E-DEBA3922A769@.microsoft.com...
I would like to find the cleanest way to do a backup and not have to backup
the log.
I have a number of DTS jobs that run as batch - loads data into SQL Server.
I know for fact that at a certain time after the batch, no one is in the
database and no jobs are running that affect the database of system
databases. I was thinking of just executing a CHECKPOINT to force any
completed records to disc, backing up the database, and then truncating the
log. Is this the correct order? I became when I saw an article that
did a backup, then checkpoint and then truncate log - in that condition the
backup would never cover the CHECKPOINTED data.
Any suggestions would be helpful. Database has full recovery mode.|||
When you backup the database and the log then you will be able to shrink the
log file. If you do not backup the log then you cannot shrink therefore the
log file could get very big. I have a feeling you already know that.
"MG" <MG@.discussions.microsoft.com> wrote in message
news:5A388DAD-5C6A-414C-946E-DEBA3922A769@.microsoft.com...
>I would like to find the cleanest way to do a backup and not have to backup
> the log.
> I have a number of DTS jobs that run as batch - loads data into SQL
> Server.
> I know for fact that at a certain time after the batch, no one is in the
> database and no jobs are running that affect the database of system
> databases. I was thinking of just executing a CHECKPOINT to force any
> completed records to disc, backing up the database, and then truncating
> the
> log. Is this the correct order? I became when I saw an article
> that
> did a backup, then checkpoint and then truncate log - in that condition
> the
> backup would never cover the CHECKPOINTED data.
> Any suggestions would be helpful. Database has full recovery mode.|||Tom, Thanks for the info but I am not sure why my concept would not work.
Simply CHECKPOINT the trans log to get what is left in the trans log to the
mdf, truncate the log, then do a database backup (without backingup the tran
s
log). Why would this not work or be affective?
"Tom Moreau" wrote:

> You will have to backup the log - or take the risk of going with the Simpl
e
> recovery model.
> If you like, you can backup the log just prior to the start of the DTS job
s.
> This will ensure that you have as much space as possible during the DTS ru
n.
> If your packages load a lot of data, you can backup the log more frequentl
y
> during the run than you would at other times. After the DTS run is done,
> that would be a good time to schedule your full backup.
> Another thing you may want to consider is to switch your recovery model to
> Bulk-logged during the running of the DTS package(s).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "MG" <MG@.discussions.microsoft.com> wrote in message
> news:5A388DAD-5C6A-414C-946E-DEBA3922A769@.microsoft.com...
> I would like to find the cleanest way to do a backup and not have to backu
p
> the log.
> I have a number of DTS jobs that run as batch - loads data into SQL Server
.
> I know for fact that at a certain time after the batch, no one is in the
> database and no jobs are running that affect the database of system
> databases. I was thinking of just executing a CHECKPOINT to force any
> completed records to disc, backing up the database, and then truncating th
e
> log. Is this the correct order? I became when I saw an article th
at
> did a backup, then checkpoint and then truncate log - in that condition th
e
> backup would never cover the CHECKPOINTED data.
> Any suggestions would be helpful. Database has full recovery mode.
>|||Executing the CHECKPOINT command will not in any way affect what will be inc
luded on your backups.
(SQL Server will do an internal CHECKPOINT with the backup execution anyhow,
but that is beside the
point.) Backup database will include all the data in a consistent state. Wha
t haven't been committed
will be restored when you do RESTORE DATABASE.
Also, "truncating the log" isn't considered good practice. Either you do reg
ular log backups, and
the log file will be emptied each time you do a log backup. By truncating th
e log, you break that
chain of log backups. Or, you don't do log backups, and all you have to do i
s to have the database
in simple recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MG" <MG@.discussions.microsoft.com> wrote in message
news:94AD791B-2749-4B4E-BCF6-82A32073D8A0@.microsoft.com...
> Tom, Thanks for the info but I am not sure why my concept would not work.
> Simply CHECKPOINT the trans log to get what is left in the trans log to th
e
> mdf, truncate the log, then do a database backup (without backingup the tr
ans
> log). Why would this not work or be affective?
> "Tom Moreau" wrote:
>|||Tibor,
Thanks for clearing that up. I did not know that when a backup occurs that
SQL does a checkpoint. Now it all fits. Is the Checkpoint done with the
database backup or the trans log backup?
"Tibor Karaszi" wrote:

> Executing the CHECKPOINT command will not in any way affect what will be i
ncluded on your backups.
> (SQL Server will do an internal CHECKPOINT with the backup execution anyho
w, but that is beside the
> point.) Backup database will include all the data in a consistent state. W
hat haven't been committed
> will be restored when you do RESTORE DATABASE.
> Also, "truncating the log" isn't considered good practice. Either you do r
egular log backups, and
> the log file will be emptied each time you do a log backup. By truncating
the log, you break that
> chain of log backups. Or, you don't do log backups, and all you have to do
is to have the database
> in simple recovery mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MG" <MG@.discussions.microsoft.com> wrote in message
> news:94AD791B-2749-4B4E-BCF6-82A32073D8A0@.microsoft.com...
>
>|||I know for sure it is done with a database backup. Whether it is performed w
ith a log backup, I
don't know, but to me it doesn't matter as all I need to know is that the re
levant log records are
recorded in the backup media. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MG" <MG@.discussions.microsoft.com> wrote in message
news:6D13A79A-51F0-4C33-95C2-C992B0ADE61E@.microsoft.com...
> Tibor,
> Thanks for clearing that up. I did not know that when a backup occurs that
> SQL does a checkpoint. Now it all fits. Is the Checkpoint done with the
> database backup or the trans log backup?
> "Tibor Karaszi" wrote:
>|||Tibor,
I agree. thanks
"Tibor Karaszi" wrote:

> I know for sure it is done with a database backup. Whether it is performed
with a log backup, I
> don't know, but to me it doesn't matter as all I need to know is that the
relevant log records are
> recorded in the backup media. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MG" <MG@.discussions.microsoft.com> wrote in message
> news:6D13A79A-51F0-4C33-95C2-C992B0ADE61E@.microsoft.com...
>
>sql

Database backup

In EM, I can only configure Database Maintenance Plan by full backup and
transaction log.
How do I set differential/incremental backup ?Differential database backups are not implemented in Database Maintenance
Plan. You will have to create your own jobs using the BACKUP and RESTORE
commands. More information about these commands and lots of examples are
available in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:umvDl9BWEHA.1380@.TK2MSFTNGP12.phx.gbl...
In EM, I can only configure Database Maintenance Plan by full backup and
transaction log.
How do I set differential/incremental backup ?|||Hi,
I hope that you can only do FULL and Transaction Log backups by the
maintenance plan.
But you can generate a SQL job to do this. Just right click on the database
in the EM, Select the database, All tasks, backup databse, choose
differential backup, and then choose "Schedule" where you can
make it as a recurring schedule.
--
Thanks
Hari
MCDBA
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:umvDl9BWEHA.1380@.TK2MSFTNGP12.phx.gbl...
> In EM, I can only configure Database Maintenance Plan by full backup and
> transaction log.
> How do I set differential/incremental backup ?
>

Database and transaction log question

New to SQL. I have 2 drives mirrored for os and sql installation files and a
raid 5 array for the database files for sql.
Should I create two partitions on the raid 5 array and put the database file
s on one and the transaction logs on another or is it not going to make a di
fference in performance since both partitions would still be on the same rai
d 5 array. Appreciate a qui
ck response to this question.
Also if it is recommended to place the transaction logs on a separate partit
ion on the same raid 5 array. Where do I set that in sql. Thanks a lot every
one.
OwenHaving multiple partitions on the same RAID5 array isn't going to buy you
anything. Put the logs on their own RAID1 volume. If you have the disks,
place the data files on a RAID10 volume. If you don't have the disks, then
put the data files on a RAID5. Never put the logs on the same array as the
data files.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"owen" <anonymous@.discussions.microsoft.com> wrote in message
news:5C7163A9-C709-4466-A823-3DCA707A5912@.microsoft.com...
New to SQL. I have 2 drives mirrored for os and sql installation files and a
raid 5 array for the database files for sql.
Should I create two partitions on the raid 5 array and put the database
files on one and the transaction logs on another or is it not going to make
a difference in performance since both partitions would still be on the same
raid 5 array. Appreciate a quick response to this question.
Also if it is recommended to place the transaction logs on a separate
partition on the same raid 5 array. Where do I set that in sql. Thanks a lot
everyone.
Owen|||"owen" <anonymous@.discussions.microsoft.com> wrote in message
news:5C7163A9-C709-4466-A823-3DCA707A5912@.microsoft.com...
> New to SQL. I have 2 drives mirrored for os and sql installation files and
a raid 5 array for the database files for sql.
> Should I create two partitions on the raid 5 array and put the database
files on one and the transaction logs on another or is it not going to make
a difference in performance since both partitions would still be on the same
raid 5 array. Appreciate a quick response to this question.
> Also if it is recommended to place the transaction logs on a separate
partition on the same raid 5 array. Where do I set that in sql. Thanks a lot
everyone.
> Owen
Creating 2 partitions on a RAID 5 is going to give you no performance
advantage at all. In fact, though log files will benefit from the fault
tolerance afforded by RAID 5, as they are only written to, rather than read
from, they will not benefit from read performance advantages that RAID 5
gives you.
If your main concern is fault tolerance then stick the logs on the mirror.
That way if your entire RAID 5 controller goes up the Swanny, you still have
your log files.
If you are going to reposition your fils, simply go into Enterprise Manager,
properties of the database and change it there
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004

database and tlog backups - MSSQL 2000 & MSSQL 2005

hi all, as we have big problems with sql timeouts and deadlocks during
the backup of the databases and transaction log, i am searching for a
possibiliy to give the backup procedure a lower priority, so that the
sql requests falling into that period don't fail anymore. is there a
way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
another/better solution to avoid such problems! thx in advance!
You can't assign priorities in any sql server version prior to 2008.
What you should do is attempt to find out WHY you are getting timeouts and
address that issue. Without further information, my first guess would be
I/O delays because you are backing up to the same drive system as the data
resides on. If this is the case you could try backing up to another disk
subsystem. NAS devices or iSCSI SANs are very effective for this and quite
inexpensive these days. Heck, for smallish dbs even an external USB 2 drive
enclosure could be beneficial.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!
|||I agree with Kevin in that it sounds like you have I/O issues to begin with.
You must also already have deadlock issues and the backups just make it
worse. I also agree with Kevin in that it sounds like you are backing up to
the same physical drive as the data or log files are on and this should be
changed immediately. A well configured system should only see a few % in
overhead when backing up. This depends on many factors but the process
itself is not that intensive that it should cause issues like you report
unless there are issues already.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!
|||I should have mentioned that you can evaluate I/O performance using
performance monitor (various physical disk counters including avg disk queue
length, avg sec/read avg sec/write). You can also evaluate wait stats using
dbcc sqlperf(waitstats) in 2000 or the sys.dm_os_wait_stats DMV in 2005.
Microsoft has very good documentation on what the waits mean. See BOL and
search for "sql server wait stats" or get this doc for 2005:
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kdt7a10ahahec@.corp.supernews.com...
> You can't assign priorities in any sql server version prior to 2008.
> What you should do is attempt to find out WHY you are getting timeouts and
> address that issue. Without further information, my first guess would be
> I/O delays because you are backing up to the same drive system as the data
> resides on. If this is the case you could try backing up to another disk
> subsystem. NAS devices or iSCSI SANs are very effective for this and
> quite inexpensive these days. Heck, for smallish dbs even an external USB
> 2 drive enclosure could be beneficial.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
>
|||Dear Kelly,
The strange thing is, that the opposite is the case. We are running 2
backups each day, a nightly one to the same hard drive and a daily one
to a different drive. The backup on the same drive causing much less
problems - however this backup also contains less data than the one
during the day. We already spent a lot of time in query optimization
and are aware that the database I/O sometimes run on the physical
limits, so we don't expect significant improvements by investing more
time on improving the queries and the indexes. That's why we hoped for
somekind of "on idle" backup option to start the backup on low prio.
Maybe you can give some advice on the sequence of the backup itself?
The sequence we use is to check db integrity first, then backup
database and finally save tlog. I wonder if the correct sequence in
sense of a full db backup must be to first save the tlog and then the
database? (In regards to performance / Timeouts this probably doesn't
matter at all I guess?)
|||See in-line:
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:039df945-a971-4257-9eb8-64d119c0bc97@.r60g2000hsc.googlegroups.com...
> Dear Kelly,
> The strange thing is, that the opposite is the case. We are running 2
> backups each day, a nightly one to the same hard drive and a daily one
> to a different drive. The backup on the same drive causing much less
> problems - however this backup also contains less data than the one
> during the day.
What are these backups? Are they both FULL backups? Why would one have less
data than the other one? Why would you backup to the same disk as the data
in the first place? If the drive fails you loose the data and the backups.
Backing up across the network can be slower than backing up to disk even if
it is on the same drive.

> We already spent a lot of time in query optimization
> and are aware that the database I/O sometimes run on the physical
> limits, so we don't expect significant improvements by investing more
> time on improving the queries and the indexes.
If the database and queries are alreay highly tuned and you still run into
I/O issues you simply don't have the proper configuration to do the job.
There is no getting around that and there are no magical cures. You need to
upgrade the hardware.

>That's why we hoped for
> somekind of "on idle" backup option to start the backup on low prio.
> Maybe you can give some advice on the sequence of the backup itself?
> The sequence we use is to check db integrity first, then backup
> database and finally save tlog. I wonder if the correct sequence in
> sense of a full db backup must be to first save the tlog and then the
> database? (In regards to performance / Timeouts this probably doesn't
> matter at all I guess?)
I bet it is more the Integrity checks that do the damage more than the
backups. Do you only backup the log once a day? Log backups should be
independent of the FULL backups and done frequently. On average they are run
every five to fifteen minutes. Doing a log backup directly before or after a
FULL backup is pretty much useless.
|||Dear Andrew,
The nighly backup contains only around 50% of the data because
recurrung clean-up jobs are running just before the backup. While the
daily backup is just a local one, the nightly one is a remote one.
However, I'll pick up your recommendation to backup the tlog more
frequently and to apply the integrity check only for the nightly
backup. Hope that helps. Thanks so far!
|||Well that explains some of it. If you delete half the rows in the db the
transaction log backup will be huge. You might want to see if you can run
the cleanup jobs more often to spread the load over the entire day and keep
the logs to a smaller size.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
> Dear Andrew,
> The nighly backup contains only around 50% of the data because
> recurrung clean-up jobs are running just before the backup. While the
> daily backup is just a local one, the nightly one is a remote one.
> However, I'll pick up your recommendation to backup the tlog more
> frequently and to apply the integrity check only for the nightly
> backup. Hope that helps. Thanks so far!
|||Or possibly interleave tlog backups within the cleanup job itself. Delete
some fixed number of rows, backup tran, repeat until no rows to delete.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ummehUQMIHA.748@.TK2MSFTNGP04.phx.gbl...
> Well that explains some of it. If you delete half the rows in the db the
> transaction log backup will be huge. You might want to see if you can run
> the cleanup jobs more often to spread the load over the entire day and
> keep the logs to a smaller size.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
>

database and tlog backups - MSSQL 2000 & MSSQL 2005

hi all, as we have big problems with sql timeouts and deadlocks during
the backup of the databases and transaction log, i am searching for a
possibiliy to give the backup procedure a lower priority, so that the
sql requests falling into that period don't fail anymore. is there a
way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
another/better solution to avoid such problems! thx in advance!You can't assign priorities in any sql server version prior to 2008.
What you should do is attempt to find out WHY you are getting timeouts and
address that issue. Without further information, my first guess would be
I/O delays because you are backing up to the same drive system as the data
resides on. If this is the case you could try backing up to another disk
subsystem. NAS devices or iSCSI SANs are very effective for this and quite
inexpensive these days. Heck, for smallish dbs even an external USB 2 drive
enclosure could be beneficial.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!|||I agree with Kevin in that it sounds like you have I/O issues to begin with.
You must also already have deadlock issues and the backups just make it
worse. I also agree with Kevin in that it sounds like you are backing up to
the same physical drive as the data or log files are on and this should be
changed immediately. A well configured system should only see a few % in
overhead when backing up. This depends on many factors but the process
itself is not that intensive that it should cause issues like you report
unless there are issues already.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!|||I should have mentioned that you can evaluate I/O performance using
performance monitor (various physical disk counters including avg disk queue
length, avg sec/read avg sec/write). You can also evaluate wait stats using
dbcc sqlperf(waitstats) in 2000 or the sys.dm_os_wait_stats DMV in 2005.
Microsoft has very good documentation on what the waits mean. See BOL and
search for "sql server wait stats" or get this doc for 2005:
Performance_Tuning_Waits_Queues.doc" target="_blank">http://download.microsoft.com/downl...aits_Queues.doc
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kdt7a10ahahec@.corp.supernews.com...
> You can't assign priorities in any sql server version prior to 2008.
> What you should do is attempt to find out WHY you are getting timeouts and
> address that issue. Without further information, my first guess would be
> I/O delays because you are backing up to the same drive system as the data
> resides on. If this is the case you could try backing up to another disk
> subsystem. NAS devices or iSCSI SANs are very effective for this and
> quite inexpensive these days. Heck, for smallish dbs even an external USB
> 2 drive enclosure could be beneficial.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
>|||Dear Kelly,
The strange thing is, that the opposite is the case. We are running 2
backups each day, a nightly one to the same hard drive and a daily one
to a different drive. The backup on the same drive causing much less
problems - however this backup also contains less data than the one
during the day. We already spent a lot of time in query optimization
and are aware that the database I/O sometimes run on the physical
limits, so we don't expect significant improvements by investing more
time on improving the queries and the indexes. That's why we hoped for
somekind of "on idle" backup option to start the backup on low prio.
Maybe you can give some advice on the sequence of the backup itself?
The sequence we use is to check db integrity first, then backup
database and finally save tlog. I wonder if the correct sequence in
sense of a full db backup must be to first save the tlog and then the
database? (In regards to performance / Timeouts this probably doesn't
matter at all I guess?)|||See in-line:
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:039df945-a971-4257-9eb8-64d119c0bc97@.r60g2000hsc.googlegroups.com...
> Dear Kelly,
> The strange thing is, that the opposite is the case. We are running 2
> backups each day, a nightly one to the same hard drive and a daily one
> to a different drive. The backup on the same drive causing much less
> problems - however this backup also contains less data than the one
> during the day.
What are these backups? Are they both FULL backups? Why would one have less
data than the other one? Why would you backup to the same disk as the data
in the first place? If the drive fails you loose the data and the backups.
Backing up across the network can be slower than backing up to disk even if
it is on the same drive.

> We already spent a lot of time in query optimization
> and are aware that the database I/O sometimes run on the physical
> limits, so we don't expect significant improvements by investing more
> time on improving the queries and the indexes.
If the database and queries are alreay highly tuned and you still run into
I/O issues you simply don't have the proper configuration to do the job.
There is no getting around that and there are no magical cures. You need to
upgrade the hardware.

>That's why we hoped for
> somekind of "on idle" backup option to start the backup on low prio.
> Maybe you can give some advice on the sequence of the backup itself?
> The sequence we use is to check db integrity first, then backup
> database and finally save tlog. I wonder if the correct sequence in
> sense of a full db backup must be to first save the tlog and then the
> database? (In regards to performance / Timeouts this probably doesn't
> matter at all I guess?)
I bet it is more the Integrity checks that do the damage more than the
backups. Do you only backup the log once a day? Log backups should be
independent of the FULL backups and done frequently. On average they are run
every five to fifteen minutes. Doing a log backup directly before or after a
FULL backup is pretty much useless.|||Dear Andrew,
The nighly backup contains only around 50% of the data because
recurrung clean-up jobs are running just before the backup. While the
daily backup is just a local one, the nightly one is a remote one.
However, I'll pick up your recommendation to backup the tlog more
frequently and to apply the integrity check only for the nightly
backup. Hope that helps. Thanks so far!|||Well that explains some of it. If you delete half the rows in the db the
transaction log backup will be huge. You might want to see if you can run
the cleanup jobs more often to spread the load over the entire day and keep
the logs to a smaller size.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
> Dear Andrew,
> The nighly backup contains only around 50% of the data because
> recurrung clean-up jobs are running just before the backup. While the
> daily backup is just a local one, the nightly one is a remote one.
> However, I'll pick up your recommendation to backup the tlog more
> frequently and to apply the integrity check only for the nightly
> backup. Hope that helps. Thanks so far!|||Or possibly interleave tlog backups within the cleanup job itself. Delete
some fixed number of rows, backup tran, repeat until no rows to delete.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ummehUQMIHA.748@.TK2MSFTNGP04.phx.gbl...
> Well that explains some of it. If you delete half the rows in the db the
> transaction log backup will be huge. You might want to see if you can run
> the cleanup jobs more often to spread the load over the entire day and
> keep the logs to a smaller size.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
>

database and tlog backups - MSSQL 2000 & MSSQL 2005

hi all, as we have big problems with sql timeouts and deadlocks during
the backup of the databases and transaction log, i am searching for a
possibiliy to give the backup procedure a lower priority, so that the
sql requests falling into that period don't fail anymore. is there a
way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
another/better solution to avoid such problems! thx in advance!You can't assign priorities in any sql server version prior to 2008.
What you should do is attempt to find out WHY you are getting timeouts and
address that issue. Without further information, my first guess would be
I/O delays because you are backing up to the same drive system as the data
resides on. If this is the case you could try backing up to another disk
subsystem. NAS devices or iSCSI SANs are very effective for this and quite
inexpensive these days. Heck, for smallish dbs even an external USB 2 drive
enclosure could be beneficial.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!|||I agree with Kevin in that it sounds like you have I/O issues to begin with.
You must also already have deadlock issues and the backups just make it
worse. I also agree with Kevin in that it sounds like you are backing up to
the same physical drive as the data or log files are on and this should be
changed immediately. A well configured system should only see a few % in
overhead when backing up. This depends on many factors but the process
itself is not that intensive that it should cause issues like you report
unless there are issues already.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!|||I should have mentioned that you can evaluate I/O performance using
performance monitor (various physical disk counters including avg disk queue
length, avg sec/read avg sec/write). You can also evaluate wait stats using
dbcc sqlperf(waitstats) in 2000 or the sys.dm_os_wait_stats DMV in 2005.
Microsoft has very good documentation on what the waits mean. See BOL and
search for "sql server wait stats" or get this doc for 2005:
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kdt7a10ahahec@.corp.supernews.com...
> You can't assign priorities in any sql server version prior to 2008.
> What you should do is attempt to find out WHY you are getting timeouts and
> address that issue. Without further information, my first guess would be
> I/O delays because you are backing up to the same drive system as the data
> resides on. If this is the case you could try backing up to another disk
> subsystem. NAS devices or iSCSI SANs are very effective for this and
> quite inexpensive these days. Heck, for smallish dbs even an external USB
> 2 drive enclosure could be beneficial.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
>> hi all, as we have big problems with sql timeouts and deadlocks during
>> the backup of the databases and transaction log, i am searching for a
>> possibiliy to give the backup procedure a lower priority, so that the
>> sql requests falling into that period don't fail anymore. is there a
>> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
>> another/better solution to avoid such problems! thx in advance!
>|||Dear Kelly,
The strange thing is, that the opposite is the case. We are running 2
backups each day, a nightly one to the same hard drive and a daily one
to a different drive. The backup on the same drive causing much less
problems - however this backup also contains less data than the one
during the day. We already spent a lot of time in query optimization
and are aware that the database I/O sometimes run on the physical
limits, so we don't expect significant improvements by investing more
time on improving the queries and the indexes. That's why we hoped for
somekind of "on idle" backup option to start the backup on low prio.
Maybe you can give some advice on the sequence of the backup itself?
The sequence we use is to check db integrity first, then backup
database and finally save tlog. I wonder if the correct sequence in
sense of a full db backup must be to first save the tlog and then the
database? (In regards to performance / Timeouts this probably doesn't
matter at all I guess?)|||See in-line:
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:039df945-a971-4257-9eb8-64d119c0bc97@.r60g2000hsc.googlegroups.com...
> Dear Kelly,
> The strange thing is, that the opposite is the case. We are running 2
> backups each day, a nightly one to the same hard drive and a daily one
> to a different drive. The backup on the same drive causing much less
> problems - however this backup also contains less data than the one
> during the day.
What are these backups? Are they both FULL backups? Why would one have less
data than the other one? Why would you backup to the same disk as the data
in the first place? If the drive fails you loose the data and the backups.
Backing up across the network can be slower than backing up to disk even if
it is on the same drive.
> We already spent a lot of time in query optimization
> and are aware that the database I/O sometimes run on the physical
> limits, so we don't expect significant improvements by investing more
> time on improving the queries and the indexes.
If the database and queries are alreay highly tuned and you still run into
I/O issues you simply don't have the proper configuration to do the job.
There is no getting around that and there are no magical cures. You need to
upgrade the hardware.
>That's why we hoped for
> somekind of "on idle" backup option to start the backup on low prio.
> Maybe you can give some advice on the sequence of the backup itself?
> The sequence we use is to check db integrity first, then backup
> database and finally save tlog. I wonder if the correct sequence in
> sense of a full db backup must be to first save the tlog and then the
> database? (In regards to performance / Timeouts this probably doesn't
> matter at all I guess?)
I bet it is more the Integrity checks that do the damage more than the
backups. Do you only backup the log once a day? Log backups should be
independent of the FULL backups and done frequently. On average they are run
every five to fifteen minutes. Doing a log backup directly before or after a
FULL backup is pretty much useless.|||Dear Andrew,
The nighly backup contains only around 50% of the data because
recurrung clean-up jobs are running just before the backup. While the
daily backup is just a local one, the nightly one is a remote one.
However, I'll pick up your recommendation to backup the tlog more
frequently and to apply the integrity check only for the nightly
backup. Hope that helps. Thanks so far!|||Well that explains some of it. If you delete half the rows in the db the
transaction log backup will be huge. You might want to see if you can run
the cleanup jobs more often to spread the load over the entire day and keep
the logs to a smaller size.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
> Dear Andrew,
> The nighly backup contains only around 50% of the data because
> recurrung clean-up jobs are running just before the backup. While the
> daily backup is just a local one, the nightly one is a remote one.
> However, I'll pick up your recommendation to backup the tlog more
> frequently and to apply the integrity check only for the nightly
> backup. Hope that helps. Thanks so far!|||Or possibly interleave tlog backups within the cleanup job itself. Delete
some fixed number of rows, backup tran, repeat until no rows to delete.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ummehUQMIHA.748@.TK2MSFTNGP04.phx.gbl...
> Well that explains some of it. If you delete half the rows in the db the
> transaction log backup will be huge. You might want to see if you can run
> the cleanup jobs more often to spread the load over the entire day and
> keep the logs to a smaller size.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
>> Dear Andrew,
>> The nighly backup contains only around 50% of the data because
>> recurrung clean-up jobs are running just before the backup. While the
>> daily backup is just a local one, the nightly one is a remote one.
>> However, I'll pick up your recommendation to backup the tlog more
>> frequently and to apply the integrity check only for the nightly
>> backup. Hope that helps. Thanks so far!
>