Home Forums SQL Server 2005 Backups Does trace flag 3226 suppress success messages on SQL2K? RE: Does trace flag 3226 suppress success messages on SQL2K?

  • Thanks for that forum reference. It, and subsequent detective work, enlightened me as to what's going on.

    The discussion there implied that using DBCC might not work after all, unless issued by the process that's actually doing the backups.

    So I rechecked BOL and found this corroborating statement: "New connections into the server do not see any trace flags until a DBCC TRACEON statement is issued. Then, the connection will see all trace flags currently enabled in the server, even those enabled by another connection."

    So I tried putting the TRACEON right into the same SQL Agent backup job, and even into the same job step. I was sure this would work, but it didn't.

    Then I realized I'm using a backup procedure derived from maintenance plans. Here's what the job looks like:

    dbcc traceon (3226)

    EXECUTE master.dbo.xp_sqlmaint [bunch of parameters here]

    I put this into Query Analyzer and it still logged the success. Then I changed the extended stored procedure call into a vanilla BACKUP statement. Voila, it worked!

    So it appears that either (1) an extended stored procedure, in general, executes in such a way as to ignore trace flage (e.g., creating a separate connection to the DB), or (2) this procedure explicitly ignores it or turns it off. Either way, to get rid of that flood of messages, I would have to change my whole backup process to eliminate that procedure, thereby losing the extra functionality such as logging backups to msdb and incrementally deleting the oldest backups.

    I'll still try setting the flag in the SQL startup parameters, but now I don't hold out much hope of it working. I'll find out Friday.

    Thanks again to everyone for your help.