Re: SQL 2005 Shrink issue

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???))




On 6/17/08 9:29 AM, in article
BD9F875A-9BA5-4842-B554-24C0127DBB68@xxxxxxxxxxxxx, "Chris"
<Chris@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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

.

Relevant Pages

  • Re: Very slow restores on new SQL Server 2000 virtual server
    ... FULL recovery mode do NOT flush committed transactions out of the tlog. ... >> Backups are on the same drive as the data and log files. ... >> rather than the restore itself. ... > Think I've answered my own question - some stupidly large transaction ...
    (microsoft.public.sqlserver.server)
  • Re: database log file keeps growing
    ... When the transaction ends, will the log file shrink ... I thought if I set the recovery mode to SIMPLE, ...
    (microsoft.public.sqlserver.server)
  • Re: Log Shipping....
    ... in log shipping environment we can't shrink the log ... If you issue regualr log backups and don't ... have long running open transactions the log file can reuse the parts of ...
    (microsoft.public.sqlserver.clients)
  • Re: Very slow restores on new SQL Server 2000 virtual server
    ... FULL recovery mode do NOT flush committed transactions out of the tlog. ... must do some flavor of tlog backup to get rid of them. ... full database backups and not taking any transaction log backups? ...
    (microsoft.public.sqlserver.server)
  • Thank you All!
    ... I need to move from FULL recovery to SIMPLE recovery. ... Then setup my backups to use log backups. ... You can perform a shrink on the logs without ...
    (microsoft.public.sqlserver.server)
Loading