1

I have a 1 GB .LDF for a test database which I want to shrink right down. I am happy to delete its contents if needed.

How can I do this for SQL Server 2000?

I have tried the following to no huge avail:

BACKUP LOG Database_TEST with truncate_only
DBCC SHRINKFILE (Database_TEST_Log, 1000)
Improve this question
0

2 Answers 2

Reset to default
5

The second argument to DBCC SHRINKFILE is target size, in MEGABYTES. So to paraphrase your code into English, you are saying:

"SQL Server, please take this 1 GB file, and shrink it to 1000 MB.

P.S. 1000 MB = 1 GB. So, don't do anything."

You should try a smaller target size, e.g.:

DBCC SHRINKFILE (Database_TEST_Log, 1);

If the file doesn't seem to shrink, run the commands again (or issue a CHECKPOINT and run the shrink command again, if the recovery model is SIMPLE).

If the file still doesn't seem to shrink, it is probably because there is a long-running transaction. You can find out what this is using the following command against this database:

DBCC OPENTRAN;

Of course, when you do things like BACKUP WITH TRUNCATE_ONLY I hope you don't care about this database or want to be able to recover to a point in time prior to this operation, or at least that once you shrink this log file you will resume proper log management and backup procedures. Also, if you shrink the file to a minimum size, the next time a user causes log to be generated, they'll have to wait for file to grow. This is especially true for log files, which can't benefit from instant file initialization like data files can, and can be particularly annoying when the autogrow size is inappropriate and/or the log is not on a fast disk (like SSD).

Please have a good read of this question and its answers:

Why Does the Transaction Log Keep Growing or Run Out of Space?

Improve this answer
0
0

Maybe you can switch the test database to the simple recovery model? Do you want to recover a test database to a point in time?

BOL: The transaction log is automatically truncated when the database is using the simple recovery model. If you must remove the log backup chain from a database, switch to the simple recovery model.

Improve this answer
7
  • 1
    Truncated <> shrunk. Switching recovery models will never make the file smaller.
    – Aaron Bertrand
    Commented Dec 10, 2013 at 14:15
  • I mean if you start with a simple recovery model in the test environment you don't have to shrink at all.
    – Poldi
    Commented Dec 10, 2013 at 15:08
  • That's not necessarily true at all either. Large transactions can still inflate the log beyond expectations, even in simple. Anyway it sounds like you are suggesting switching to simple recovery is a fix for the current file size, hence my comment.
    – Aaron Bertrand
    Commented Dec 10, 2013 at 15:10
  • Yes, you're right.
    – Poldi
    Commented Dec 10, 2013 at 15:44
  • Switching to simple doesn't fix anything and large transactions will also grow the log file, but at least it doesn't keep growing over time.
    – Poldi
    Commented Dec 10, 2013 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.