Octopus.Migrator.exe export 3.3.20 Long running transaction detected

Robert_Livermore · 4 July 2016 11:42

We have a process to export the Octopus Deploy server so contents of project and steps can be kept in source control. The 3.3.20 version of Octopus.Migrator.exe is throwing the following exception. Is the a command line parameter to increase the long running transaction timeout?

Console Output
Created D:\Ept\Releases\Update_SIM_ABC\Default\0.0.2\release.json
N+1 or long running transaction detected.
Stack: at Octopus.Core.RelationalStorage.RelationalTransaction.AddCommandTrace(String commandText)
at Octopus.Core.RelationalStorage.RelationalTransaction.ExecuteReader[T](String query, CommandParameters args)
at Octopus.Core.RelationalStorage.QueryBuilder1.First() at Octopus.Migrator.ExportProcess.ExportController.WriteVariableSetSnapshotForRelease(ExportContext context, IRelatio nalTransaction transaction, Release release, String directoryPath) at Octopus.Migrator.ExportProcess.ExportController.WriteReleases(ExportContext context) at Octopus.Migrator.ExportProcess.ExportController.Export() at Octopus.Shared.Startup.AbstractCommand.Octopus.Shared.Startup.ICommand.Start(String[] commandLineArguments, IComma ndRuntime commandRuntime, OptionSet commonOptions) at Octopus.Shared.Startup.ConsoleHost.Run(Action1 start, Action shutdown)
at Octopus.Shared.Startup.OctopusProgram.Run()
at Octopus.Migrator.Program.Main(String[] args)

2016-07-04T07:29:48 INSERT INTO dbo.[Event] (RelatedDocumentIds, ProjectId, EnvironmentId, Category, UserId, Username, O
ccurred, Message, Id, Json) values (@RelatedDocumentIds, @ProjectId, @EnvironmentId, @Category, @UserId, @Username, @Occ
urred, @Message, @Id, @Json)
2016-07-04T07:29:48 SELECT * FROM dbo.[Team] ORDER BY Id
2016-07-04T07:29:48 SELECT * FROM dbo.[User] ORDER BY Id
2016-07-04T07:29:48 SELECT * FROM dbo.[Certificate] ORDER BY Id
2016-07-04T07:29:48 SELECT * FROM dbo.[DeploymentEnvironment] ORDER BY Id
2016-07-04T07:29:49 SELECT * FROM dbo.[Feed] ORDER BY Id
2016-07-04T07:29:49 SELECT * FROM dbo.[Lifecycle] ORDER BY Id
2016-07-04T07:29:49 SELECT * FROM dbo.[Machine] ORDER BY Id
2016-07-04T07:29:49 SELECT * FROM dbo.[ProjectGroup] ORDER BY Id
2016-07-04T07:29:49 SELECT * FROM dbo.[UserRole] ORDER BY Id
2016-07-04T07:29:49 SELECT * FROM dbo.[Account] ORDER BY Id
2016-07-04T07:29:49 SELECT * FROM dbo.[Event] ORDER BY Id
2016-07-04T07:29:59 SELECT * FROM dbo.[ServerTask] ORDER BY Id
2016-07-04T07:30:00 SELECT * FROM dbo.[ApiKey] ORDER BY Id
2016-07-04T07:30:00 SELECT * FROM dbo.[Project] ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:00 SELECT * FROM dbo.[LibraryVariableSet] ORDER BY Id
2016-07-04T07:30:00 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT * FROM dbo.[ActionTemplate] ORDER BY Id
2016-07-04T07:30:01 SELECT * FROM dbo.[Channel] ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT * FROM dbo.[Release] ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Channel] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Channel] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[LibraryVariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Channel] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Channel] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Channel] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[LibraryVariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Channel] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[LibraryVariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Channel] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[LibraryVariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Channel] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[VariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[LibraryVariableSet] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[DeploymentProcess] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Project] WHERE (Id = @id) ORDER BY Id
2016-07-04T07:30:01 SELECT TOP 1 * FROM dbo.[Channel] WHERE (Id = @id) ORDER BY Id
Created D:\Ept\Releases\Update_SIM_ABC\Default\0.0.2\variable-set-snapshot.json
Created D:\Ept\Releases\Update_SIM_ABC\Default\0.0.2…\LibraryVariableSetSnapshots\ABC-variable-set-snapshot-v5.json

Michael_Noonan · 5 July 2016 01:32

Hi Robert,

Thanks for getting in touch! We’ve been adding a little bit of performance diagnostics to give us a sense for which areas need optimization. Unfortunately it is a bit noisy right now, and we are dialling this particular message down to Verbose instead of Warning. There is no way to configure this diagnostic logging in 3.3.20.

Can you confirm some things for me?

Did the export actually succeed (with this noisy warning) or did the export actually fail?
Was there an exception/error in the log, or was it just this noisy warning?

As far as I can tell, the export should have succeeded, and we will continue tuning when and where these kinds of logs are written. I would suggest upgrading to 3.3.21 when it ships and let me know if the logging is more suitable.

Hope that helps!
Mike

Rob_Livermore · 6 July 2016 11:02

Hi Michael,

            The count of the number of files exported seems correct. I’ll agree it was successful. When I read what was logged, I cannot determine the category of the log message between warning/information/exception/etc.

            A tuning tip I can pass along. When testing for existence of a row avoid the wildcard projection. Potently adds a bookmark lookup to the query plan depending on indexing type on ID and extra network latency to transfer data. Do not forget the semi-colon at end of statement.

USED
SELECT TOP 1 * FROM dbo.[Channel] WHERE (Id = @id) ORDER BY Id
TRY

SELECT TOP 1 1 FROM dbo.[Channel] WHERE (Id = @id) ORDER BY Id;

Thank you for the follow-up email.

Robert Livermore B.Sc. MCSD, MCSE+I, MCDBA
Software Developer
Cetaris
41 Constellation Court
Toronto, ON M9W 1K4
Office (416) 679-9555x2918
www.cetaris.com http://www.cetaris.com/

The information in this document is intended only for the parties specifically identified on this page and may contain confidential, proprietary and/or privileged material. Any unauthorized review, distribution or other use of or the taking of any action in reliance upon this information is prohibited. If you have received this document in error, please contact the author and delete or destroy all copies.

Michael_Noonan · 7 July 2016 01:59

Hi Robert,

Thanks for getting back to me, and don’t hesitate to reach out if you need anything else!

Happy Deployments!
Mike