Hi,

We have a SQL 2005 DB which has a .ldf file of around 10GB...so it needs
shrinking!!

I've tried a shrink from the management tools but nothing actually happens.
This is probably due to the fact the DB is set as Full recovery mode. Without
having to actually detatch the DB to re-create the .ldf, what other methods
are available to us to shrink the .ldf to a much smaller size?

We'd rather not have to lose any recovery points should we need to go back
to say 2 months ago.

Any ideas?

Thanks

Have you backed up the log? If you are in full recovery mode, you should be
doing regular log transaction backups. If you are not, then you may as well
be in simple recovery mode, because without log backups, you are still only
going to be able to recover to the last full backup in the event your hard
drive goes south.

Anyway, once you have backed up the log, this should clear up space in the
transaction log file, and you should be able to shrink. If you still can't
(please use DBCC SHRINKFILE and not the GUI), then there is probably a
reason. Run DBCC OPENTRAN in a query window in the context of this
database, and you might see a transaction.

However, please read the following article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

10GB may be an appropriate size for your log. If you shrink now, and
tomorrow it is just going to grow to 10GB again, then the shrink was a waste
of time, and the autogrow event(s) will actually hamper performance in an
uncontrollable way. The exception is if log growth was due to an abnormal
data move, large bulk operation, massive delete, etc. that would not happen
regularly.

(As an aside, do you not have backups newer than two months ago???))

Aaron,

Thanks for the quick reply!

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.

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

any idea?

Thanks

"Aaron Bertrand [SQL Server MVP]" wrote:

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.

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]" wrote in message
news:C47D418F.81DB%%ten.xoc@dnartreb.noraa...

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:

The log file itself should not have shrunk at all, unless you also issued
some kind of shrink command (shrink database or shrink file). If it did
then you either came across a new bug or didn't explain exactly what you did
using the management tool.

The data that has been removed from the log file are transactions that have
already been committed prior to the most recent log backup. Since you
backed them up in the .TRN file you don't need to keep them in the log file
anymore.

A

On 6/17/08 11:26 AM, in article
C5844784-B471-430E-802B-AEFDDBA815F8@microsoft.com, "Chris"
discussions.microsoft.com> wrote:

Yes, typo, sorry... I meant the transactions that have been committed...

On 6/17/08 11:14 AM, in article
4A1C1BC2-F619-4A66-A0AF-39081DFCB4AA@microsoft.com, "Ekrem Önsoy"
compecta.com> wrote:

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 WITH TRUNCATE_ONLY

or

BACKUP LOG 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.

Part of the problem I think (maybe not in this specific thread, but in
general) is that people correlate "truncate" and "shrink". So it's
important to have a good understanding of the distinction. One way to help
is with an analogy.

Truncate in terms of log file is kind of like emptying your fridge. The
fridge is still there, but there is plenty of room to stock groceries.

Shrink is kind of buying a smaller fridge, transferring the groceries you
want to keep into the smaller fridge, and throwing the larger fridge away.

Of course, come Thanksgiving time, you may regret the latter decision.
(One-sentence summary of Tibor's article.) :-)

A