full, differential, transaction hwo to rotate the differential?

Being of the "point and click"-generation I managed to create some sort of a fairly simple backup plan for our databases. There are hourly transactional backups and daily full backups, which were set up with a database maintenance plan. As the databases store event records they are ever increasing around the hour. The daily backups are now of a size that holding them is not feasable anymore and I want to move to a differential backup scheme:

Weekly full backups

Daily differential backups

Hourly transactional log backups

The problem I have is that the database maintenance plans allow me to rotate backup files automatically as they included the date and time of the backup. For differential backups there is no option in the Database Maintenance plan so I want to create them manually. The problem now is that I can create one device, append the differential backup to it. But how do I rotate the differential backup device name, let's say bi-weekly?

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.|||

Do you mean with the SQL-DMO? Or is there another way

oj wrote:

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.

|||

So basically I have no clue

1) how to script out a diff backup plan

2) how to create a sql job that drops the diff plan, creates a new one with the desired device name.

|||

I am not in front of a sqlserver so can't verify. but if:

1. create a differential backup job just as you would normally.

2. go to sql jobs under sqlagent, right click on the job and script it out. This is where you get the definition for the diff job (i.e. job id, name, steps, etc.).

3. take note of the step id and steps definition.

4. create a new sql job that runs every 4 week. In the jobstep definition for this new job (say, switchdevice), you'd want to execute

sp_update_jobstep @.jobname='the_diff_jobname',@.stepid=<the stepid of the diff job>,@.command='new_backup_command_pointing_to_a_desired_device'

5. create another job that does the same thing as the #4 but with a different device name. Be sure to schedule 2 weeks before or after the job in #4.

With the combo of #4 and #5, you essentially have a device name changed every 2 week.

|||thanks for your elaborat answer oj. one thing is still puzzling me. If I create the diff backup to append to the device it will append even after switching. So the device will be ever growing. We can live with a device per week and manually cleanup old one's. Would you know a way to automatically generate these devices? Or should I stop trying and just create them by hand?|||

You can take a look at sp_addumpdevice and sp_dropdevice in sql book online.

Cheers,

|||

Ok, Thanks for the support here. What we end up doing is the following:

We've created two backup devices per database

Two bi-weekly (every odd/even week) scheduled job performs a full back with INIT
BACKUP DATABASE base
TO base_wk<n>
WITH INIT

A daily job backs up a differential
BACKUP DATABASE base
TO base_wk<n>
WITH DIFFERENTIAL

Every half hour a transaction log back is performed
BACKUP LOG base
TO base_wk<n>

In each case <n> is either 1 or 0 for odd and even weeks. Numerous links I've found on backups seem to fail to see the real possibility that during a full backup due to a hardware failure both database and backup are lost, not really disastor save, which backing up is all about.

So there are in total three scheduled jobs: one weekly, one daily and one every half hour. schedule in bi-weekly intervals writing to base_wk0 and base_wk1 alternatively. Furthermore for historic reasons a monthly full backup is scheduled using the wizard which stores a unique backup file for every month.

Let the disastors happen...

full, differential, transaction hwo to rotate the differential?

Being of the "point and click"-generation I managed to create some sort of a fairly simple backup plan for our databases. There are hourly transactional backups and daily full backups, which were set up with a database maintenance plan. As the databases store event records they are ever increasing around the hour. The daily backups are now of a size that holding them is not feasable anymore and I want to move to a differential backup scheme:

Weekly full backups

Daily differential backups

Hourly transactional log backups

The problem I have is that the database maintenance plans allow me to rotate backup files automatically as they included the date and time of the backup. For differential backups there is no option in the Database Maintenance plan so I want to create them manually. The problem now is that I can create one device, append the differential backup to it. But how do I rotate the differential backup device name, let's say bi-weekly?

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.|||

Do you mean with the SQL-DMO? Or is there another way

oj wrote:

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.

|||

So basically I have no clue

1) how to script out a diff backup plan

2) how to create a sql job that drops the diff plan, creates a new one with the desired device name.

|||

I am not in front of a sqlserver so can't verify. but if:

1. create a differential backup job just as you would normally.

2. go to sql jobs under sqlagent, right click on the job and script it out. This is where you get the definition for the diff job (i.e. job id, name, steps, etc.).

3. take note of the step id and steps definition.

4. create a new sql job that runs every 4 week. In the jobstep definition for this new job (say, switchdevice), you'd want to execute

sp_update_jobstep @.jobname='the_diff_jobname',@.stepid=<the stepid of the diff job>,@.command='new_backup_command_pointing_to_a_desired_device'

5. create another job that does the same thing as the #4 but with a different device name. Be sure to schedule 2 weeks before or after the job in #4.

With the combo of #4 and #5, you essentially have a device name changed every 2 week.

|||thanks for your elaborat answer oj. one thing is still puzzling me. If I create the diff backup to append to the device it will append even after switching. So the device will be ever growing. We can live with a device per week and manually cleanup old one's. Would you know a way to automatically generate these devices? Or should I stop trying and just create them by hand?|||

You can take a look at sp_addumpdevice and sp_dropdevice in sql book online.

Cheers,

|||

Ok, Thanks for the support here. What we end up doing is the following:

We've created two backup devices per database

Two bi-weekly (every odd/even week) scheduled job performs a full back with INIT
BACKUP DATABASE base
TO base_wk<n>
WITH INIT

A daily job backs up a differential
BACKUP DATABASE base
TO base_wk<n>
WITH DIFFERENTIAL

Every half hour a transaction log back is performed
BACKUP LOG base
TO base_wk<n>

In each case <n> is either 1 or 0 for odd and even weeks. Numerous links I've found on backups seem to fail to see the real possibility that during a full backup due to a hardware failure both database and backup are lost, not really disastor save, which backing up is all about.

