Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question

2 Answers 2

up vote 5 down vote accepted

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?

share|improve this answer
    
Perfect. Thanks for the link! –  rhughes Dec 10 '13 at 13:55

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.

share|improve this answer
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 Data 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 Data 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 Data Dec 10 '13 at 15:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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