Ho can i truncate transaction Log?

Question

Ho can i truncate transaction Log?

andy-232663

SSChasing Mays

Points: 603
More actions
June 28, 2005 at 7:04 am

#109555

How can i truncate transaction log with no recovery,i run out of space on my test server.so i want to truncate transaction log.
Please help me
how can i do with T-Sql.

Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sswords SSCrazy Eights Points: 8207 More actions · Answer 1

Assuming that this is a test server and you don't need to recover the data, the simplest thing would probably be to detach the database, then re-attach using the sp_attach_single_file_db method which can be found in Books On Line. This will reattach the database with a default log file size of 1 megabyte.

I'd also suggest you read up on backup and recovery methods and implement one to make sure your log file doesn't grow out of control again in the future. Hope this helps.

My hovercraft is full of eels.

sswords SSCrazy Eights Points: 8207 More actions · Answer 2

Oops. Hit the post button too quick. The T-SQL for the method described above is as follows:

1.  EXEC sp_detach_db @dbname = 'mydatabase'

2.  Delete the offending *.ldf file

3.  EXEC sp_attach_single_file_db @dbname = 'mydatabase',    @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\mydatabase.mdf'

Best of luck.

My hovercraft is full of eels.

amish-215805 Old Hand Points: 350 More actions · Answer 3

Hi

you can also use

Set your recovery model to simple.

run the following

Backup log <dbname> with no_log

set your recovery model to full

backup your database immediately.

Thanks

Amish

Richard-212791 SSC Rookie Points: 43 More actions · Answer 4

You can also truncate the log without performing a backup by setting the recovery model for the database to simple and use dbcc shinkdatabase (can also use shinkfile)

stick something like this in a scheduled job

use master

alter database testdatabasename

set recovery simple

go

dbcc shrinkdatabase(testdatabasename)

go

alter database testdatabasename

set recovery full

go

although the detach, reattach method is quicker, with the above their is no downtime

Lisa Andrews SSC Rookie Points: 32 More actions · Answer 5

Another option is to run:

DUMP transaction your db name WITH NO_log

Jason Hall Hall of Fame Points: 3906 More actions · Answer 6

The command that I use is:

BACKUP LOG <dbname> WITH TRUNCATE_ONLY

Keep in mind that this will simply remove the entries from the transaction log, it will not shrink the transaction log file.

DCPeterson SSCoach Points: 15266 More actions · Answer 7

Although none of the posts are technically incorrect, they are also incomplete in my estimation. You should understand the consequences of using these methods.

If you set the database to Simple recovery you will loose the ability to restore to a point in time, even if you switch back to full recovery, until you do a full backup. The same applies to issuing a backup log...with truncate_only command.

Not that you shouldn't do as advised, it is likely at this point that you have little choice, but if recovery to a point in time is important, as soon as you truncate the log and shrink the file, you need to do a full backup, and then resume regular log backups.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Johnny Zhang Grasshopper Points: 17 More actions · Answer 8

Have you ever tried to use DBCC shrinkfile (<logfileID>, <fileSize in MB&gt? It makes things a lot easier, if you already have a scheduled backup plan.

The truncation of the transaction log occurs in various circumstances as explained in 'Truncating the Transaction Log' of BOL. Therefore, you do not have to manually truncate it to very small size, if the active portion of the log is not too big. In this way, you do not have to conduct additional full backup.