"Unknown" Changed By Field

SteveGTRSteveGTR Posts: 91
edited June 8, 2012 10:42AM in SQL Source Control Previous Versions
We're doing our initial tests and we've been getting "unknown" changed by fields almost as a rule.

1) User A signs into SSMS using a SQL login account
2) User A changes a stored procedure
3) User B signs into SSMS using a SQL login account
4) Goes to Commit Changes page and the change User A made is annotated as "unknown"
5) User C signs into SSMS using Windows Authentication (to see if this works better)
6) User C changes a stored procedure
7) User A and user B go to the Commit Changes page and the change User C made is annotated correctly (change by is User C's account)
8) User B is in SSMS and goes back to Commit Changes page and clicks the refresh button
9) Now User C changes have the "unknown" changed by field
10) User A sees the same thing

I've seen the posts regarding setting the frequency of the RedGate polls, etc... I'm getting this by hitting the refresh button, so I'm manually polling the system.

Thanks
· Share on Twitter

Comments

  • SteveGTRSteveGTR Posts: 91
    I repeated the test using a SQL login account and the changed by field appears for a time and then disappears, transformed into "unknown".
    · Share on Twitter
  • SteveGTRSteveGTR Posts: 91
    I see now what all the talk about the default trace is now. I've been monitoring our trace file and can understand why the changed by fields are being displayed as unknown after some time. The trace files are restarted after a time and new information is captured. This paragraph from http://www.simple-talk.com/sql/performa ... -auditing/ sums up the problem well:

    "The default trace is a very powerful way to examine the health and the security of your SQL Server instance. There are several pitfalls to keep in mind – mainly related to file rollovers and size limitations, but with some programming the workarounds are not impossible. It is important to remember that the queries presented in this article will return the result from the single most recent default trace file. Depending on how busy the SQL Server instance is, the files may roll over way too fast for a DBA to catch all significant events; therefore, some automation is needed."
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    Hi Steve,

    You've hit the nail on the head. The default trace rolls over and the information goes with it. We're in the process of optimising how we use the default trace to ensure it rolls over less frequently. We'll post to this thread as soon as we have a build for you to try out.

    Thanks for drawing this to our attention.

    Kind regards,

    David Atkinson
    Red Gate
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • SteveGTRSteveGTR Posts: 91
    I'm at a loss of what to tell our implementation team with regard to this issue. When can we expect a fix?

    Thanks :)
    · Share on Twitter
  • SteveGTRSteveGTR Posts: 91
    Does the prior release (before version 3) exhibit this problem? I'm looking at ways to use your software. As it is, this bug is a deal breaker...
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    Yes, this problem has always been present. I'm chasing up the SQL Source Control project manager to estimate when a build might be ready. This will most likely be a pre-release build for you to try so we can test whether the changes improves it for you.

    David
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • SteveGTRSteveGTR Posts: 91
    I give up... Does anyone use the shared database model? If so, how are you handling the "Unknown" changed by problem?

    It's hard for me to understand how this product can be used in a multi-developer environment when the changed by field goes out of scope (becomes "Unknown") in such a short time frame. In our case, 15 minutes and all the pending changes come up as "Unknown".

    Why RedGate would implement such an unreliable technique as the default trace for the changed by field is beyond my comprehension. Maybe someone could explain it to me.

    Thanks :)
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    Hi Steve,

    Thanks for your question. The reason the default trace is used is because it is enabled by default (thereby requiring no additional configuration) and supplies us with the information we need. The challenge is that the default trace will roll over, and for some users too quickly. 15 minutes is very quickly.

    We're currently investigating alternatives, including persisting this in our own data store. The drawback is that this requires DBAs to administer yet another database, which is an implementation that could be too intrusive for some, but it could be the only option.

    I hope this answers your question. We'll be aiming to release on a more frequent cycle, so hopefully we'll be able to further improve what is understandably a popular feature.

    Out of interest, what is the reason you are choosing a shared development environment over dedicated per-developer ones?

    David Atkinson
    Product Manager
    Red Gate
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • SteveGTRSteveGTR Posts: 91
    Thanks for the reply David.

    I'm actually looking for a response from someone who uses your product with the shared database model in a team environment.
    · Share on Twitter
  • SteveGTRSteveGTR Posts: 91
    We are choosing the shared environment because it reflects how we currently work.

    In addition, I like that other developers can see what is being worked on by other developers.

    That is why the "Unknown" changed by bug is so important to me.

    In a perfect world the source control system we used would:

    1) Manage the SQL scripts automatically for us --- RedGate does a great job with this

    2) When someone is working on a SQL object others developers should be able to see that they are doing the work --- RedGate's shared model should do this, but the "Unknown" bug is causing this to fail

    The reason why I feel it is important to communicate when a change is being made is because stored procedures don't merge well when two people are working on them at the same time. If someone sees that another person is working on a SQL object they can work more carefully. This avoids duplication of effort, wasted effort, and errors.

    Don't get me started... oh! Too late ;)
    · Share on Twitter
  • SteveGTRSteveGTR Posts: 91
    Are you folks going to fix this problem? The last I heard was that it wouldn't be ready until March, but there was a concern regarding SSC users about maintaining the information in database.
    · Share on Twitter
  • PDinCAPDinCA Posts: 642 Silver 1
    I, too, am astounded at the rapid appearance of "Unknown"!

    I added an SP this morning and within three hours "I" became "Unknown". There's NOBODY ELSE on the server - I'm the ONLY DB Developer... There are a few background jobs acquiring fresh data from Production but they're only actually creating rows on 5 of 60 executions per hour.

    I wouldn't expect MY default trace file to roll-over in just 3 hours!

    In my case, as I'm "it", I know whodunnit, but it is still a BIG BUG crying out for a rapid fix (IMO).

    When, please?
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
    · Share on Twitter
  • arthurblakearthurblake Posts: 3
    David Atkinson wrote: "Out of interest, what is the reason you are choosing a shared development environment over dedicated per-developer ones? "

    We use a shared dev environment because the database is too large to replicate for all the developers (well over 500GB.)

    Your question makes me wonder if the shared development model is not as well supported or implemented as the per-developer model...

    Could you potentially use a db trigger to detect changes to the database?
    Here's a sample trigger I wrote once to audit db changes to a table...
    CREATE TRIGGER [DDL_AuditTrigger]
