transaction log and file size

Try this. Put it in your master databse and run it IN the database where you want to shrink the log. If you run it in a production DB you should back it up immediately afterward.

Run it like this:

Use MyDatabase

GO

EXEC sp_trx_log_shink 2, 200

You may have to run it 2 or 3 times.

Here's the proc code:

create Procedure sp_trx_log_shrink (@MaxMinutes INT,
@NewSize INT )
/*=========================================================================
Title: Shrink SQL Server Transaction Log Stored procedure
Script C:\Docs\SQL_Scripts\Administrative\Maintenance\Logs\sp_trx_log_shrink.sql

Purpose: system proc based on
INF: How to Shrink the SQL Server 7.0 Transaction Log
Force shrink transaction log of current database to
specific size.

Params: @MaxMinutes = Max number minutes to run before stoppint
(recommend 2 at least)
@NewSize = New size in MBs of the log file after shrinking
(recommend at least 10 MB in most DBs)

Create/Update History:
10/31/2005 9:38:24 PM - Pre-delete DummyTrans table if exists.
3/9/2005 3:33:44 PM - Converted to procedure.

Notes:
Assumes only 2 physical database files and that _Data file
is file id 1 in sysfiles table and that log file is file id 2.
Original Source:
http://support.microsoft.com/default.aspx scid=kb;EN-US;Q256650
Microsoft Knowledge Base Article - 256650 ;
=========================================================================*/
AS
SET NOCOUNT ON
DECLARE @err int
DECLARE @LogicalFileName sysname
--DECLARE @SSQL as VARCHAR(255)
DECLARE @DBN as nVarchar(50)

-- Setup / initialize
DECLARE @OriginalSize int
set @DBN = (select db_name())
PRINT 'Database: ' + @DBN

SET @LogicalFileName = (SELECT FILE_NAME (2))

PRINT 'Log logical filename: ' + @LogicalFileName
PRINT ''
EXEC sp_helpdb @DBN

SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName

IF OBJECT_ID('DummyTrans') IS NOT NULL
DROP TABLE DummyTrans

CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)

-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)

EXEC (@TruncLog)

-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles
WHERE name = @LogicalFileName) -- the log has not shrunk
-- IF thhe value passed in for new size is smaller than the current size...
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
-- Because it is a char field it inserts 8000 bytes...
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF

IF @err <> 0
BEGIN
RETURN(@err)
PRINT '*** SHRINK FAILED!!! ***'
END

IF @err = 0
BEGIN
PRINT '*** Perform a full database backup ***'
END