March 26, 2009 at 10:51 am
Last week, I turned the following flag on to start recording deadlock in the error log.
dbcc traceon (1204, 1205, 1222, 3406, -1)
Today, TL started to fail. Someone created a database and didn't start the chain with a full backup. However, when I went to check the error log to find out what database it was, there was no record of the TL failure. Going back to last week, there was no record of success or failure on any backups.
Now I have turned off all the trace flags I turned off, but I am still not receiving any logs stating success and/or failure of the backups (main TL at this point)
Running a dbcc tracestatus does not return any results, so I can only assume that there are no more flags active.
Does anyone have any thoughts on what the heck is going on here?
Thanks,
Fraggle
March 26, 2009 at 11:36 am
Fraggle (3/26/2009)
dbcc traceon (1204, 1205, 1222, 3406, -1)
None of the trace flags would not cause anything to Tlog backups. But, to trace deadlock graph why would you want to enable so many flags? 1222 would be suffice and it will give you the best information including the TSQL contributed for deadlock.
One this here, what is flag 3604 for?
As far as I know, the flags concerned to Tlog backups are:
Trace flag 3231 will turn NO_LOGS and TUNCATE_ONLY no-ops
Trace flag 3031 turns them in checkpoints in all recovery models
Flag 3226 to stop writing error messages to the errorlog
may be i am missing anything?
Did you check the backup tables and are the jobs really failing?
March 26, 2009 at 11:45 am
Have you looked in the SQL Server Logs in the Management Folder in SSMS Obect Explorer?
March 26, 2009 at 12:12 pm
Well I figured out the issue and it has nothing to do with the trace flags.
The drive were we keep the logs filled up. Here is the really funny reason why. Due to tempdb. This is funny, because my predecessor claimed to have moved all the tempdb to their separate drive. There is actually a separate drive already created for it, but it isn't even being use. DOH!. Move 16GB tempdb to it own drive, and what do we have.....space for logging errors. WOOT!
In regards to the question of what does the trace flag of 3604 do. It logs everything to the client. This was actually a typo. It should have been 3605, which logs everything to the error log. As for why so many flags, I wanted to see the output of them all for an actual deadlock.
Hope that clears everything else up.
Fraggle.
March 26, 2009 at 12:21 pm
Fraggle (3/26/2009)
As for why so many flags, I wanted to see the output of them all for an actual deadlock.
1222 would be suffice to see the actual deadlock output and 1223 for cascading locks.
March 26, 2009 at 12:25 pm
Thanks for the updates on the flags. Good to know.
Fraggle
March 26, 2009 at 12:35 pm
Fraggle (3/26/2009)
Thanks for the updates on the flags. Good to know.Fraggle
You are welcome. and also check this blog post too:
This is a great article, you can send notifications to your mail when a deadlock occurs. Just providing you as you were wanting to know more about deadlocks may be this could help a bit too.
March 26, 2009 at 12:38 pm
That looks like a great article I will have to read this weekend. Thank you very much.
Fraggle
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply