Truncate transaction log - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Truncate transaction log

Author

Topic

klsrao
Starting Member

4 Posts

Posted - 2009-05-30 : 12:05:05

how to truncate transaction log?

I used dbcc shrinkfile(mydbname_log,2)

but it is not truncating the log.

Question 2:
i do updates, inserts and deletes. i dont need to keep log of these actions.
how to disable log before start of my process and enable after?

Tx in advance.
BR
Lak

Learn how not to speak negative.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-05-30 : 12:15:39

You need to backup the log. If the database is a testing only database that you do not need to be able to reliably recover you could set the database recovery model to Simple, which means youwill not need to (and will not be able to) backup the transaction log.

a.rameshk
Starting Member

19 Posts

Posted - 2009-05-31 : 03:39:31

USE master
GO
DBCC SQLPERF(LOGSPACE) --To know about the Database LogFile
DUMP TRAN DatabaseName WITH NO_LOG--
GO
USE DatabaseName
SP_HELPFILE
GO
DBCC SHRINKFILE ('DatabaseName')
By doing this you can shrink the log file.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-05-31 : 04:59:36

You cannot disable the transaction log. It's not an optional component of a database.

Back the log up and then you'll be able to shrink it. Shrinking shouldn't be done regularly, as a once-off operation it's Ok. If you don't need point in time recovery, set the DB to simple recovery moed.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources