3 Replies Latest reply on Aug 11, 2015 9:26 AM by cjensen

    Unable to update definitions in SQL database

    Rookie

      Hi,

      We've started having issues when updating the patch definitions. This prevents the running of scans.

       

      Running the 'refresh files' process fails with the error;

      "Failed to update the database with new deifinitions."

      and

      "The transaction associated with the current connection has completed but has not been disposed.  The transaction must be disposed before the connection can be used to execute SQL statements."

       

      Initial thoughts were the size/vintage/upgrades that the db has had, so a fresh one was created. This has been ok for a few weeks but the error has returned...

       

      looking at the st.protect.managed(username).log reveals:

       

      2015-08-10T03:28:02.8549135Z 0007 E WorkItemCatalogDataImport.cs:147|ST.UI.UserViewableException: Failed to update the database with new definitions. ---> System.InvalidOperationException: The transaction associated with the current connection has completed but has not been disposed.  The transaction must be disposed before the connection can be used to execute SQL statements.

         at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)

         at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)

         at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

         at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)

         at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

         at ST.Data.CatalogData.DataAdapters.PatchDataAdapter.UpdatePatchProductCveLink(DataTable patchProductCveDataTable, SqlConnection connection)

         at ST.Data.CatalogData.DataAdapters.PatchDataAdapter.UpdateNonBulk(PatchUpdateDataSet patchData, String sysXmlVersion, SqlConnection connection)

         at ST.Data.CatalogData.DocumentParsers.Hf7Parser.PersistModelInternal(AssessmentData model)

         at ST.Data.DbAccessLayer.AttemptTransaction.Try[TArgument1](Action`1 transactedOperation, TArgument1 argument1)

         at ST.Data.CatalogData.DocumentParsers.Hf7Parser.PersistModel(AssessmentData model)

         at ST.Data.CatalogData.CatalogDataFileImporter.ExecuteCatalogImport(Func`1 createModel, Version currentFileVersion, String sqlConnectionString, Hf7Parser parser)

         at ST.Data.CatalogData.CatalogDataFileImporter.ImportPatchDataDocument(Func`1 createModel, Version currentVersion)

         at ST.BusinessObjects.FileDownload.CatalogImportOperation.Import(Boolean alwaysImport)

         at ST.UI.Controllers.CatalogData.CatalogImportController.UpdateDatabase(Boolean alwaysImport)

         at ST.UI.Controllers.Tasks.WorkItemCatalogDataImport.<RunAsync>d__0.MoveNext()

         --- End of inner exception stack trace ---

       

      Which seems to indicate a deadlock condition in the database - the question is why? The DB server is MSSQL 2012 on Windows Server 2008R2, Console is v9.1.0 Build: 4511 on Windows Server 2008R2, SQL load is low (currently only used for 'internal' applications like Shavlik, backups catalogs/antivirus etc.)

      Both machines are ESX5.5 VM's on 10Gbe iSCSI SAN storage and vFRC allocated to 'workload' volumes. The SQL VM is provisioned with 20GB vRAM and 4 vCPU's. Console VM has 8GB & 4 vCPU's,

       

      Any help appreciated!

        • 1. Re: Unable to update definitions in SQL database
          cjensen SupportEmployee

          Hello,

           

          After looking into this error, I believe the issue may be stemming from a timeout within the Shavlik application itself. Please try changing the database timeout value within the configuration files by following the process outlined in the link below:

           

          How To Increase The Database Timeout Period For Protect

           

          If the issue persists following this change, please let me know. We will need to look at some other variables and see what is going on.

           

          Thanks,
          Channing

          • 2. Re: Unable to update definitions in SQL database
            Rookie

            Unfortunately that hasn't resolved the issue - the error still occurs after a few minutes of starting to update the patch definitions.

            • 3. Re: Unable to update definitions in SQL database
              cjensen SupportEmployee

              Hello,

               

              I'm sorry to hear that didn't seem to make a difference. I believe we will need to look further into the database. Please open a case with our support team at support.shavlik.com or contact them at 1-800-690-6911 and we can take a further look. If you open the case via the support portal, please attach a fresh set of Console logs by following the process outlined below:

               

              Protect 9.X console logging:

               

              1. Please open the Protect GUI and then go to Tools > Options > Logging and change logging to “All” for both user interface and services.

              a. If you are unable to set logging via the GUI see this doc: http://community.shavlik.com/docs/DOC-22938

              2. Close the Protect GUI.

              3. Stop the following services

                   a.Shavlik Protect Console Service

                    b. ST Remote Scheduler Service

              4. Delete all the logs from

                   a.  Windows 7, 8, 2008, 2012 & Vista: C:\ProgramData\LANDesk\Shavlik Protect\Logs

                   b.  Earlier OS’s:  C:\Documents and Settings\All Users\Application Data\LANDesk\Shavlik Protect\Logs

              5.  Start the console service and open the Protect GUI.

              6. Attempt to reproduce the issue. Please document steps to reproduce.

              a. Collect the logs from the Logs folder mentioned earlier in step 4 (please zip if possible)

              b. [Deployment issues only] On the target system please zip and send a copy of the entire C:\Windows\Propatches folder and its contents (you can leave out the Patches sub-folder).

              7. Zip and send all the logs.


              If you have trouble opening a case please feel free to email the logs to me at channing.jensen@shavlik.com and I will create a case for you.


              Thanks,

              Channing