November 12, 2007 at 11:39 am
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?
November 12, 2007 at 1:01 pm
> 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.
November 13, 2007 at 10:53 am
what version sql 2000 and SP level? as said, works for me. i tried using sql2k SE SP4
November 13, 2007 at 12:43 pm
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?
November 13, 2007 at 9:13 pm
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
November 14, 2007 at 1:01 pm
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.
November 16, 2007 at 10:55 am
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!
December 12, 2007 at 12:37 pm
Thank you for doing the leg work on this!
Regards,
Rubes
December 27, 2007 at 12:00 pm
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
December 31, 2007 at 1:03 pm
Interesting. This problem doesn't seem to exist in my SQL Server 2000 -- at least not working with the Enterprise Manager > [server name] > Properties, which is the only way I know to do it. It just has discrete slots for startup parameters and I added /T3226 to the end of the list. If there are any spaces, semicolons, etc. to be added, it adds them. I don't have to worry about it.
December 31, 2007 at 1:17 pm
It's always good to know the differences b/t the 2 products:) Microsoft loves to keep us on our toes and make us earn our pay!:) I just updated 7 of our dev instances so I can keep an eye on it. I've got 500 databases on each of my SQL 2k5 32 bit instances so 23 hourly tran log backups, plus a full backup every day, makes each of our errorlogs a complete nightmare (24 backup ops * 500 databases = 12000 rows of unneeded informational data every day)! I was wondering if you have encountered any oddities or any issues in your experience with using the flag? I'm rolling this change out to our production servers in a few weeks so any input would be helpful!
December 31, 2007 at 3:42 pm
Absolutely no problems. The thing keeps humming right along, on a 24/7 application.
September 12, 2008 at 12:49 pm
Use DBCC TRACEON (3226,-1) for global scope. I set it on my dev server and it's great! I'm planning on setting it in production next week:cool:
February 1, 2010 at 8:34 am
When I want to turn off the reporting of successful backups I do two things; first I go to the configuration manager and add the "-T3226" flag to the startup parameters. This will enable this flag on the next sql service startup. The second thing I do is run this in QA " DBCC TRACEON (3226, -1)"; this will enable this flag until it is stopped with either DBCC TRACEOFF or until the service is restarted; which when it is the startup parameter is now in place to enable the flag. This works for me. Good luck!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply