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?
It looks like your query is returning the logical name for the data file and not the log file? (fileID=1)