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
  • 8,932
  • 6
  • 45
  • 51
rhughes
  • 325
  • 3
  • 5
  • 14

2 Answers2

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?

Glorfindel
  • 2,201
  • 5
  • 17
  • 26
Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
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
  • 1
    Truncated <> shrunk. Switching recovery models will never make the file smaller. – Aaron Bertrand Dec 10 '13 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 Dec 10 '13 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 Dec 10 '13 at 15:10
  • Yes, you're right. – Poldi Dec 10 '13 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 Dec 10 '13 at 15:50
  • But preventing your log file from growing isn't the only consideration when choosing a recovery model. You can keep a log file from growing over time in FULL too, you just have to take log backups frequently enough (kind of the point of FULL in the first place). Again, changing the recovery model is not a "fix" for this specific problem. – Aaron Bertrand Dec 10 '13 at 15:54
  • Yes, it is more like a last consideration. And backing up regularly right is always better, as you said. – Poldi Dec 10 '13 at 16:12