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.

How do I truncate the transaction log in a SQL Server 2008 database?

What are possible best ways best ways?

I tried this from a blog as follows:

1) From the setting database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.

2) Shrinking database file or database adds fragmentation.

There are a lot of things you can do. First, start taking proper log backup using the following command instead of truncating them and losing them frequently.

BACKUP LOG [TestDb] TO  DISK = N'C:\Backup\TestDb.bak'
GO

Remove the code of SHRINKING the file. If you are taking proper log backups, your log file usually (again usually, special cases are excluded) do not grow very big.

share|improve this question

migrated from stackoverflow.com Oct 20 '11 at 10:22

This question came from our site for professional and enthusiast programmers.

2  
OK, and what happened when you tried that? –  Widor Oct 18 '11 at 13:25

3 Answers 3

up vote 3 down vote accepted

The safest and correct way to truncate a log file if the database is in Full Recovery Mode is to do a Transaction Log Backup (without TRUNCATE_ONLY. This is going to be deprecated in future releases and is not advisable).

It sounds like you want to shrink your log file afterwards, in which case you'd run a DBCC SHRINKFILE(yourTLogName) command. There is an optional second parameter for the requested size to shrink it to.

share|improve this answer

You could backup the log to the null device:

backup log [databasename] to disk = 'nul';

Or you could switch the recovery model to simple and then back to full/bulk again.

share|improve this answer
    
I did + one on this because this is a tool in the dbas toolbox and your answer do not deserve negative votes I think. I agree with the sentiment that the best way is to take transactionlog backups but hey all good tools can be used in a correct way or in a bad way. –  Martin Sjöberg Mar 12 '13 at 7:25

If you do not care about your log data and just want to get rid of it:

Change the recovery model from full to simple, and then back to full. Shrink the file using DBCC SHRINKFILE with the TRUNCATEONLY argument

The following command will change the recovery model from full to simple

ALTER DATABASE <databse_name> SET RECOVERY SIMPLE

The following command will change the recovery model to full

ALTER DATABASE <databse_name> SET RECOVERY FULL

To find the name of the log file you can use the following query

SELECT name 
FROM sys.master_files
WHERE database_id = DB_ID('<databse_name>')

Shrink the file

DBCC SHRINKFILE (N'<logical_file_name_of_the_log>' , 0, TRUNCATEONLY)

See What is the command to truncate a SQL Server log file? for more information on this

share|improve this answer

Your Answer

 
discard

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