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




On 6/17/08 9:29 AM, in article
BD9F875A-9BA5-4842-B554-24C0127DBB68...soft (DOT) com, "Chris"
<Chris (AT) discussions (DOT) microsoft.com> wrote:

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...soft (DOT) com, "Chris"
<Chris (AT) discussions (DOT) microsoft.com> wrote:

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...soft (DOT) com, "Chris"
<Chris (AT) discussions (DOT) microsoft.com> wrote:

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...soft (DOT) com, "Chris"
<Chris (AT) discussions (DOT) microsoft.com> wrote:

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...soft (DOT) com, "Chris"
<Chris (AT) discussions (DOT) microsoft.com> wrote:

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...soft (DOT) com, "Chris"
<Chris (AT) discussions (DOT) microsoft.com> wrote:

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...soft (DOT) com, "Chris"
<Chris (AT) discussions (DOT) microsoft.com> wrote:

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...soft (DOT) com, "Chris"
<Chris (AT) discussions (DOT) microsoft.com> wrote:

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...soft (DOT) com, "Chris"
<Chris (AT) discussions (DOT) microsoft.com> wrote: