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

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close