LogsRemoveByDaysAndCount Error

[Solution_Provider]

Greetings!
After upgrading from version 2012 Patch7 to 2015 Patch2 using SQL Database Environment, the system connects to the database but keeps spinning the “radioactive donut” for several minutes and then pops an error with a message:
“An error occured on executing an sql statement LogsRemoveByDaysAndCount Timeout expired.”
…with only an [OK] button to choose from. Then a few minutes after clicking on it, the error pops up again and everything works fine after clicking on it for the second time.
I can see a few folks having similar problems, but I haven’t found out how they fixed this situation. Maybe the logs from the 2012 had to be truncated before upgrading? It seems to me that the system wants to trim those logs and something goes wrong in the process. What is the recommended action to take in this situation?

[DevOma]

It is planned to release another patch version today or latest at Monday - this should solve the problem

[Solution_Provider]

Following is the stack trace produced upon failures. (There is another group of identical error stacks with the only difference being the statement name. One of them is “LogsRemoveByDaysAndCount” and the other “LogsRemove”)

An error occured on executing an sql statement LogsRemoveByDaysAndCount Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
---------------------------
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

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 CloudAdminDataAccess.SqlDataAccessor.LogsRemoveByCount(LogTypes logType, Int32 count)

---

It is planned to release another patch version today or latest at Monday - this should solve the problem

WOW! You guys are awesome! I just barely finished posting the stack traces and you’re already coming up with a solution! Those are great news! Thank you very much for your prompt response! I’ll be standing by. Much appreciated! =-)

[redfoxert]

Having the same issue, except that we get an error before this one:

So in proper order:

- Start ASGRD2015 (after upgrading from a 2012 Patch8 DB)
- Waiting a really long time with an hourglass mouse cursor (3-4 minutes, sometimes even 15)
- SQL error: An error occured on executing an sql statement LogsAdd
- SQL error: An error occured on executing an sql statement LogsAdd
- SQL error: An error occured on executing an sql statement LogsRemoveByDaysAndCount

After this the program works, for as far as i've been able to test

Hope Patch3 will be released soon. This delay in startup prevents us from going live with ASGRD2015 (40 users, 1500 connection entries)

[Solution_Provider]

- SQL error: An error occured on executing an sql statement LogsAdd

Interestingly enough, I now also receive an error on LogsAdd in addition to the two LogsRemoveByDaysAndCount. I will check back later on to see if the new patch has been posted…

[redfoxert]

Got another different one this evening:

BaseItemGetAll

Followed by an unhandled exception error

[redfoxert]

Upgrades to Patch3 today, removed the ASGRD2015 database and did a new import to a new database of our old 2012 environment.

Unfortunately the SQL errors are still present. LogsAdd, LogsRemoveByDaysAndCount and BaseItemGetAll are shown in random order. Software still starts very very slowly.

Another thing i've noticed:

Database size 2012: 362MB
Database size 2015: 1103MB

Is it expected behaviour for a database migration to increase three times in size?

[Solution_Provider]

Got another different one this evening:
BaseItemGetAll
Followed by an unhandled exception error. Another thing i've noticed:
Database size 2012: 362MB
Database size 2015: 1103MB

I have not received the "BaseItemGetAll" error but when I read your message I checked and my situation is quite similar:

Database size 2012: 250.31 MB
Database size 2015: 1215.69 MB

And I can see that Patch 3 has been released, so I will try it as soon as I can...

[Solution_Provider]

