To the person who wants to know how to "Shrink the log file in Simple Recovery Model". 

If the file has grown to a size in SIMPLE recovery model, you probaly shouldn't shrink it.  There was a reason why it grew and it will just grow again. 

If you must use dbcc shrinkfile.  The only reason it wouldn't shrink is if there are open transactions.

HTH

Andy

To Kevin, who want to know how to do this in SQL 2000 and 2005.  You should be able to use this procedure in those versions.  Earlier versions also have backup log with truncate_only, but you really shouldn't use that.  Use this procedure.

HTH

Andy

Code for SQL Server 2005. Just needed to modify the declares in the proc as you cannot declare them and defined them in the same breath as you can in 2008.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[ns_shrink_db_log]

   @db_name SYSNAME
  , @target_size_mb INT
  , @backup_location NVARCHAR(200)
  , @backup_file_name NVARCHAR(200)
  , @maximum_attempts INT
 
/* Shrinks the log file of @db_name to the @target_size_mb
*
exec [dbo].[ns_shrink_db_log] 'scratch', 2, 'c:\temp\'
             , 'scratch_shirnk_backup', 4
************************************************************/
AS

SET NOCOUNT ON

SELECT @db_name = COALESCE(@db_name, DB_NAME())
    
DECLARE @logical_log_file_name SYSNAME,
        @backup_log_sql NVARCHAR(MAX),
        @shrink_sql NVARCHAR(MAX),
        @checkpoint_sql NVARCHAR(MAX),
        @db_id INT,
        @start_size_mb INT,
        @final_size_mb INT,
        @attempts INT,
        @recovery_model INT,
        @recovery_model_desc SYSNAME,
        @rc INT -- return code

SET @db_id = DB_ID(@db_name)
SET @attempts = 0
SET @rc = 0
SELECT @logical_log_file_name = name,
       @start_size_mb = size / 128
   FROM MASTER..sysaltfiles
   WHERE dbid=@db_id AND fileid=2
  
SELECT @recovery_model = recovery_model  
     , @recovery_model_desc = recovery_model_desc
   FROM sys.databases
   WHERE database_id=@db_id
  
PRINT 'Starting size of [' + @db_name + '].['
            + @logical_log_file_name
            + '] is '
            + CONVERT(VARCHAR(20), @start_size_mb) + ' MB '
            + ' recovery model = ' + @recovery_model_desc

IF @start_size_mb    PRINT '['+@db_name+'] does not need shrinking'
    END
   
ELSE BEGIN   

          
    IF @recovery_model != 3
        AND (@backup_file_name IS NULL OR @backup_location IS NULL) BEGIN
        RAISERROR ('Null backup file location or name. aborting.', 16, 1)
        SET @rc = 50000
        GOTO get_out
    END

   WHILE @attempts < @maximum_attempts
      AND @target_size_mb < (SELECT CONVERT(INT, size/128) FROM MASTER..sysaltfiles
                               WHERE dbid = @db_id AND
                                      name = @logical_log_file_name) -- not target
      BEGIN

        SET @attempts = @attempts + 1
       
        IF @recovery_model= 3 BEGIN
           SET @checkpoint_sql = 'use ['+@db_name+']; '
                               + 'checkpoint'
           PRINT @checkpoint_sql
           EXEC (@checkpoint_sql)
            END
        ELSE BEGIN
           SET @backup_log_sql =  'BACKUP LOG ['+ @db_name + '] '
                              + ' to disk = ''' + @backup_location
                              + CASE WHEN RIGHT(RTRIM(@backup_location), 1)='\'
                                    THEN '' ELSE '\' END
                              + @backup_file_name
                              + CONVERT(VARCHAR(10), @attempts)
                             + '.trn'''
           PRINT @backup_log_sql              

           EXEC (@backup_log_sql) -- See if a trunc of the log shrinks it.
        END
              
       SET @shrink_sql = 'use ['+@db_name+'];'
                       + 'dbcc shrinkfile (['+@logical_log_file_name+'], '
                       + CONVERT(VARCHAR(20), @target_size_mb) + ')'
       EXEC (@shrink_sql)
    END
END

SELECT @final_size_mb = size/128
   FROM MASTER..sysaltfiles
   WHERE dbid = @db_id AND name = @logical_log_file_name
  
PRINT  'Final size of [' + @db_name + '].['
            + @logical_log_file_name
            + '] is ' +
       CONVERT(VARCHAR(20),@final_size_mb)
       + ' MB'
   
get_out:
RETURN @rc

I have a SQL 2008 db that is currently around 130mb; however, the Transaction log file is almost 120gb and is taking up over half of available drive space.  I am pretty sure it is a candidate to shrink?  Looking for opinions before I do it.... Yes?

Keith,

I would check the database recovery model first.  I suspect it is Full.  Check out this tip - http://www.mssqltips.com/sqlservertip/1497/selecting-the-sql-server-database-recovery-model-to-ensure-proper-backups/.

If the database recovery model is Full and if there is no need to have it configured to Full for any high availability or disaster recovery needs, then I would consider setting the database recovery model to Simple.  The tip above shows the code to do so.  If you have a need to use the transaction log for high availability or disaster recovery needs, then I would correct that issue.

Once you have addressed these items, then I would shrink the database transaction log.

HTH.

Thank you,
Jeremy Kadlec
Co-Community Leader

 Dear sir ,

 i want to delete sql traction log files is it possibal and not effect data .

 if possibal then send me step by step process how to delete  that fils .

thanks 

Wow! Excellent! This log was getting way out of hand on my development machine and nothing I tried was working.

Thank you very much.