"Unknown User" problem with ChangeLog-Database
Julia
Posts: 3 Bronze 1
Hello!
We are a team of developers who started using Source Control (v5.7) (shared database) in SQL Server Management Studio v17.3 about a month ago.
We have three servers and four databases on each server which are tracked by Source Control.
After initializing Source Control we had the "unkown user" problem as described in the following article:
https://documentation.red-gate.com/soc5/troubleshooting/object-changed-by-unknown
To solve our problem we created a ChangeLog-DB and edited the config file for each developer-SSMS as described in the following article:
https://documentation.red-gate.com/soc5/configuring/log-changes-to-shared-databases
But we sometimes still got "unknown user"-entries, so we created a test-table in each database to see why/when the "unknown user"-entries sometimes occured. We checked the test-tables in Source Control for a few days and the "unknown user" only occured when one night passed, on the same day the user was always correct:
On Server1 the user of the test-table in all databases was always correct.
On Server2 the user of the test-table in one database was sometimes correct and sometimes not, other three always correct.
On Server3 the user of the test-table in one database was sometimes correct and sometimes not, other three always correct.
(After I changed the test-table, the user was shown again, so I could test the next day)
Even though the test-table sometimes had a "unknown user" in Source Control, some other objects in the SAME Database still had the same "last changed"-username as the day before, some others not:
After a few days I updated my Source Control to v6.0.2.6908 (most of the team still uses v5.7) but the same behaviour occured.
The ChangeLog databases from Server1, Server2 and Server3 (compared via SQL Compare 12) are identical.
Is there any way to fix this?
If you need further information just ask.
Thanks in advance!
We are a team of developers who started using Source Control (v5.7) (shared database) in SQL Server Management Studio v17.3 about a month ago.
We have three servers and four databases on each server which are tracked by Source Control.
After initializing Source Control we had the "unkown user" problem as described in the following article:
https://documentation.red-gate.com/soc5/troubleshooting/object-changed-by-unknown
To solve our problem we created a ChangeLog-DB and edited the config file for each developer-SSMS as described in the following article:
https://documentation.red-gate.com/soc5/configuring/log-changes-to-shared-databases
But we sometimes still got "unknown user"-entries, so we created a test-table in each database to see why/when the "unknown user"-entries sometimes occured. We checked the test-tables in Source Control for a few days and the "unknown user" only occured when one night passed, on the same day the user was always correct:
On Server1 the user of the test-table in all databases was always correct.
On Server2 the user of the test-table in one database was sometimes correct and sometimes not, other three always correct.
On Server3 the user of the test-table in one database was sometimes correct and sometimes not, other three always correct.
(After I changed the test-table, the user was shown again, so I could test the next day)
Even though the test-table sometimes had a "unknown user" in Source Control, some other objects in the SAME Database still had the same "last changed"-username as the day before, some others not:
After a few days I updated my Source Control to v6.0.2.6908 (most of the team still uses v5.7) but the same behaviour occured.
The ChangeLog databases from Server1, Server2 and Server3 (compared via SQL Compare 12) are identical.
Is there any way to fix this?
If you need further information just ask.
Thanks in advance!
Answers
Thank you for contacting Redgate support and sorry to hear you're running into an issue!
My first suspicion is that the default trace is rolling over before SQL Source Control can get the username from it.
The documentation suggests that to improve the chances of the default trace being read before it rolls over, you can set the change indicators to update more frequently in the Setup tab. (Note though that this might slow down your server.)
Can I first just check if you've already tried this?
Jessica Ramos | Product Support Engineer | Redgate Software
Have you visited our Help Center?
Thank you for your answer.
No I haven't tried it.
It would make sense because the two databases which don't have correct usernames all the time are the databases which we change the most, but still the amount of changes isn't that big (about 10-20 changes/new objects each day with 100 lines in average).
The reason I didn't change the time interval already is because I don't understand what this time interval does and why it should help with our problem. When does Source Control check for updates? (If SSMS is opened? If Source Control is opened?) And which databases does it check?
Nevertheless I will try as you suggested, but it would be nice if you could answer me the above questions.
Currently the time is set to 25 seconds and I'm going to change it to 10 seconds. Do we need to change the time value in everyones Source Control?
I will monitor the databases this week with the decreased time interval.
By default, SQL Source Control only checks the username when you are actually using the tool and go to the commit tab for a database (it will then check for changes/usernames for all databases). SQL Source Control gets the username from your server's latest default trace file, but this file rolls over once it hits a certain size, so sometimes it rolls over before SQL Source Control can get the "last changed by" username.
When that interval polling option is enabled though, SQL Source Control will automatically check for changes/usernames more often which decreases the chance of that happening.
If all the users are working with the same databases, it should be sufficient to change it for just one user.
Please let us know if that does help or not!
Jessica Ramos | Product Support Engineer | Redgate Software
Have you visited our Help Center?
Thank you very much for your answer.
I'm sorry to tell you that the behaviour didn't change:
Server 1: everything correct (little changes)
Server 2: three databases correct (little changes), one database has (most of the days) the unkown user problem (medium changes: 10-20 changed objects)
Server 3: two databases correct (little changes), one database with little changes and one database with medium changes have the unkown user problem
Changing the time to 10 seconds didn't solve the problem.
Is there something else I can try to solve the problem?
Thanks in advance.
I'm going to continue troubleshooting this through your ticket #101396. You should receive an update shortly.
Jessica Ramos | Product Support Engineer | Redgate Software
Have you visited our Help Center?
https://documentation.red-gate.com/soc6/release-notes-and-other-versions/sql-source-control-6-0-release-notes
This doesn't change the fact that SQL Source Control only polls the default trace while SSMS is open, so if lots of changes are made without SOC/SSMS being open anywhere then trace entries could still be missed.
Redgate Software