SQL TRANSACTION LOG SHRINK/TRUNCATE

use [DBNAME]
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE
declare @fileId as int = (select file_id from sys.database_files where name like '%log%')

DBCC SHRINKFILE(@fileID, EmptyFile)
DBCC SHRINKDATABASE ([DBNAME], 0, TRUNCATEONLY);

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

IN THIS YOU CANT EASILY GET DB LOG NAME IN @FILEID



For Multiple Database, We can use Cursor like below

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200)
DECLARE @Command2 nvarchar(200)
DECLARE @Command3 nvarchar(500)
DECLARE database_cursor CURSOR FOR 
SELECT name 
FROM MASTER.sys.sysdatabases

OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0 
BEGIN 
IF (@DB_Name <> 'master' AND @DB_Name <> 'tempdb' AND @DB_Name <> 'model' AND @DB_Name <> 'msdb') 
 BEGIN
--SELECT @Command = 'SELECT ' + '''' + @DB_Name + '''' + ', SF.filename, SF.size FROM ' +  @DB_Name + '.sys.sysfiles SF'
     SELECT @Command ='ALTER DATABASE [' + @DB_Name + '] SET RECOVERY SIMPLE'
     EXEC sp_executesql @Command
     Select @Command = 'use [' + @DB_Name + '] ' 
     Select @Command2 = @Command + 'SELECT name ,size/128.0 - CAST(FILEPROPERTY(name,' + ''''+  'SpaceUsed'+ '''' +') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files'
     select @Command3 = 'declare @fileId as int = (select file_id from sys.database_files where name like ''%log%'');DBCC SHRINKFILE(@fileID, EmptyFile);DBCC SHRINKDATABASE ([' +@DB_Name + '], 0, TRUNCATEONLY);'
     EXECUTE sp_executesql @Command2;EXECUTE sp_executesql @Command3;EXECUTE sp_executesql @Command2 --exec (@Command2)
  End
     FETCH NEXT FROM database_cursor INTO @DB_Name 
END

CLOSE database_cursor 
DEALLOCATE database_cursor