get sql log file name from within a script

Question

We have a few test databases that we are throwing test indexes into and the log file gets bloated real quickly due to us dropping the contents of the table and repopulating it.

I have found, thanks to Stack Overflow, a few scripts and put them together to do what I need.

Here is the script:

USE SSSIndexes
GO
ALTER DATABASE SSSIndexes SET RECOVERY SIMPLE WITH NO_WAIT
GO
DBCC SHRINKFILE(N'SSSIndexes_Log', 1)   <-- my issue is here
GO

The problem is the log file name. Is there a way to get the log file name without having to look it up manually and include it in the script to where this part is automated?

Btw, we never intend on restore this database. These are temporary indexes.

Thanks!

Answer 1

USE SSSIndexes
GO
ALTER DATABASE SSSIndexes SET RECOVERY SIMPLE WITH NO_WAIT
GO
DECLARE @Name NVARCHAR(50)

DECLARE cur CURSOR FOR
SELECT [name]
FROM [sys].[database_files] 
where [type] = 1

OPEN cur
FETCH NEXT FROM cur INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC SHRINKFILE(@Name, 1)
    FETCH NEXT FROM cur INTO @Name
END
CLOSE cur
DEALLOCATE cur