Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Does trace flag 3226 suppress success messages on SQL2K? Expand / Collapse
Author
Message
Posted Monday, November 12, 2007 11:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:35 PM
Points: 134, Visits: 192
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?



Post #421160
Posted Monday, November 12, 2007 12:21 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 15, 2013 4:46 PM
Points: 69, 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?
Post #421175
Posted Monday, November 12, 2007 1:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:35 PM
Points: 134, Visits: 192
> 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.



Post #421187
Posted Tuesday, November 13, 2007 10:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 15, 2013 4:46 PM
Points: 69, Visits: 185
what version sql 2000 and SP level? as said, works for me. i tried using sql2k SE SP4
Post #421703
Posted Tuesday, November 13, 2007 12:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:35 PM
Points: 134, Visits: 192
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?



Post #421741
Posted Tuesday, November 13, 2007 9:13 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 2:52 PM
Points: 1,326, Visits: 1,289
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
Post #421880
Posted Wednesday, November 14, 2007 1:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:35 PM
Points: 134, Visits: 192
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.



Post #422276
Posted Friday, November 16, 2007 10:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:35 PM
Points: 134, Visits: 192
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!



Post #423160
Posted Wednesday, December 12, 2007 12:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 10:27 AM
Points: 136, Visits: 398
Thank you for doing the leg work on this!


Regards,
Rubes
Post #432497
Posted Thursday, December 27, 2007 12:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 5:19 PM
Points: 30, Visits: 257
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
Post #436802
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse