Re: SQL 2005 Shrink issue

When you perform a Transaction Log Backup, passive virtual logs in your transaction log file are deleted. So you can shrink your log file.

Taking full backup would not truncate your transaction log file. You could also use

BACKUP LOG <db_name> WITH TRUNCATE_ONLY

or

BACKUP LOG <db_name> WITH NO_LOG

to truncate your transaction log file. Only your active virtual logs would be left in your transaction log file so when you shrink your log file, it's physical file size would decrease if there was any passive virtual logs (till the last active log) in it before you truncating it.

I hope you consider what Aaron has told you about shrinking a database whether it's what you need or not and you've read the article of Tibor.

Note that, WITH TRUNCATE_ONLY and NO_LOG are not going to be used in SQL Server 2008. Instead, Microsoft recommends using SIMPLE recovery model. Simply, you can change your database's recovery model to SIMPLE when you want to get rid of your passive virtual logs in your transaction log file. However, it's recommanded using FULL recovery model for most of the production environments. Again, Aaron has advised you reading this topic from Books Online where you'll find useful information about these topics and much more.

--
Ekrem Önsoy



"Chris" <Chris@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:C5844784-B471-430E-802B-AEFDDBA815F8@xxxxxxxxxxxxxxxx
Ok,

Success. Quite interesting how we've shrunk the log files...can you comment
at all how this has worked?

We just used MS SQL 2005 Mngment tool and did a FULL BACKUP on the DB...then
we did a TRANSACTION LOG BACKUP. The log file shrunk a few MBs. Then we did
exactly the same again, FULL BACKUP then a TRANSACTION LOG backup. The .ldf
then went from about 10GB to about 70MB.

How did this happen, what has actually happened here?

Also, what actual data will have been removed? The .bak is about 10GB so I'm
guessing the data from the .ldf is in there, but is this true...and what data
has been moved?

Thanks so far!!

Chris

"Ekrem Önsoy" wrote:

> 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: How can I back up a log-shipped database?
    ... This means that a later log backup from the production database will not just be ... able to add the log records to the log-shipped database, because the transaction log has been ... It's clear I don't understand the whole RECOVERY business. ...
    (microsoft.public.sqlserver.server)
  • Re: Restore Database w/Large Transaction Log File
    ... If I am reading you correctly, the backup has already been trasmitted to the ... AGAIN, just like the other respondents have told you: shrink the log file, ... Shrinking a database transaction log file that has that has been pre-gone to 200 GB in size, will not save any disk space on the restore. ...
    (microsoft.public.sqlserver.server)
  • Re: newbie backup question
    ... Your data will be moved to MDF file after the checkpoint, ... You can run the below command to truncate the transaction log, ... Even if you backup the transaction log, ... not be useful since you turned the recovery to SIMPLE. ...
    (microsoft.public.sqlserver.setup)
  • Re: How can I back up a log-shipped database?
    ... It's clear I don't understand the whole RECOVERY business. ... I had *hoped* that, by temporarily suspending the log file processing, I could somehow get ... sounds as if, by virtue of performing a backup on the DB, I'd be "marking" the transaction ... > Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: sql server table sizes
    ... If I were you, I would not back the log up, but rather set the recovery mode ... Regularly schedule a transaction log backup using the DB Maintenance Plan ... > dbcc shrinkfile ...
    (microsoft.public.sqlserver.clients)