Ask the Expert

Is there a way to shrink the transaction log?

I am having a problem with backing up transaction logs on MS SQL 2000. I have a maintenance plan running that backs up the database, but not the transaction log. I hadn't noticed the size before, but now the transaction log has filled up the hard drive. I am unable to backup and truncate, as there is no room to back up to. I have tried to use "DBCC SHRINKFILE," but it only shrinks it a few hundred megabytes. Is there any other way to shrink the transaction log?

    Requires Free Membership to View

If you don't require the transaction log for recovery purposes then you need to change the recovery model that the database is using to SIMPLE so this doesn't happen again. You can do this by executing

 ALTER DATABASE databasename SET RECOVERY SIMPLE
DBCC SHRINKFILE should then be able to reduce the log file to a reasonable size. Alternatively you can clear the transaction log of committed transactions by executing
 BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE should now also be more effective, however if you don't change the recovery model to SIMPLE the log file will just fill up over time again. Search on "Selecting a Recovery Model" in SQL Server Books online for more information.

 

For More Information

This was first published in June 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: