Unexpected sp_MSForEachDB behavior

Question

I'm working to enhance my understanding of some system sprocs and I'm very confused by this script I was working on. To exercise my understanding of sp_MSForEachDB I decided to write a script that would truncate the logs of all databases on a server. As such, I came up with the following script:

sp_MSForEachDb 'IF LOWER(rtrim(''?'')) NOT IN ('''', ''master'', ''tempdb'', ''tempdev'', ''model'', ''msdb'')
                BEGIN
                    declare @LogFile nvarchar(max)
                    USE [?]
                    SELECT @LogFile = sys.sysaltfiles.name FROM sys.sysdatabases
                    INNER JOIN sys.sysaltfiles ON sys.sysdatabases.dbid = sys.sysaltfiles.dbid
                    WHERE (sys.sysaltfiles.fileid = 1) AND (sys.sysdatabases.name = ''?'')
                    print ''DB: [?], Log: '' + @LogFile
                    CHECKPOINT
                    DBCC SHRINKFILE (@LogFile, 1)
                END'

It turns out that only sometimes does this successfully truncate the log of a database. On the databases it fails (no error message, just leaves me with an untruncated log file), it consistently/reproducibly fails.

In the print statement, however, it prints EXACTLY what I would expect it to print. However, if I manually just type out the functional part of this script for each database:

USE [Seed]
CHECKPOINT
DBCC SHRINKFILE('Seedlog', 1)

it works 100% of the time.

Why is my sp_MSForEachDB "loop" not working as expected? What am I missing?

Answer 1

It looks like your query is returning the logical name for the data file and not the log file? (fileID=1)