Truncate Log

Question

Post reply

Truncate Log

Dinesh Asanka

SSChampion

Points: 11058
More actions
January 18, 2005 at 10:12 pm

#60384

How to truncate the Transation Log of a given database file by using a T-SQL script

My Blog: http://dineshasanka.spaces.live.com/

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

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

philcart SSC-Forever Points: 47794 More actions · Answer 1

Look for BACKUP in Books Online.

--------------------
Colt 45 - the original point and click interface

Subhash-63067 SSCarpal Tunnel Points: 4171 More actions · Answer 2

hai Dinesh,

You can use ,

backup Log <database name> with truncate_only

or

Backup Log <database name> with no_log

this will not reduce the size of the physical log file

subban

Osoba SSCommitted Points: 1964 More actions · Answer 3

BACKUP LOG [dbname] WITH TRUNCATE_ONLY

Note:

The "Backup Log Truncate Only" command should be used if you are out of disk space and can't backup the db.

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 4

Thnk you all. simple thing great help

My Blog: http://dineshasanka.spaces.live.com/

Michael Ziese-200679 Right there with Babe Points: 789 More actions · Answer 5

after you had truncated the log file you can shrink the file by:

DBCC SHRINKFILE

( { file_name | file_id }

{ [ , target_size ]

| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]

}

)

Russell Shilling SSCarpal Tunnel Points: 4349 More actions · Answer 6

I truncate the log after the weekly maintenance tasks have run, where there is no production. I do this because we archive the logs for HIPAA reasons, and I'd prefer not to save a 1GB transaction log that just records the maintenance tasks. Then, I immediately back up the database.

There is a little risk, in that if the backup fails, I could have a problem the next (production) day, but, I've been lucky so far.

If someone knows of a better way, or a setting that won't log the maintenence transactions, let me know, please. (I'm using the DB Maint plan rather than raw T-SQL scripts to do the weekly stuff.)

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Subhash-63067 SSCarpal Tunnel Points: 4171 More actions · Answer 7

During your meintenance task, set you recovry model to SIMPLE

subban

philcart SSC-Forever Points: 47794 More actions · Answer 8

Now that would be a silly thing to do if the database is operational 24x7

--------------------
Colt 45 - the original point and click interface