ON DATABASE
FOR

CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION,
CREATE_INDEX,ALTER_INDEX,DROP_INDEX,
CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE,
RENAME,
CREATE_TABLE,ALTER_TABLE,DROP_TABLE,
CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER,
CREATE_VIEW,ALTER_VIEW,DROP_VIEW,
CREATE_XML_INDEX

AS
DECLARE @eventData xml;
DECLARE @time datetime,
@user varchar(50),
@object varchar(50),
@tsql varchar(max);

SET ARITHABORT ON

SELECT @eventData = EVENTDATA();

SELECT @user=@eventData.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(50)'),
@time = @eventData.value('(/EVENT_INSTANCE/PostTime)[1]','datetime'),
@object= @eventData.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(50)')+'.'+@eventData.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(50)'),
@tsql = @eventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)');

INSERT INTO dbo.DDL_Audit VALUES(@object,@user,@time,@tsql);

GO

DISABLE TRIGGER [DDL_AuditTrigger] ON DATABASE
GO

ENABLE TRIGGER [DDL_AuditTrigger] ON DATABASE
GO
    · Share on Twitter
  • mgordonzaismgordonzais Posts: 1
    David,
    Is there a way to vote on issues to change priority? It's not transparent to us users which issues are higher on your list and when the expected release is.

    Thanks,
    Michael
    · Share on Twitter
  • arthurblakearthurblake Posts: 3
    @mgordonzais

    Yes, just click the little feedback link in the tool itself!

    Or go here: http://redgate.uservoice.com/forums/390 ... ce-control
    · Share on Twitter
  • csmithcsmith Posts: 138 Bronze 1
    edited March 2, 2012 8:58AM
    Hi Michael,

    We have a feedback site where you can vote for changes you'd like to see in the product called UserVoice. There is already an entry for the ChangedBy = Unknown issue, which you can vote on.

    In terms of priority - this is something that we are looking at right now. We want to make sure that our solution meets the requirements of as many users on the Shared Model as possible and have consulted a number of you regarding how best to do this. However, this feedback has indicated that we need to make a more significant change than we originally envisaged, so we don't expect to get the solution out until Q2 (2012).

    I'll update this post when we have news on an Early Access or Release build that includes this work.

    Best regards,
    Chris
    Divisional Development Lead
    Redgate Software
    · Share on Twitter
  • arthurblakearthurblake Posts: 3
    FYI:

    He posted an "admin" link to the page (I think, accidently...)
    The correct link for us users is:

    https://redgate.uservoice.com/forums/39 ... -says-unkn
    · Share on Twitter
  • csmithcsmith Posts: 138 Bronze 1
    Yep, it was an accident. Thanks for clearing that up.
    Divisional Development Lead
    Redgate Software
    · Share on Twitter
  • SteveGTRSteveGTR Posts: 91
    Who do we talk to regarding getting our money back? It looks like we will be pursuing other products that can meet our needs...
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    @Steve - Thanks for your question. I've emailed you the necessary details in a separate email.

    David Atkinson
    Red Gate
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • SteveGTRSteveGTR Posts: 91
    We looked around and there are not any other products that perform to the level of your product. We'll just have to work around this problem for now and patiently wait for a fix from you guys. Thanks :)
    · Share on Twitter
  • csmithcsmith Posts: 138 Bronze 1
    Hi

    An Early Access Release of SQL Source Control v3.1 is now available that includes a solution to the 'Changed by = Unknown' problem.

    For more details, and a download link, please see:
    http://www.red-gate.com/MessageBoard/vi ... hp?t=15345

    Thanks,
    Chris
    Divisional Development Lead
    Redgate Software
    · Share on Twitter
Sign In or Register to comment.