SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Does trace flag 3226 suppress success messages on SQL2K?


Does trace flag 3226 suppress success messages on SQL2K?

Author
Message
wodom
wodom
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 256
Last week there was a QOTD that dealt with using trace flag 3226 to suppress the success messages from backups. This would be the answer to my dreams -- except it isn't working for me.

The blog entry to which the question refers says you can do this by executing

DBCC TRACEON (3226);

So I put that into a SQL Server Agent job (so it will run every time SQL Server starts) and have now run it at least 3 times (twice manually and once after a restart), and I'm still getting my usual 400+ backup success messages every hour. I've also tried it through Query Analyzer.

Can anyone tell me: (1) Have they tried this on SQL Server 2000, and (2) Should the DBCC method be expected to work, or is there some reason this might have to be done as a startup parameter?



russell-154600
russell-154600
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 185
works for me in sql 2000.

dbcc traceon (3226)
go

sure u don't have it off in startup param?

is this the blog entry u r talking about?
wodom
wodom
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 256
> is this the blog entry u r talking about?

The blog entry that suggests "dbcc traceon (3226)", yes.

> sure u don't have it off in startup param?

Well, since I never even heard of this trace flag before last week, it would be really hard for it to be specified explicitly in the startup.

Even if it were, I find no startup option in BOL to turn a trace flag OFF at startup; only to them them ON. Probably because all trace flags simply default to off on startup.

And no matter what happens at startup, it should turn on (immediately) once I issue the DBCC command, as I have several times now. At least that's what I thought from reading BOL, etc.

Am I reading it correctly?

I did add "-T3226" to the startup params, to try turning it ON that way, the next time SQL is started. That will be on the next scheduled reboot Thursday night. But it seems like the DBCC solution ought to work.



russell-154600
russell-154600
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 185
what version sql 2000 and SP level? as said, works for me. i tried using sql2k SE SP4
wodom
wodom
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 256
SQL2K Standard Edition, version 8.00.818 (SP3)

Haven't put in SP4 yet - it's a 24/7 app and we need to schedule it at night.

Did this trace flag suddenly show up in SP4? Doesn't sound too likely....

Trying again, am I correct in thinking that "dbcc traceon (3226)", executed in SQL Server Agent or Query Analyzer, should take effect immediately?

And does it matter which database I'm "in" when the command is given?



SQL_ABD
SQL_ABD
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1536 Visits: 1349
Hi,
It seems that the flag 3226 has been introduced in SQL Server 2000 before SP4.
Check this http://www.thescripts.com/forum/thread162385.html
Regards,
Ahmed
wodom
wodom
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 256
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.



wodom
wodom
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 256
Well, whaddya know -- IT WORKED!

(I've seen it documented as -T3226, but BOL says use /T3226 so I switched to that syntax.)

So it appears that using DBCC to set a trace flag has only a "local" scope, whereas setting it in the startup parameters has a "global" scope. I have not seen this difference mentioned anywhere.

It is SOOOO nice to see a CLEAN and navigable event log at last!

Thanks again to all that helped!



rubes
rubes
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 398
Thank you for doing the leg work on this!

Regards,
Rubes
abair34
abair34
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 315
Just an FYI I found out while testing that the startup parameters are sometimes a little fussy. I was changing the startup params by going to

Start->Programs->MS SQL SERVER 2k5->Config Tools->SqlServerConfigMgr
Right click the sql server service->properties->Advanced Tab

The Startup Parameters field is the one we're concerned with... I noticed that you can give yourself a big headache if you just think that you can drop -t3226 or /t3226 in there anywhere. The service would not start back up if I didn't remember to put a semicolon on the line before adding the switch (it considered the tag /T3226 to be part of the path for the previous switch). After repeated tests I found out that a blank space between the newly added semicolon and /T3226 caused it to ignore the flag. So be careful when you're crafting your parameters cause even though it would start back up with the extra space there all the downtime would have been for nothing.

The Working Value for my instance is this: (for illustrative purposes only. the values for -d -e and -l flags are instance dependant.)

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf;/T3226
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search