Well… I installed Patch 3 and my problems did not go away =-(
So I decided to make a backup of both my 2012 and 2015 Environments just in case; and then opened my 2012 environment and actually go to /Tools/Options/Logs and in my case all options were unchecked, so I checked them all and configured them to 30 days and 500 maximum entries to match the 2015 default settings.
Then I closed the environment and opened it again in the hopes of the 2012 logs to get truncated and then closed the 2012 environment.
Once I did that, I opened my 2015 environment and deleted the whole database environment and closed it. And then, visited the SQL Server and deleted the database I created for the 2015 environment including the SQL user. I also selected the 2012 database, right-click on it and selected /Tasks/Shrink/Database then logged out of the SQL Server.
Back on the 2015 Patch 3 installation, I created a brand new SQL database, connected to it and chose “/Environment/Upgrade of 2012 Environment…/Specify database access directly” and placed a check mark on “Delete existing data in the current environment before migrating the data” and accepted the warning with [Yes]. Then clicked on [Next] and filled in the server and database information for my 2012 database.
This time the process finished quite faster and good news! After opening the 2015 Environment it connected very quickly and received no more error messages. I closed it and opened it several times just to ensure functionality, and it is working very well for me at the moment.
Just for curiosity, I looked at the database sizes and here is what I have now:
Database size 2012: 24.49 MB
Database size 2015: 25.68 MB
Hopefully this information will be helpful for other folks experiencing these problems.
Happy remote controlling!

[DevOma]

Thanks for all your informations - I have to check why all logs are loaded at startup

[redfoxert]

When trying to remove the Logs (Security, Error) from the new ASGRD2015 database i'm getting the following SQL error:

An error occured on executing an sql statement LogsRemove

[DevOma]

That's really hard to understand - the table has valid index configuration - but it took too long to execute the statement - have to check it again

Can you try to delete some logs directly with MS SQL Management Studio - I will think about what causes this time consumption


you can exec the stored proc

exec LogsRemove - use as parameter 0, 1, 2 (in this order for Error, Change and History logs)

[redfoxert]

Hi Oliver,

Unfortunately i'm not an SQL hero The query kept failing with a wrong syntax error so I've manually selected all items in the various logfiles and used the "Delete Selected" option. This worked, all logfiles are now empty. It is still really slow though, in the left bottom corner it says "Loading data ..." for a very long time.

Then the error LogsRemoveByDaysAndCount shows up again.

However, this does worry me as we have 40 users connecting to this database on a daily basis. Logs will start to fill pretty rapidly and with this kind of behaviour it would appear that over time the speed of starting the application will become a problem again. How do you look at this?

Regards.

[DevOma]

We had the same issue in our company in another department - and I thought I solved the issue-startup time increased rapidly.

Would it be able to get a backup of your database - its the easiest way to debug it - if yes you can send it to asg.rd@asg.com or if the size is to big perhaps you can use a cloudspace, send me a mail- I will confirm the download...

[redfoxert]

I'm preparing to export the database. However, our security officer does not like the idea to send a database including all our admin user accounts. Is there a way to quickly remove all the stored user accounts from the database?

[DevOma]

Sure - best would be to backup the database (by MS SQL Management Studio) - restore it in a new database - then execute the following starement on the database

UPDATE ItemProperties SET Data = '' WHERE RolePropertyId = '5B5AA970-E8EA-4162-BECE-6A366E150C92'

Backup it again - all information (Username/Domain/Password) will be invalid - you can connect to that database to verify - you will get a message that credential can't be read and all credentials will have an red exclmation mark...

Thank you for your assistance - hope I can find the issue immediately and publish a new version after that

[DevOma]

Did you send the database or a link to it? Want to solve that problem quickly, but we can't reproduce it - perhaps something from your migrated data causes the problem

[redfoxert]

Not yet, other projects claiming my time unfortunately Will try to arrange for a database without credentials this weekend.

[DevOma]

Let me try to summarize my results :-)

I had really high performance issues even if I only tried to select the count of the Logs-Table - took at the first run about 10 minutes!

Result: about 700.000 log entries

So, delete of "not in the last 30 days" or "not in last 500" takes 10-20 minutes - and I think there are 2 options. You can execute these statements while the application is not running directly from SQL Query - or better DELETE the whole content of the table by "DELETE FROM Logs" OR TRUNCATE LOGS (even faster)

Else you should deactivate the automatic deletion of the logs - this is executed at startup and as I told before, it takes too long.

I found some other index to increase the performance again on startup - from about 20sec to 3sec from my computer.

Hope that helps

[redfoxert]

So ... basically you're saying that there are too many entries in the Logs?

I'll see what I can do to remove all the entries. Do you perhaps have some easy SQL statements for me to purge the Log tables?