what can we do if just only want to truncate transaction log without backup ?

In some circumstances,  we just want to truncate transaction log without backup and refuce change database mode to "Simple".

You may want to say "You are make an unnecessary move". 

Yes, that is true.  However,. our customer just want to keep current setting and release transaction log usage and also refuse to backup them.

 

Here we can go to solve the problem:

 

You just can exec that procedure what ever your exist databases and their recover mode are.

 

 

 

CREATE PROCEDURE [dbo].[truncatetlog]

AS

BEGIN

 

 

DECLARE @m int

DECLARE @n int

DECLARE @SQL VARCHAR(MAX)

DECLARE @SQL2 VARCHAR(MAX)

DECLARE @SQL3 VARCHAR(MAX)

DECLARE @SQL4 VARCHAR(MAX)

DECLARE @SQL5 VARCHAR(2)

--DECLARE @SQL5 VARCHAR(MAX)

DECLARE @createtable VARCHAR(MAX)

DECLARE @createtable2 VARCHAR(MAX)

DECLARE @createtable3 VARCHAR(MAX)

DECLARE @createtable4 VARCHAR(MAX)

--DECLARE @droptableList TABLE (id int IDENTITY(1,1) NOT NULL,contents nvarchar(MAX) NOT NULL)

DECLARE @droptableList TABLE (databaseid int ,name nvarchar(MAX) ,logfile_name nvarchar(MAX),recovery_model_desc  nvarchar(MAX))

 

 

SET NOCOUNT ON

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

 

 

set @m=(SELECT MAX(database_id) from sys.databases where database_id >4)

 

 

set @n=5

--

 

 

while @n<=@m

begin

 

 

INSERT @droptableList (databaseid,name,recovery_model_desc) select database_id,name,recovery_model_desc from sys.databases where database_id = @n

update @droptableList set logfile_name = ( select name from sys.database_files where type=1 )where  databaseid = @n

 

 

SELECT @SQL = (SELECT name from @droptableList where databaseid=@n)

SELECT @SQL2 = (SELECT logfile_name from @droptableList where databaseid=@n)

SELECT @SQL3 = (SELECT recovery_model_desc from @droptableList where databaseid=@n)

SELECT @SQL4 = 'checkpoint'

 

 

set @createtable4='use ['+@SQL+'];'

exec(@createtable4)

 

 

--print @createtable4

 

 

set @createtable='ALTER DATABASE ['+@SQL+']  SET RECOVERY SIMPLE;'

exec(@createtable)

--print @createtable

 

 

set @createtable2='DBCC SHRINKFILE (N''' + @SQL2 + ''',50,TRUNCATEONLY);'

--print @SQL4

exec(@SQL4)

exec(@createtable2)

--print @createtable2

 

 

set @createtable3='ALTER DATABASE ['+@SQL+']  SET RECOVERY ' +@SQL3 +';'

exec(@createtable3)

--print @createtable3

 

 

set @n=@n+1

end

 

 

END

 

 

if that successed ?. just  execute them,  you also can make an schedule on it.  Then that id  OK