Full Model Recovery (Backup)

I run a FULL backup each morning at 12:00am, Steps are, check db, backup, zip backup and remove, shrink log file.
Along with the FULL backup I run Transaction log backups from 7:00am to 11:59pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with the previous days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to remove inactive entries at each hour backup. Can I shrink the file after each hour backup with out damaging the previous backup?
Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?
|||Thanks, I guess the problem is the actual backup file growth, by the end of the night it is ove 50gigs. Would a simple model work with a differential with init option work better?
After each differential I would zip and remove the the backup to another drive.
In the event of a disaster I would restore from the Full I did at 12:00am and use the last differential made in the current day.
"Hari Prasad" wrote:

> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup.
> This controls the growth of transaction log file(LDF). Probably as you do by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> zip backup and remove, shrink log file.
> 11:59pm
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> to remove inactive entries at each hour backup. Can I shrink the file after
> each hour backup with out damaging the previous backup?
>
>

Full Model Recovery (Backup)

I run a FULL backup each morning at 12:00am, Steps are, check db, backup, zi
p backup and remove, shrink log file.
Along with the FULL backup I run Transaction log backups from 7:00am to 11:5
9pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm.
At 12:30am I zip the file and remove. This zipped file goes with the previo
us days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to
remove inactive entries at each hour backup. Can I shrink the file after ea
ch hour backup with out damaging the previous backup?Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?|||Thanks, I guess the problem is the actual backup file growth, by the end of
the night it is ove 50gigs. Would a simple model work with a differential wi
th init option work better?
After each differential I would zip and remove the the backup to another dri
ve.
In the event of a disaster I would restore from the Full I did at 12:00am an
d use the last differential made in the current day.
"Hari Prasad" wrote:

> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup
.
> This controls the growth of transaction log file(LDF). Probably as you do
by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> zip backup and remove, shrink log file.
> 11:59pm
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> to remove inactive entries at each hour backup. Can I shrink the file afte
r
> each hour backup with out damaging the previous backup?
>
>

Full Model Recovery (Backup)

I run a FULL backup each morning at 12:00am, Steps are, check db, backup, zip backup and remove, shrink log file.
Along with the FULL backup I run Transaction log backups from 7:00am to 11:59pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with the previous days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to remove inactive entries at each hour backup. Can I shrink the file after each hour backup with out damaging the previous backup?Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?|||Thanks, I guess the problem is the actual backup file growth, by the end of the night it is ove 50gigs. Would a simple model work with a differential with init option work better?
After each differential I would zip and remove the the backup to another drive.
In the event of a disaster I would restore from the Full I did at 12:00am and use the last differential made in the current day.
"Hari Prasad" wrote:
> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup.
> This controls the growth of transaction log file(LDF). Probably as you do by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> > I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
> zip backup and remove, shrink log file.
> >
> > Along with the FULL backup I run Transaction log backups from 7:00am to
> 11:59pm
> > once an hour. Append the file. The file has grown to over 33gigs by
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> >
> > My question is, how can I keep the transaction log trimmed? I have it set
> to remove inactive entries at each hour backup. Can I shrink the file after
> each hour backup with out damaging the previous backup?
>
>

Full Log Setting

I've confused myself. If you're doing log backups and you
have of course the database option set to FULL, then how
can your logs ever shrink? I understand that after the
log backup happens technically the logs can shrink w/o
risk of losing data in the logs. But what actually
shrinks these logs? Autoshrink won't do it, will it? Do
you have run something manuall?
Please refer to Tibor's link
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Jeff Duncan
MCDBA, MCSE+I
"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in message
news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
> I've confused myself. If you're doing log backups and you
> have of course the database option set to FULL, then how
> can your logs ever shrink? I understand that after the
> log backup happens technically the logs can shrink w/o
> risk of losing data in the logs. But what actually
> shrinks these logs? Autoshrink won't do it, will it? Do
> you have run something manuall?
>
|||Thx for the link, but I'm getting more confused (which is
probably a good sign):
If I'm doing log backups and have FULL on, can I ever get
these logs shrunk w/o using Tibor's specialized shrink
code?

>--Original Message--
>Please refer to Tibor's link
>http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
you[vbcol=seagreen]
Do
>
>.
>
|||There's no shrink code on that page. The code is only there to prove that when you shrink a data file, the
same amount of data is logged to the tlog.
If you do both full and log backup, then the log backups will empty the files. To shrink the files, you need
to either have autoshrink, or execute DBCC SHRINKFILE or DBCC SHRINKDB. But please read the section about
virtual log files and the log file has to be empty at the end.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:905a01c432ed$d176ac70$a301280a@.phx.gbl...[vbcol=seagreen]
> Thx for the link, but I'm getting more confused (which is
> probably a good sign):
> If I'm doing log backups and have FULL on, can I ever get
> these logs shrunk w/o using Tibor's specialized shrink
> code?
> message
> you
> Do

Full Log Setting

I've confused myself. If you're doing log backups and you
have of course the database option set to FULL, then how
can your logs ever shrink? I understand that after the
log backup happens technically the logs can shrink w/o
risk of losing data in the logs. But what actually
shrinks these logs? Autoshrink won't do it, will it? Do
you have run something manuall?Please refer to Tibor's link
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Jeff Duncan
MCDBA, MCSE+I
"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in message
news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
> I've confused myself. If you're doing log backups and you
> have of course the database option set to FULL, then how
> can your logs ever shrink? I understand that after the
> log backup happens technically the logs can shrink w/o
> risk of losing data in the logs. But what actually
> shrinks these logs? Autoshrink won't do it, will it? Do
> you have run something manuall?
>|||Thx for the link, but I'm getting more confused (which is
probably a good sign):
If I'm doing log backups and have FULL on, can I ever get
these logs shrunk w/o using Tibor's specialized shrink
code?

>--Original Message--
>Please refer to Tibor's link
>http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"bzkdfl" <anonymous@.discussions.microsoft.com> wrote in
message
>news:8ed101c432de$bb0a7610$a101280a@.phx.gbl...
you[vbcol=seagreen]
Do[vbcol=seagreen]
>
>.
>|||There's no shrink code on that page. The code is only there to prove that wh
en you shrink a data file, the
same amount of data is logged to the tlog.
If you do both full and log backup, then the log backups will empty the file
s. To shrink the files, you need
to either have autoshrink, or execute DBCC SHRINKFILE or DBCC SHRINKDB. But
please read the section about
virtual log files and the log file has to be empty at the end.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:905a01c432ed$d176ac70$a301280a@.p
hx.gbl...[vbcol=seagreen]
> Thx for the link, but I'm getting more confused (which is
> probably a good sign):
> If I'm doing log backups and have FULL on, can I ever get
> these logs shrunk w/o using Tibor's specialized shrink
> code?
>
> message
> you
> Do

Full back ups and transaction logs

Running 2005 with sp2. It seems that when running a full back up set as a
maintenace plan the transaction logs are filling up and do not shrink back.
The database is set to FULL recovery. Can some please assist me thank youFull backup does not empty the log. Use BACKUP LOG for that. If you don't want to do log backups,
set the recovery model to simple.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
news:844C7B8A-2E4D-46F0-A51B-CAD917959C64@.microsoft.com...
> Running 2005 with sp2. It seems that when running a full back up set as a
> maintenace plan the transaction logs are filling up and do not shrink back.
> The database is set to FULL recovery. Can some please assist me thank you|||A FULL backup does nothing to clear the transaction log in FULL recovery
mode. You need regular LOG backups for that to happen. Even a LOG backup
will not shrink the file, it only allows committed and backed up portions of
the log to be reused. If you are in FULL recovery mode and wish to reap the
benefits of that you must do regular LOG backups, otherwise change the
recovery mode to Simple.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in
message news:844C7B8A-2E4D-46F0-A51B-CAD917959C64@.microsoft.com...
> Running 2005 with sp2. It seems that when running a full back up set as a
> maintenace plan the transaction logs are filling up and do not shrink
> back.
> The database is set to FULL recovery. Can some please assist me thank
> you|||My questions is why would the back up cause the transaction log to grow and
grow. I do not recall this happending in 2000.
"Tibor Karaszi" wrote:
> Full backup does not empty the log. Use BACKUP LOG for that. If you don't want to do log backups,
> set the recovery model to simple.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
> news:844C7B8A-2E4D-46F0-A51B-CAD917959C64@.microsoft.com...
> > Running 2005 with sp2. It seems that when running a full back up set as a
> > maintenace plan the transaction logs are filling up and do not shrink back.
> > The database is set to FULL recovery. Can some please assist me thank you
>|||The backup has nothing to do with the logs growing. It is the transactions
you are issuing that cause the log to grow and if you don't issue regular
Log backups this will keep happening. Something you may be encountering is
that even if you are in FULL recovery mode, until you issue a valid FULL
backup it will act as if it was in Simple mode and truncate the log when 70%
full. As soon as you issue a FULL backup it will expect you to also issue
regular Log backups. So I suspect no one ever did a FULL backup on the old
db.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in
message news:4B79C133-8BFC-4841-B36D-FDD6482811E1@.microsoft.com...
> My questions is why would the back up cause the transaction log to grow
> and
> grow. I do not recall this happending in 2000.
> "Tibor Karaszi" wrote:
>> Full backup does not empty the log. Use BACKUP LOG for that. If you don't
>> want to do log backups,
>> set the recovery model to simple.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in
>> message
>> news:844C7B8A-2E4D-46F0-A51B-CAD917959C64@.microsoft.com...
>> > Running 2005 with sp2. It seems that when running a full back up set
>> > as a
>> > maintenace plan the transaction logs are filling up and do not shrink
>> > back.
>> > The database is set to FULL recovery. Can some please assist me thank
>> > you