SQL Server - How to Truncate Transaction Log in SQL2000 usin SQLDMO

in SQL Server 2000

backup log [database name] with truncate_only
(run this in query analyzer)

(open enterprise manager and expand the server tree)
--right click on the database
--all task
--shrink database
--bottom button (on right side of form) for files
--select sql log file (default is data file so make sure and change it)
--click ok

the log size of my main database was 67 gigs. I ran this and it shrinked my database size from 67 gigs to 50 megs (49 of which is the data file)

reference

http://forums.databasejournal.com/archive/index.php/t-40824.html

Truncate SQL Server Transaction Log

http://www.codeproject.com/cs/database/ClearTransactionLog.asp

DBCC statememnts act as Database Console commands. You can use the below listed queries, sprcifically the DBCC ones to shrink your transaction log.

-- To GET the name of the log and data files
select name from dbo.sysfiles

-- SEE the active transactions as well as the File ID
dbcc loginfo('db_Name')

-- Mark transactions inactive so that they can be truncated later
-- Suppose file ID is 2

DBCC SHRINKFILE (2)
-- Backup the log file and truncate the inactive entries
BACKUP LOG [db_name] WITH TRUNCATE_ONLY
-- Run Shrinkfile again for maintainence.
DBCC SHRINKFILE (2)

You can create a stored procedure out of the last 3 statements and schedule it as a job on your server than can run overnight and ensure that the transaction log file size does not get out of hand.

Hope this helps.