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)
marc_s
  • 9,052
  • 6
  • 46
  • 52
rhughes
  • 325
  • 3
  • 5
  • 14

2 Answers2

4

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?

Glorfindel
  • 2,205
  • 5
  • 19
  • 26
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
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.

Poldi
  • 181
  • 6