3

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?

1 Answer 1

2

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

Sign up to request clarification or add additional context in comments.

1 Comment

Wow, quite the oversight. Good Catch! Thanks for your eyes. :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.