Re: SQL 2005 Shrink issue

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

night's backup, then switch to simple recovery mode (this will prevent the
log from growing, because in simple recovery mode, it doesn't need to hold
all of your active transactions). Otherwise, I would create a job that

I believe you'd say "it doesn't need to hold all of your PASSIVE transactions" instead of "active" as passive virtual logs are deleted in every checkpoint if the recovery model is SIMPLE. Active transactions have to stay in the transaction log file as they are still active. This must be a typo.

--
Ekrem Önsoy



"Aaron Bertrand [SQL Server MVP]" <ten.xoc@xxxxxxxxxxxxxx> wrote in message news:C47D418F.81DB%ten.xoc@xxxxxxxxxxxxxxxxx
Are you able to clarify the tranasctional log backup, log backup and any
other types of backup we're able to do in SQL 2005? I think I may be getting
confused over some of these.

You can backup the database, which is typically just called a backup.
Backing up the database gives you a file that you can restore somewhere for
testing or for recovery purposes. It is basically a point in time snapshot
of your data.

You can also backup the transaction log in between full backups. This
allows you to take that full backup snapshot, restore it, and then apply the
log backups to recover up until a point in time. Without log backups, if
you take a full backup every night, and then you lose your disk at 4 PM, you
lose all of the changes to your database that happened after the backup. If
you have transaction log backups every 15 minutes, you can have little or
even no data loss.

(Assuming, of course, that you put the .BAK files on a different disk than
your data files reside on. If you put them in the same place, you will lose
both.)

Books Online can explain this in much more detail than I can possibly do in
a newsgroup post.

We run backups nightly, creating a new .bak each time and we can go back up
to 2 months.

In FULL recovery mode, this is not a wise backup model. FULL recovery mode
is meant to allow you to recover fully to a specific point in time. However
to do so you need to back up your logs periodically in order to apply them
to get up to a point in time. If you don't back up your log multiple times
throughout the day, and only do a full backup every night, then the log will
get bigger and bigger... it is waiting for you to back it up so it can free
up space. If you don't back it up to free up the space used by what should
be inactive transactions, the log needs to keep those as active because you
haven't backed it up. In your current mode, you have no more safety than in
simple recovery mode. Whether you need to be in full recovery mode, or can
switch to simple recovery mode, depends on your tolerance for data loss. In
a catastrophe, if it is okay to lose all data that has changed since last
night's backup, then switch to simple recovery mode (this will prevent the
log from growing, because in simple recovery mode, it doesn't need to hold
all of your active transactions). Otherwise, I would create a job that
backs up the transaction log periodically throughout the day.

A


.

Relevant Pages

  • Re: SQL 2005 Shrink issue
    ... We just used MS SQL 2005 Mngment tool and did a FULL BACKUP on the DB...then ... FULL BACKUP then a TRANSACTION LOG backup. ... all of your active transactions). ... In FULL recovery mode, this is not a wise backup model. ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL 2005 Shrink issue
    ... Yes, typo, sorry... ... I meant the transactions that have been committed... ... log from growing, because in simple recovery mode, it doesn't need to hold ... all of your active transactions). ...
    (microsoft.public.sqlserver.tools)
  • Re: truncate transaction log
    ... backup after the bulk logged operation. ... you said you did a Backup Log with Truncate_Only. ... point in time recovery or even log file recovery then maybe you should ... Make sure when you switch back to Full recovery mode you issue a ...
    (microsoft.public.sqlserver.server)
  • Re: Log backup question.
    ... If db is in full recovery mode, ... do a log backup using the NO_TRUNCATE option. ... > Is there a way to apply the active log after restore the DB ...
    (microsoft.public.sqlserver.server)
  • Re: database log truncated, transaction log backup job failed
    ... sense to put msdb in full recovery mode I don't know, ... I usually have msdb in full recovery and do the backups for msdb as the very last database each time. ... This is just so I have backup of the backup history tables. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.tools)