Truncating Transaction Logs.

[itsmarkdavies]

I am having trouble Truncating a Transaction Log. I`ve tried everything in Book Online.
I`ve backed up the database, I`ve tried DBCC SHRINKFILE, DBCC SHRINKDATABASE, BACKUP LOG TRUNCATE_ONLY ...etc, but it will not shrink. Any suggestions ? Thanks.

 

[tlbroadbent]

Check out the FAQ posted by FOXDEV - FAQ183-345 - to find more information about shrinking the log and the log file. You can also read the following articles.

Shrinking Active Log Files—Revisited

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9011

Quickly Shrinking the Transaction Log

http://www.swynk.com/friends/krishnan/tranlogshrink.asp

Shrinking the Transaction Log

http://msdn.microsoft.com/library/psdk/sql/8_ar_da2_1uzr.htm

DBCC SHRINKFILE

http://msdn.microsoft.com/library/psdk/sql/ts_dbcc_8b51.htm

Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[Tim1]

Hiya,

As a quick method, you can always try:

DUMP TRANSACTION database_name WITH no_log.

That should eliminate all information in the t-log.

Tim

 

[TomSark]

BACKUP LOG DBNAME WITH TRUNCATE_ONLY will only remove the inactive portion of the log. The active portion cannot be truncated.

And, if you are using an earlier version of SQL Server like 7.0, you may have to fill up a virtual log segment before you can truncate that virtual log. Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[dbmsguy]

what you might have is a transaction in progress. dump tran with truncate only affects the inactive part of the log.

try 'dbcc opentran' if there are open transactions, kill them (you may have to do this iteratively if you have more than one) and then do the truncate

paul

http:\\

http://wwww.dbmsguy.com

 

[DarkMan]

I had this problem for quite a while, and what I did that worked was:

Go into Enterprise Manager, then the database's properties.

Click on the options tag and change the database recovery model to 'Simple' and select the 'AutoShrink' checkbox.

This keeps the transaction log's file size at a useable level, and all is well...

I did this with a database that had a 4 Gig transaction file and it ended up as 1 Meg, and the autoshrink keeps it there.

NOTE: If you use this model, you will not be able to restore your database to the point of failure in the event it does fail, only to the last backup. So if you do this, be sure to back up your data regularly. I wouldn't use this setting on a mission critical database, but it probably wouldn't hurt to do this just for the shrink and change the model back to Normal... DarkMan

 

[Guest_imported]

if the t log is completely full SQL svr 65 cannot update sysindexes. (known bug) execute "dbcc checktable(syslogs)"
This will return corrections made and the amount of log space available.