Q

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?

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 last published in June 2002

Dig Deeper on Microsoft SQL Server Installation

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close