Extreme server log growth with "decrypt encrypted objects"

MckMurrayMckMurray Posts: 37 Bronze 4
edited September 23, 2015 4:01PM in SQL Compare 11
I am new to SQL compare and was trying it out using the default project options when I noticed that every compare operation was adding ~400 MB to my error log file. Looking in the logs there were thousands of rows of what appear to be pages of data. Over the course of two days it completely filled up the available space on my drive.

With some troubleshooting this appears to be due to the option "decrypt encrypted objects", which I have since turned off.

Is this expected behavior?
· Share on Twitter

Comments

  • Mark JMark J Posts: 125 Bronze 5
    That's definitely not expected behaviour - if there isn't anything too sensitive then it would be useful to send some logs to support@red-gate.com so we could see what's going on.

    In the meantime, you should be able to change the logging options here: https://documentation.red-gate.com/disp ... +log+files - hopefully this fixes the problem for now.
    · Share on Twitter
  • MckMurrayMckMurray Posts: 37 Bronze 4
    Hi Mark, currently SQL Compare is set to "No Logging" so I have no SC-generated logs. To be clear, the logs I am talking about are the SQL Server error log files, the ones which can be found by going to Management > SQL Server Logs in SSMS.

    Elsewhere in the forums I came across this post from several years and many versions ago which may be related? I'm definitely seeing "DBCC 3604 ON" and "DBCC 3604 OFF" in the logs.
    https://forums.red-gate.com/viewtopic.php?t=10817
    · Share on Twitter
  • Mark JMark J Posts: 125 Bronze 5
    Oh, I see. Yeah, Compare needs DBCC TRACEON 3604 (also DBCC DBINFO and DBCC PAGE) to decrypt objects that have WITH ENCRYPTION set on them so that we can deploy them properly. Background: We're doing something similar to http://sqlity.net/en/1617/decrypting-en ... e-objects/ but using DBCC PAGE instead of the DAC in order to access the system base tables. I haven't heard of it causing a problem to this degree before, though - is there something else that could be filling up the log?

    I'm hoping we can take a look at the code for decrypting objects so that it spams 3604 less often - I think at the moment it's trying a bit too hard to be conservative (to make sure 3604 is never set when it shouldn't be)
    · Share on Twitter
  • MckMurrayMckMurray Posts: 37 Bronze 4
    OK, accepting that it is necessary, I'm curious why the results of DBCC PAGE would need to be sent to the errorlog for a compare operation. DBCC TRACEON 3604 should route the results back to SQL Compare's connection, right? Would that mean that a DBCC PAGE is being issued with 3604 off?

    Thanks
    · Share on Twitter
  • Mark JMark J Posts: 125 Bronze 5
    It's hard to find any official information, but from a quick test it seems the DBCC PAGE results should only get written to the errorlog if trace flag 3605 is set (instead of 3604), and if both flags are set it gets written to both places - is this something you might have set currently?
    · Share on Twitter
  • MckMurrayMckMurray Posts: 37 Bronze 4
    Sure enough for some reason 3605 was set globally, though I can't think why... turned it off.

    I'll try it again with the option enabled and see if that fixes it. Thanks

    Edit: Sure enough, that was the culprit - a compare operation that previously logged > 787 MB just finished at < 3MB. Thanks for the pointer!
    · Share on Twitter
Sign In or Register to comment.