So there are in total three scheduled jobs: one weekly, one daily and one every half hour. schedule in bi-weekly intervals writing to base_wk0 and base_wk1 alternatively. Furthermore for historic reasons a monthly full backup is scheduled using the wizard which stores a unique backup file for every month.

Let the disastors happen...

full, differential, transaction hwo to rotate the differential?

Being of the "point and click"-generation I managed to create some sort of a fairly simple backup plan for our databases. There are hourly transactional backups and daily full backups, which were set up with a database maintenance plan. As the databases store event records they are ever increasing around the hour. The daily backups are now of a size that holding them is not feasable anymore and I want to move to a differential backup scheme:

Weekly full backups

Daily differential backups

Hourly transactional log backups

The problem I have is that the database maintenance plans allow me to rotate backup files automatically as they included the date and time of the backup. For differential backups there is no option in the Database Maintenance plan so I want to create them manually. The problem now is that I can create one device, append the differential backup to it. But how do I rotate the differential backup device name, let's say bi-weekly?

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.|||

Do you mean with the SQL-DMO? Or is there another way

oj wrote:

(untested):

1. script out the diff backup plan.
2. create a sql job that runs every 2 week that drops the diff plan, creates a new one with the desired device name.

|||

So basically I have no clue

1) how to script out a diff backup plan

2) how to create a sql job that drops the diff plan, creates a new one with the desired device name.

|||

I am not in front of a sqlserver so can't verify. but if:

1. create a differential backup job just as you would normally.

2. go to sql jobs under sqlagent, right click on the job and script it out. This is where you get the definition for the diff job (i.e. job id, name, steps, etc.).

3. take note of the step id and steps definition.

4. create a new sql job that runs every 4 week. In the jobstep definition for this new job (say, switchdevice), you'd want to execute

sp_update_jobstep @.jobname='the_diff_jobname',@.stepid=<the stepid of the diff job>,@.command='new_backup_command_pointing_to_a_desired_device'

5. create another job that does the same thing as the #4 but with a different device name. Be sure to schedule 2 weeks before or after the job in #4.

With the combo of #4 and #5, you essentially have a device name changed every 2 week.

|||thanks for your elaborat answer oj. one thing is still puzzling me. If I create the diff backup to append to the device it will append even after switching. So the device will be ever growing. We can live with a device per week and manually cleanup old one's. Would you know a way to automatically generate these devices? Or should I stop trying and just create them by hand?|||

You can take a look at sp_addumpdevice and sp_dropdevice in sql book online.

Cheers,

|||

Ok, Thanks for the support here. What we end up doing is the following:

We've created two backup devices per database

Two bi-weekly (every odd/even week) scheduled job performs a full back with INIT
BACKUP DATABASE base
TO base_wk<n>
WITH INIT

A daily job backs up a differential
BACKUP DATABASE base
TO base_wk<n>
WITH DIFFERENTIAL

Every half hour a transaction log back is performed
BACKUP LOG base
TO base_wk<n>

In each case <n> is either 1 or 0 for odd and even weeks. Numerous links I've found on backups seem to fail to see the real possibility that during a full backup due to a hardware failure both database and backup are lost, not really disastor save, which backing up is all about.

So there are in total three scheduled jobs: one weekly, one daily and one every half hour. schedule in bi-weekly intervals writing to base_wk0 and base_wk1 alternatively. Furthermore for historic reasons a monthly full backup is scheduled using the wizard which stores a unique backup file for every month.

Let the disastors happen...

sql

Full Text catalogue best drive to build on

We have a server with 2 drives. Drive 1 has our swap file, operating
system and SQL transaction log writing to it.
Drive 2 has our SQL database and flat file dumps (db backup)
We can't install a 3rd drive pair as there is not physical room in the
server. Which would be the better drive to have the FT catalogues built
on for best effeciency and speed of search?
We have 14GB of RAM on this box and our FT catalogue is only about
0.5GB. Would any of the catalogue stay memory resident or will there be
a lot of disk reads occuring?
Cheers
Julia
*** Sent via Developersdex http://www.codecomments.com ***
Try your second drive which stored the backups.
The caching mechanism used by SQL FTS is the file system cache. Frequently
requested catalog pages will be in the file system cache. Other pages will
be flushed to disk.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Julia" <julia.nicholas@.nospamgmail.com> wrote in message
news:exZZSLStFHA.2212@.TK2MSFTNGP15.phx.gbl...
> We have a server with 2 drives. Drive 1 has our swap file, operating
> system and SQL transaction log writing to it.
> Drive 2 has our SQL database and flat file dumps (db backup)
> We can't install a 3rd drive pair as there is not physical room in the
> server. Which would be the better drive to have the FT catalogues built
> on for best effeciency and speed of search?
> We have 14GB of RAM on this box and our FT catalogue is only about
> 0.5GB. Would any of the catalogue stay memory resident or will there be
> a lot of disk reads occuring?
> Cheers
> Julia
>
> *** Sent via Developersdex http://www.codecomments.com ***

Full Recovery model and transaction log

When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
the transaction log just keep getting bigger and bigger.
Will doing a Complete backup and backup the transcation log as part of the
maintenance plan truncate the transaction log ?
Thanks.> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
Truncate - yes.
Stop the growth - probably.
Make it smaller - no
Please read the information in BOL regarding the recovery model and how to
choose one that fits your needs. It sounds like you haven't given
sufficient thought to this entire process - including how you use the
various backups to actually recover your system in the event of a loss or
failure.|||Tlog backup will truncate the inactive part i.e. commited transactions of
the log file and minimize the log. Full backup do not shrink tlog.
"fniles" <fniles@.pfmail.com> wrote in message
news:On6exDdJHHA.4384@.TK2MSFTNGP03.phx.gbl...
> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
> Thanks.
>|||fniles (fniles@.pfmail.com) writes:
> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
If you never back it up, that's the way it goes.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
Yes, but as other have said, it will not shrink it. To shrink you need to
use DBCC SHRINKFILE, but you should only shrink if have been any exceptional
event, as for instance not having backed up the transaction log for the
last six months. If you back up the log regularly, the log will grow to
the size it needs, and you should let it stay that way.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hello,
If it is FULL recovery; please schedule a FULL database backup once a day
and schedule the transaction log backup every 30 minutes or so...
This will help you to recover the database and keep the LDF file grown in
control.
Thanks
Hari
"fniles" <fniles@.pfmail.com> wrote in message
news:On6exDdJHHA.4384@.TK2MSFTNGP03.phx.gbl...
> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
> Thanks.
>

full backup database files includes transaction log?

when I complete full database backup ,
I want to know this full backup file includes transanction log ?lovexueer@.gmail.com wrote:
> when I complete full database backup ,
> I want to know this full backup file includes transanction log ?
>
Short answer - No.
You'll have to do a seperate log backup in order to get your logfile
backed up - if you need it. If your database is in SIMPLE recovery mode,
you don't need the log backup and SQL server won't let you back it up
either.
When that's said, SQL server do backup a little part of the logfile
during a FULL backup, but it's only so much that it can recreate the
logfile when you restore the backup.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator

full backup database files includes transaction log?

when I complete full database backup ,
I want to know this full backup file includes transanction log ?lovexueer@.gmail.com wrote:
> when I complete full database backup ,
> I want to know this full backup file includes transanction log ?
No, the contents of the transaction log is not included in a full
backup. Only the current state (of all data, indexes, as well as the
other objects) is included.
However, when restoring a full backup, the transaction log file is
expanded to the size it was when the backup was taken (so this may make
you think the transaction log was actually restored, when it in fact
was not).
Razvan|||This is a multi-part message in MIME format.
--000301030603010101030205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
lovexueer@.gmail.com wrote:
> when I complete full database backup ,
> I want to know this full backup file includes transanction log ?
>
Short answer - No.
You'll have to do a seperate log backup in order to get your logfile
backed up - if you need it. If your database is in SIMPLE recovery mode,
you don't need the log backup and SQL server won't let you back it up
either.
When that's said, SQL server do backup a little part of the logfile
during a FULL backup, but it's only so much that it can recreate the
logfile when you restore the backup.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--000301030603010101030205
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:lovexueer@.gmail.com">lovexueer@.gmail.com</a> wrote:
<blockquote
cite="mid1152689474.604519.28620@.m79g2000cwm.googlegroups.com"
type="cite">
<pre wrap="">when I complete full database backup ,
I want to know this full backup file includes transanction log ?
</pre>
</blockquote>
<font size="-1"><font face="Arial">Short answer - No. <br>
You'll have to do a seperate log backup in order to get your logfile
backed up - if you need it. If your database is in SIMPLE recovery
mode, you don't need the log backup and SQL server won't let you back
it up either.<br>
<br>
When that's said, SQL server do backup a little part of the logfile
during a FULL backup, but it's only so much that it can recreate the
logfile when you restore the backup.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
<br>
</font></font>
</body>
</html>
--000301030603010101030205--|||Steen Persson (DK) wrote:
> lovexueer@.gmail.com wrote:
> > when I complete full database backup ,
> >
> > I want to know this full backup file includes transanction log ?
> >
> >
> Short answer - No.
> You'll have to do a seperate log backup in order to get your logfile
> backed up - if you need it. If your database is in SIMPLE recovery mode,
> you don't need the log backup and SQL server won't let you back it up
> either.
> When that's said, SQL server do backup a little part of the logfile
> during a FULL backup, but it's only so much that it can recreate the
> logfile when you restore the backup.
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator
>
> --000301030603010101030205
> Content-Type: text/html; charset=3DISO-8859-1
> X-Google-AttachSize: 1148
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content=3D"text/html;charset=3DISO-8859-1" http-equiv=3D"Content-=Type">
> </head>
> <body bgcolor=3D"#ffffff" text=3D"#000000">
> <a class=3D"moz-txt-link-abbreviated" href=3D"mailto:lovexueer@.gmail.com"=>lovexueer@.gmail.com</a> wrote:
> <blockquote
> cite=3D"mid1152689474.604519.28620@.m79g2000cwm.googlegroups.com"
> type=3D"cite">
> <pre wrap=3D"">when I complete full database backup ,
> I want to know this full backup file includes transanction log ?
> </pre>
> </blockquote>
> <font size=3D"-1"><font face=3D"Arial">Short answer - No. <br>
> You'll have to do a seperate log backup in order to get your logfile
> backed up - if you need it. If your database is in SIMPLE recovery
> mode, you don't need the log backup and SQL server won't let you back
> it up either.<br>
> <br>
> When that's said, SQL server do backup a little part of the logfile
> during a FULL backup, but it's only so much that it can recreate the
> logfile when you restore the backup.<br>
> <br>
> <br>
> -- <br>
> Regards<br>
> Steen Schlüter Persson<br>
> Databaseadministrator / Systemadministrator<br>
> <br>
> </font></font>
> </body>
> </html>
> --000301030603010101030205--
thank you very much ,
but what is a little part of logfile?|||It records the transactions from the log that occured while the backup was
taking place. This ensures the backup is complete and up to date when it
finishes.
"lovexueer@.gmail.com" wrote:
> Steen Persson (DK) wrote:
> > lovexueer@.gmail.com wrote:
> > > when I complete full database backup ,
> > >
> > > I want to know this full backup file includes transanction log ?
> > >
> > >
> > Short answer - No.
> > You'll have to do a seperate log backup in order to get your logfile
> > backed up - if you need it. If your database is in SIMPLE recovery mode,
> > you don't need the log backup and SQL server won't let you back it up
> > either.
> >
> > When that's said, SQL server do backup a little part of the logfile
> > during a FULL backup, but it's only so much that it can recreate the
> > logfile when you restore the backup.
> >
> >
> > --
> > Regards
> > Steen Schlüter Persson
> > Databaseadministrator / Systemadministrator
> >
> >
> > --000301030603010101030205
> > Content-Type: text/html; charset=ISO-8859-1
> > X-Google-AttachSize: 1148
> >
> > <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> > <html>
> > <head>
> > <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> > </head>
> > <body bgcolor="#ffffff" text="#000000">
> > <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:lovexueer@.gmail.com">lovexueer@.gmail.com</a> wrote:
> > <blockquote
> > cite="mid1152689474.604519.28620@.m79g2000cwm.googlegroups.com"
> > type="cite">
> > <pre wrap="">when I complete full database backup ,
> >
> > I want to know this full backup file includes transanction log ?
> >
> > </pre>
> > </blockquote>
> > <font size="-1"><font face="Arial">Short answer - No. <br>
> > You'll have to do a seperate log backup in order to get your logfile
> > backed up - if you need it. If your database is in SIMPLE recovery
> > mode, you don't need the log backup and SQL server won't let you back
> > it up either.<br>
> > <br>
> > When that's said, SQL server do backup a little part of the logfile
> > during a FULL backup, but it's only so much that it can recreate the
> > logfile when you restore the backup.<br>
> > <br>
> > <br>
> > -- <br>
> > Regards<br>
> > Steen Schlüter Persson<br>
> > Databaseadministrator / Systemadministrator<br>
> > <br>
> > </font></font>
> > </body>
> > </html>
> >
> > --000301030603010101030205--
>
> thank you very much ,
> but what is a little part of logfile?
>|||Steen Persson (DK) wrote:
> > When that's said, SQL server do backup a little part of the logfile
> > during a FULL backup, but it's only so much that it can recreate the
> > logfile when you restore the backup.
lovexueer@.gmail.com wrote:
> but what is a little part of logfile?
The part which contains the transactions executed between the start of
the backup process and the end of it (usually this is several seconds
or minutes, depending on the size of the database).
Razvan|||Razvan Socol wrote:
> Steen Persson (DK) wrote:
> > > When that's said, SQL server do backup a little part of the logfile
> > > during a FULL backup, but it's only so much that it can recreate the
> > > logfile when you restore the backup.
> lovexueer@.gmail.com wrote:
> > but what is a little part of logfile?
> The part which contains the transactions executed between the start of
> the backup process and the end of it (usually this is several seconds
> or minutes, depending on the size of the database).
> Razvan
I meant that the part contains transaction log is drity pages?
the dirty pages is uncommited for datafile.|||lovexueer@.gmail.com wrote:
> Razvan Socol wrote:
> > Steen Persson (DK) wrote:
> > > > When that's said, SQL server do backup a little part of the logfile
> > > > during a FULL backup, but it's only so much that it can recreate the
> > > > logfile when you restore the backup.
> >
> > lovexueer@.gmail.com wrote:
> > > but what is a little part of logfile?
> >
> > The part which contains the transactions executed between the start of
> > the backup process and the end of it (usually this is several seconds
> > or minutes, depending on the size of the database).
> >
> > Razvan
>
> I meant that the part contains transaction log is drity pages?
> the dirty pages is uncommited for datafile.
The transaction log pages that are contained in a full backup, may or
may not be committed in the actual backup file. In other words, they
were not committed to the data file when the backup was started, but
they were committed to the data file (or should have been committed,
i.e. they were dirty pages) when the backup was finished. Since it's
not clear if the those data pages were copied in the backup file before
or after they were modified, the log pages are stored in the backup
file too, so when the backup is restored, the database can be recovered
in a consistent state.
Razvan|||Razvan Socol wrote:
> lovexueer@.gmail.com wrote:
> > Razvan Socol wrote:
> > > Steen Persson (DK) wrote:
> > > > > When that's said, SQL server do backup a little part of the logfile
> > > > > during a FULL backup, but it's only so much that it can recreate the
> > > > > logfile when you restore the backup.
> > >
> > > lovexueer@.gmail.com wrote:
> > > > but what is a little part of logfile?
> > >
> > > The part which contains the transactions executed between the start of
> > > the backup process and the end of it (usually this is several seconds
> > > or minutes, depending on the size of the database).
> > >
> > > Razvan
> >
> >
> >
> > I meant that the part contains transaction log is drity pages?
> > the dirty pages is uncommited for datafile.
> The transaction log pages that are contained in a full backup, may or
> may not be committed in the actual backup file. In other words, they
> were not committed to the data file when the backup was started, but
> they were committed to the data file (or should have been committed,
> i.e. they were dirty pages) when the backup was finished. Since it's
> not clear if the those data pages were copied in the backup file before
> or after they were modified, the log pages are stored in the backup
> file too, so when the backup is restored, the database can be recovered
> in a consistent state.
> Razvan
thanks great!

Full backup and Transaction Logs

Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
doing a Full backup isn't truncating or shrinking the log file. The log
file got to be over 10GB and forced the Data drive to fill up.
Is there some reason for this? I thought when full backups were successful,
they should be truncating the log file.
I can backup log with truncate_only and then issue a dbcc shrinkfile to get
it back down, but I'd like to know why full backups aren't working first.
Thanks.
Kevin A wrote:
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were successful,
> they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to get
> it back down, but I'd like to know why full backups aren't working first.
> Thanks.
>
Nope. See the topic "Truncating the Transaction Log" in Books Online.
If you're running in Full or Bulk-Logged recovery mode, you MUST do
transaction log backups, or the log will continue to accumulate
transactional data. Even in Simple mode, a single large transaction can
cause the log to grow.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Sorry, I just need a bit more clarification.
Are you saying, after a full backup, I need to issue the truncate command?
That doesn't always seem to shrink the Logfile. I have to manually issue
the dbcc srhinkfile.
What would be the suggested steps to take, after the full backup of the DB?
Thanks.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A3927B.3030202@.realsqlguy.com...
> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
> you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Hello,
FULL database backup will not clear the contents in tranasction log file.
The purpose of FULL recovery model is to perform the POIN IN TIME recovery.
In order to do this
you need to take the tranasction log backup. So when ever you perform a
transaction log backup automatically transaction log will be cleared from
the LDF file and will
leep the LDF file growth under control. Incase if you need to do a recovery
on crash/point in time you can use these log backup files. Just read the
Transaction log backup topic in books online.
Thanks
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:eh2Ecu%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were
> successful, they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to
> get it back down, but I'd like to know why full backups aren't working
> first.
> Thanks.
>
|||Hello,
1. First take a look into the recovery model you use for the database.
2. If it is FULL or BULK_LOGGED then you have schedule a transction log
backup in regular intervals [Say every 15 minutes or so]. This frequency can
be changed based on ur data growth,.
Take a look into the BACKUP LOG command to backup the transaction log or you
could use the Maintenence plan wizard to automate the transaction log
backup.
This will make sure that you LDF will not grow drastically.
To reduce the size of 10 GB currently; you may to need to truncate the Log
first and use DBCC SHRINKFILE to reduce the file size. After that to bring
back the backup chain do:-
1. A full database backup
2. Followed with transaction log backups.
THANKS
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:ez%237O%23%23MHHA.4916@.TK2MSFTNGP06.phx.gbl.. .
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually
> issue the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the
> DB?
> Thanks.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45A3927B.3030202@.realsqlguy.com...
>
|||Kevin A wrote:
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually issue
> the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the DB?
> Thanks.
TRUNCATING is not SHRINKING, they are two different operations.
The log file is a "journal", recording every change that takes place in
your database. If the database is in Simple recovery mode, these
journal entries are automatically removed from the log once they've
completed. In Full or Bulk-Logged mode, they remain in the log until
you perform a log backup. This gives you the ability to restore the
database to any point in time, by "playing back" those journal entries
that you've backed up, an ability that you lose with Simple mode.
For example, suppose your database is in Simple mode, and you are doing
full backups nightly at 9:00pm. You suffer a hard drive crash at
8:00pm, corrupting your database. Your only option for recovery is to
restore the backup from 9:00pm the previous night, losing 23 hours of data.
Now, suppose that same database is in Full recovery mode. You're doing
nightly full backups at 9:00pm, and log backups every 5 minutes. Your
drive fails at 8:00pm, corrupting the database. You now have the
ability to restore the previous night's full backup, followed by the log
backups that have been done since then. At most, you lost 5 minutes of
data.
The space inside the transaction log is "recycled", as committed
transactions are flushed out (via a log backup), the space that they
were using is made available for new transactions. This helps to keep
the log file size under control. If you aren't flushing them out (i.e.
TRUNCATING), the log has to grow larger to hold new transactions.
SHRINKING is a different process - it will remove any free space from
the log, physically reducing the size of the log file. You should only
do this under extreme circumstances, it should NOT be part of your daily
routine. Repeated shrinking/growing of a file (log or database) will
lead to fragmentation, which will ultimately hurt performance. If you
have the disk space, you should allow the log to grow as needed, making
sure you're doing proper backups to keep the committed transactions
flushed out.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thank you, you put the very well... Regards, John
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A39711.7070204@.realsqlguy.com...
> Kevin A wrote:
> TRUNCATING is not SHRINKING, they are two different operations.
> The log file is a "journal", recording every change that takes place in
> your database. If the database is in Simple recovery mode, these journal
> entries are automatically removed from the log once they've completed. In
> Full or Bulk-Logged mode, they remain in the log until you perform a log
> backup. This gives you the ability to restore the database to any point
> in time, by "playing back" those journal entries that you've backed up, an
> ability that you lose with Simple mode.
> For example, suppose your database is in Simple mode, and you are doing
> full backups nightly at 9:00pm. You suffer a hard drive crash at 8:00pm,
> corrupting your database. Your only option for recovery is to restore the
> backup from 9:00pm the previous night, losing 23 hours of data.
> Now, suppose that same database is in Full recovery mode. You're doing
> nightly full backups at 9:00pm, and log backups every 5 minutes. Your
> drive fails at 8:00pm, corrupting the database. You now have the ability
> to restore the previous night's full backup, followed by the log backups
> that have been done since then. At most, you lost 5 minutes of data.
> The space inside the transaction log is "recycled", as committed
> transactions are flushed out (via a log backup), the space that they were
> using is made available for new transactions. This helps to keep the log
> file size under control. If you aren't flushing them out (i.e.
> TRUNCATING), the log has to grow larger to hold new transactions.
> SHRINKING is a different process - it will remove any free space from the
> log, physically reducing the size of the log file. You should only do
> this under extreme circumstances, it should NOT be part of your daily
> routine. Repeated shrinking/growing of a file (log or database) will lead
> to fragmentation, which will ultimately hurt performance. If you have the
> disk space, you should allow the log to grow as needed, making sure you're
> doing proper backups to keep the committed transactions flushed out.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||once i have a full backup of the database why would i need the log files
anymore? backupexec 9 indicates they will be removed during a full backup as
i would expect, but it isn't happening on one of my servers. thx if you have
more clues.
"Tracy McKibben" wrote:

> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online.
> If you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||help wrote:
> once i have a full backup of the database why would i need the log files
> anymore? backupexec 9 indicates they will be removed during a full backup as
> i would expect, but it isn't happening on one of my servers. thx if you have
> more clues.
>
Technically, you don't need log files that predate a full backup,
however, it's good insurance to keep as many backups as you can afford
to keep. As the DBA, you're responsible for protecting the company's
data, so keep as many backups as you can.
As for why they're not being deleted by BackupExec, I have no idea. I
don't use third party backup tools like that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Full backup and Transaction Logs

Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
doing a Full backup isn't truncating or shrinking the log file. The log
file got to be over 10GB and forced the Data drive to fill up.
Is there some reason for this? I thought when full backups were successful,
they should be truncating the log file.
I can backup log with truncate_only and then issue a dbcc shrinkfile to get
it back down, but I'd like to know why full backups aren't working first.
Thanks.Kevin A wrote:
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were successful,
> they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to get
> it back down, but I'd like to know why full backups aren't working first.
> Thanks.
>
Nope. See the topic "Truncating the Transaction Log" in Books Online.
If you're running in Full or Bulk-Logged recovery mode, you MUST do
transaction log backups, or the log will continue to accumulate
transactional data. Even in Simple mode, a single large transaction can
cause the log to grow.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Sorry, I just need a bit more clarification.
Are you saying, after a full backup, I need to issue the truncate command?
That doesn't always seem to shrink the Logfile. I have to manually issue
the dbcc srhinkfile.
What would be the suggested steps to take, after the full backup of the DB?
Thanks.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A3927B.3030202@.realsqlguy.com...
> Kevin A wrote:
>> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed
>> that doing a Full backup isn't truncating or shrinking the log file. The
>> log file got to be over 10GB and forced the Data drive to fill up.
>> Is there some reason for this? I thought when full backups were
>> successful, they should be truncating the log file.
>> I can backup log with truncate_only and then issue a dbcc shrinkfile to
>> get it back down, but I'd like to know why full backups aren't working
>> first.
>> Thanks.
> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
> you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hello,
FULL database backup will not clear the contents in tranasction log file.
The purpose of FULL recovery model is to perform the POIN IN TIME recovery.
In order to do this
you need to take the tranasction log backup. So when ever you perform a
transaction log backup automatically transaction log will be cleared from
the LDF file and will
leep the LDF file growth under control. Incase if you need to do a recovery
on crash/point in time you can use these log backup files. Just read the
Transaction log backup topic in books online.
Thanks
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:eh2Ecu%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were
> successful, they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to
> get it back down, but I'd like to know why full backups aren't working
> first.
> Thanks.
>|||Hello,
1. First take a look into the recovery model you use for the database.
2. If it is FULL or BULK_LOGGED then you have schedule a transction log
backup in regular intervals [Say every 15 minutes or so]. This frequency can
be changed based on ur data growth,.
Take a look into the BACKUP LOG command to backup the transaction log or you
could use the Maintenence plan wizard to automate the transaction log
backup.
This will make sure that you LDF will not grow drastically.
To reduce the size of 10 GB currently; you may to need to truncate the Log
first and use DBCC SHRINKFILE to reduce the file size. After that to bring
back the backup chain do:-
1. A full database backup
2. Followed with transaction log backups.
THANKS
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:ez%237O%23%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually
> issue the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the
> DB?
> Thanks.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45A3927B.3030202@.realsqlguy.com...
>> Kevin A wrote:
>> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed
>> that doing a Full backup isn't truncating or shrinking the log file.
>> The log file got to be over 10GB and forced the Data drive to fill up.
>> Is there some reason for this? I thought when full backups were
>> successful, they should be truncating the log file.
>> I can backup log with truncate_only and then issue a dbcc shrinkfile to
>> get it back down, but I'd like to know why full backups aren't working
>> first.
>> Thanks.
>> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
>> you're running in Full or Bulk-Logged recovery mode, you MUST do
>> transaction log backups, or the log will continue to accumulate
>> transactional data. Even in Simple mode, a single large transaction can
>> cause the log to grow.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||Kevin A wrote:
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually issue
> the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the DB?
> Thanks.
TRUNCATING is not SHRINKING, they are two different operations.
The log file is a "journal", recording every change that takes place in
your database. If the database is in Simple recovery mode, these
journal entries are automatically removed from the log once they've
completed. In Full or Bulk-Logged mode, they remain in the log until
you perform a log backup. This gives you the ability to restore the
database to any point in time, by "playing back" those journal entries
that you've backed up, an ability that you lose with Simple mode.
For example, suppose your database is in Simple mode, and you are doing
full backups nightly at 9:00pm. You suffer a hard drive crash at
8:00pm, corrupting your database. Your only option for recovery is to
restore the backup from 9:00pm the previous night, losing 23 hours of data.
Now, suppose that same database is in Full recovery mode. You're doing
nightly full backups at 9:00pm, and log backups every 5 minutes. Your
drive fails at 8:00pm, corrupting the database. You now have the
ability to restore the previous night's full backup, followed by the log
backups that have been done since then. At most, you lost 5 minutes of
data.
The space inside the transaction log is "recycled", as committed
transactions are flushed out (via a log backup), the space that they
were using is made available for new transactions. This helps to keep
the log file size under control. If you aren't flushing them out (i.e.
TRUNCATING), the log has to grow larger to hold new transactions.
SHRINKING is a different process - it will remove any free space from
the log, physically reducing the size of the log file. You should only
do this under extreme circumstances, it should NOT be part of your daily
routine. Repeated shrinking/growing of a file (log or database) will
lead to fragmentation, which will ultimately hurt performance. If you
have the disk space, you should allow the log to grow as needed, making
sure you're doing proper backups to keep the committed transactions
flushed out.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you, you put the very well... Regards, John
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A39711.7070204@.realsqlguy.com...
> Kevin A wrote:
>> Sorry, I just need a bit more clarification.
>> Are you saying, after a full backup, I need to issue the truncate
>> command? That doesn't always seem to shrink the Logfile. I have to
>> manually issue the dbcc srhinkfile.
>> What would be the suggested steps to take, after the full backup of the
>> DB?
>> Thanks.
> TRUNCATING is not SHRINKING, they are two different operations.
> The log file is a "journal", recording every change that takes place in
> your database. If the database is in Simple recovery mode, these journal
> entries are automatically removed from the log once they've completed. In
> Full or Bulk-Logged mode, they remain in the log until you perform a log
> backup. This gives you the ability to restore the database to any point
> in time, by "playing back" those journal entries that you've backed up, an
> ability that you lose with Simple mode.
> For example, suppose your database is in Simple mode, and you are doing
> full backups nightly at 9:00pm. You suffer a hard drive crash at 8:00pm,
> corrupting your database. Your only option for recovery is to restore the
> backup from 9:00pm the previous night, losing 23 hours of data.
> Now, suppose that same database is in Full recovery mode. You're doing
> nightly full backups at 9:00pm, and log backups every 5 minutes. Your
> drive fails at 8:00pm, corrupting the database. You now have the ability
> to restore the previous night's full backup, followed by the log backups
> that have been done since then. At most, you lost 5 minutes of data.
> The space inside the transaction log is "recycled", as committed
> transactions are flushed out (via a log backup), the space that they were
> using is made available for new transactions. This helps to keep the log
> file size under control. If you aren't flushing them out (i.e.
> TRUNCATING), the log has to grow larger to hold new transactions.
> SHRINKING is a different process - it will remove any free space from the
> log, physically reducing the size of the log file. You should only do
> this under extreme circumstances, it should NOT be part of your daily
> routine. Repeated shrinking/growing of a file (log or database) will lead
> to fragmentation, which will ultimately hurt performance. If you have the
> disk space, you should allow the log to grow as needed, making sure you're
> doing proper backups to keep the committed transactions flushed out.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||help wrote:
> once i have a full backup of the database why would i need the log files
> anymore? backupexec 9 indicates they will be removed during a full backup as
> i would expect, but it isn't happening on one of my servers. thx if you have
> more clues.
>
Technically, you don't need log files that predate a full backup,
however, it's good insurance to keep as many backups as you can afford
to keep. As the DBA, you're responsible for protecting the company's
data, so keep as many backups as you can.
As for why they're not being deleted by BackupExec, I have no idea. I
don't use third party backup tools like that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Full backup and Transaction Logs

Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
doing a Full backup isn't truncating or shrinking the log file. The log
file got to be over 10GB and forced the Data drive to fill up.
Is there some reason for this? I thought when full backups were successful,
they should be truncating the log file.
I can backup log with truncate_only and then issue a dbcc shrinkfile to get
it back down, but I'd like to know why full backups aren't working first.
Thanks.Kevin A wrote:
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were successfu
l,
> they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to ge
t
> it back down, but I'd like to know why full backups aren't working first.
> Thanks.
>
Nope. See the topic "Truncating the Transaction Log" in Books Online.
If you're running in Full or Bulk-Logged recovery mode, you MUST do
transaction log backups, or the log will continue to accumulate
transactional data. Even in Simple mode, a single large transaction can
cause the log to grow.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Sorry, I just need a bit more clarification.
Are you saying, after a full backup, I need to issue the truncate command?
That doesn't always seem to shrink the Logfile. I have to manually issue
the dbcc srhinkfile.
What would be the suggested steps to take, after the full backup of the DB?
Thanks.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A3927B.3030202@.realsqlguy.com...
> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
> you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hello,
FULL database backup will not clear the contents in tranasction log file.
The purpose of FULL recovery model is to perform the POIN IN TIME recovery.
In order to do this
you need to take the tranasction log backup. So when ever you perform a
transaction log backup automatically transaction log will be cleared from
the LDF file and will
leep the LDF file growth under control. Incase if you need to do a recovery
on crash/point in time you can use these log backup files. Just read the
Transaction log backup topic in books online.
Thanks
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:eh2Ecu%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were
> successful, they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to
> get it back down, but I'd like to know why full backups aren't working
> first.
> Thanks.
>|||Hello,
1. First take a look into the recovery model you use for the database.
2. If it is FULL or BULK_LOGGED then you have schedule a transction log
backup in regular intervals [Say every 15 minutes or so]. This frequency
can
be changed based on ur data growth,.
Take a look into the BACKUP LOG command to backup the transaction log or you
could use the Maintenence plan wizard to automate the transaction log
backup.
This will make sure that you LDF will not grow drastically.
To reduce the size of 10 GB currently; you may to need to truncate the Log
first and use DBCC SHRINKFILE to reduce the file size. After that to bring
back the backup chain do:-
1. A full database backup
2. Followed with transaction log backups.
THANKS
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:ez%237O%23%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually
> issue the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the
> DB?
> Thanks.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45A3927B.3030202@.realsqlguy.com...
>|||Kevin A wrote:
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually issu
e
> the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the DB
?
> Thanks.
TRUNCATING is not SHRINKING, they are two different operations.
The log file is a "journal", recording every change that takes place in
your database. If the database is in Simple recovery mode, these
journal entries are automatically removed from the log once they've
completed. In Full or Bulk-Logged mode, they remain in the log until
you perform a log backup. This gives you the ability to restore the
database to any point in time, by "playing back" those journal entries
that you've backed up, an ability that you lose with Simple mode.
For example, suppose your database is in Simple mode, and you are doing
full backups nightly at 9:00pm. You suffer a hard drive crash at
8:00pm, corrupting your database. Your only option for recovery is to
restore the backup from 9:00pm the previous night, losing 23 hours of data.
Now, suppose that same database is in Full recovery mode. You're doing
nightly full backups at 9:00pm, and log backups every 5 minutes. Your
drive fails at 8:00pm, corrupting the database. You now have the
ability to restore the previous night's full backup, followed by the log
backups that have been done since then. At most, you lost 5 minutes of
data.
The space inside the transaction log is "recycled", as committed
transactions are flushed out (via a log backup), the space that they
were using is made available for new transactions. This helps to keep
the log file size under control. If you aren't flushing them out (i.e.
TRUNCATING), the log has to grow larger to hold new transactions.
SHRINKING is a different process - it will remove any free space from
the log, physically reducing the size of the log file. You should only
do this under extreme circumstances, it should NOT be part of your daily
routine. Repeated shrinking/growing of a file (log or database) will
lead to fragmentation, which will ultimately hurt performance. If you
have the disk space, you should allow the log to grow as needed, making
sure you're doing proper backups to keep the committed transactions
flushed out.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you, you put the very well... Regards, John
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A39711.7070204@.realsqlguy.com...
> Kevin A wrote:
> TRUNCATING is not SHRINKING, they are two different operations.
> The log file is a "journal", recording every change that takes place in
> your database. If the database is in Simple recovery mode, these journal
> entries are automatically removed from the log once they've completed. In
> Full or Bulk-Logged mode, they remain in the log until you perform a log
> backup. This gives you the ability to restore the database to any point
> in time, by "playing back" those journal entries that you've backed up, an
> ability that you lose with Simple mode.
> For example, suppose your database is in Simple mode, and you are doing
> full backups nightly at 9:00pm. You suffer a hard drive crash at 8:00pm,
> corrupting your database. Your only option for recovery is to restore the
> backup from 9:00pm the previous night, losing 23 hours of data.
> Now, suppose that same database is in Full recovery mode. You're doing
> nightly full backups at 9:00pm, and log backups every 5 minutes. Your
> drive fails at 8:00pm, corrupting the database. You now have the ability
> to restore the previous night's full backup, followed by the log backups
> that have been done since then. At most, you lost 5 minutes of data.
> The space inside the transaction log is "recycled", as committed
> transactions are flushed out (via a log backup), the space that they were
> using is made available for new transactions. This helps to keep the log
> file size under control. If you aren't flushing them out (i.e.
> TRUNCATING), the log has to grow larger to hold new transactions.
> SHRINKING is a different process - it will remove any free space from the
> log, physically reducing the size of the log file. You should only do
> this under extreme circumstances, it should NOT be part of your daily
> routine. Repeated shrinking/growing of a file (log or database) will lead
> to fragmentation, which will ultimately hurt performance. If you have the
> disk space, you should allow the log to grow as needed, making sure you're
> doing proper backups to keep the committed transactions flushed out.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||once i have a full backup of the database why would i need the log files
anymore? backupexec 9 indicates they will be removed during a full backup a
s
i would expect, but it isn't happening on one of my servers. thx if you hav
e
more clues.
"Tracy McKibben" wrote:

> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online.
> If you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||help wrote:
> once i have a full backup of the database why would i need the log files
> anymore? backupexec 9 indicates they will be removed during a full backup
as
> i would expect, but it isn't happening on one of my servers. thx if you h
ave
> more clues.
>
Technically, you don't need log files that predate a full backup,
however, it's good insurance to keep as many backups as you can afford
to keep. As the DBA, you're responsible for protecting the company's
data, so keep as many backups as you can.
As for why they're not being deleted by BackupExec, I have no idea. I
don't use third party backup tools like that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

full backup and transaction log truncate

Hi,
I'm about how the backup system works in SQL Server. I'm using
the BULK LOGGED recovery model, I do regular full backups but my log
file is growing very large.
BOL says:
Full database backup, which backs up the entire database including the
transaction log
and it also says the BACKUP LOG truncates the log file, so I assumed
that BACKUP DATABASE would truncate the log file as well, but it doesn't
seem to be doing that. So the question is:
Does doing a full backup using BACKUP DATABASE truncate the log file?
Although I might be by the statement in BOL I actually hope
that BACKUP DATABASE does not truncate the log file, that way I can do
full backups whenever I want, for whatever reason and these backups will
not interfere with an automated full/differential/trans log backup system.
Thanks.BACKUP DATABASE does not truncate the inactive log portion. This means that
full backups do not interrupt a log backup sequence. If your most recent
backup is bad or incomplete, you can use an older backup as a starting point
and restore more logs to get back to the desired recovery point in time.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"John" <no@.spam> wrote in message
news:edF0K$deGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm about how the backup system works in SQL Server. I'm using
> the BULK LOGGED recovery model, I do regular full backups but my log file
> is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed that
> BACKUP DATABASE would truncate the log file as well, but it doesn't seem
> to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be by the statement in BOL I actually hope that
> BACKUP DATABASE does not truncate the log file, that way I can do full
> backups whenever I want, for whatever reason and these backups will not
> interfere with an automated full/differential/trans log backup system.
> Thanks.|||John,
You have to backup the log in order to allow sql server to re-use the VLF
before the MinLSN. If these VLFs can not be reused and we reach the end of
the transaction log, then sql server will have to expand it.
Truncating the Transaction Log
http://msdn.microsoft.com/library/d...r />
_7vaf.asp
How to stop the transaction log of a SQL Server database from growing
unexpectedly
http://support.microsoft.com/?kbid=873235
AMB
"John" wrote:

> Hi,
> I'm about how the backup system works in SQL Server. I'm using
> the BULK LOGGED recovery model, I do regular full backups but my log
> file is growing very large.
> BOL says:
> Full database backup, which backs up the entire database including the
> transaction log
> and it also says the BACKUP LOG truncates the log file, so I assumed
> that BACKUP DATABASE would truncate the log file as well, but it doesn't
> seem to be doing that. So the question is:
> Does doing a full backup using BACKUP DATABASE truncate the log file?
> Although I might be by the statement in BOL I actually hope
> that BACKUP DATABASE does not truncate the log file, that way I can do
> full backups whenever I want, for whatever reason and these backups will
> not interfere with an automated full/differential/trans log backup system.
> Thanks.
>

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