意外的sp_MSForEachDB行为(Unexpected sp_MSForEachDB behavior)

 我正在努力增强我对一些系统sprocs的理解，我对我正在研究的这个脚本感到非常困惑。 为了理解sp_MSForEachDB我决定编写一个脚本来截断服务器上所有数据库的日志。 因此，我想出了以下脚本： 
 
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'
 
 事实证明，这有时只会成功截断数据库的日志。 在它失败的数据库上（没有错误消息，只留下一个未截断的日志文件），它一致/可重复地失败。 
 
 但是，在print语句中，它会完全打印出我希望打印的内容。 但是，如果我手动为每个数据库键入此脚本的功能部分： 
 
USE [Seed]
CHECKPOINT
DBCC SHRINKFILE('Seedlog', 1)
 
 它100％的工作时间。 
 
 为什么我的sp_MSForEachDB “循环”不能按预期工作？ 我错过了什么？ 

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?

原文：https://stackoverflow.com/questions/5567110