logo
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
digdug  
#1 Posted : Thursday, March 16, 2017 2:15:17 PM(UTC)
digdug


Rank: Newbie

Joined: 3/9/2015(UTC)
Posts: 15
Chile
Location: Santiago

Good Day,

We have a premise installation that has been running for several years, but recently we effectively blocked ourselves out of the application by retaining 'too much' history. Since we cannot get the services to start we need to do some maintence to clean the database from an SQL client. We prefer not to completely wipe the sessions table and would like to eliminate history by date the way the maintenance process does..

Does anyone out there have SQL that can 'correctly' perform maintenance on the sessions table ?

Contacted support and was told to run maintenance, but without access to the interface I need another way to do this.

(Given that this is probably a self induced failure any and all help would be appreciated !)

Dave
digdug  
#2 Posted : Thursday, March 16, 2017 2:41:42 PM(UTC)
digdug


Rank: Newbie

Joined: 3/9/2015(UTC)
Posts: 15
Chile
Location: Santiago

Forgot to mention an important detail.. We are running Version 5.6.10957.6018 until we update our integration for 6.x
Ben B  
#3 Posted : Thursday, March 16, 2017 3:06:48 PM(UTC)
Ben B


Rank: Administration

Medals: Level 2: Lent a Helping Hand! 10 Thanks!

Joined: 10/2/2015(UTC)
Posts: 307

Thanks: 1 times
Was thanked: 63 time(s) in 58 post(s)
Originally Posted by: digdug Go to Quoted Post
Forgot to mention an important detail.. We are running Version 5.6.10957.6018 until we update our integration for 6.x


How large is your database?

Would you mind providing some additional information about your implementation, including:

1) Server OS and hardware specs
2) If the Session Manager/Relay/Web services fail to start, does the server's event viewer contain any error messages related to the failure?
3) Are you using the default SQLite database (i.e., have you migrated to another database engine)?

Thanks
ScreenConnect Team
digdug  
#4 Posted : Friday, April 28, 2017 7:20:15 PM(UTC)
digdug


Rank: Newbie

Joined: 3/9/2015(UTC)
Posts: 15
Chile
Location: Santiago

Hi Ben,

Sorry I did not answer sooner. I have been tied up implementing some new clusters and this dropped off my radar for a while.

The main reason I was asking for SQL has to do with the fact that we use SQL server rather than SQLLite. What we ran into is essentially that some of the UI quieries appear to scan the full tables so having allowed them to grow created some timeouts and response issues. At one point the users complained that the systems seemed frozen so I restarted the services, and at that point they took a very long time to start.

For now I worked around the problem by doing some manual cleanup and regenerating indexes/stats. My concern is that since we disabled the cleanup process because the company wanted to keep 'all' history, we will see this problem again in the near future. I spoke with support at one point, but since we are not using SQLLite they could not help us directly. They did suggest that a 'newer' version could help, but we use a rather complex integration we built so we upgrade 'slowly' rather than frequently.

In any event in reply to your questions:
1 - Server is a VM running under Hyper-V. It is configured with Windows 2012R2, 6 Gigabytes memory, and runs screenconnect and SQL Server Express to host the ScreenConnect Database.
2 - No. In fact they did start, they just take a very long time to start responding. Prior to optimizing the DB it took over 40 minutes.
3 - We use SQL Server Express 2012 for the database.

More recently I activated the cleanup routines specifying they retain 90 days, but there are still very old records present so I am not certain he process is working.
Scott  
#5 Posted : Tuesday, May 2, 2017 11:36:30 AM(UTC)
Scott


Rank: Administration

Medals: Level 4: Wise Old Owl! Received 100 Thanks!

Joined: 3/28/2014(UTC)
Posts: 2,533
United States

Thanks: 3 times
Was thanked: 309 time(s) in 266 post(s)
It's difficult to provide the queries used to delete old/unnecessary information without knowing what information (if any) you want to preserve. If you could take a look at the Database tab on the Administration page and perhaps provide a sample plan of what you want to delete (IE Access Session Activity for > 30 days), it would be easier to provide queries that could help.

And to speak to what the Support team was talking about that a newer version would help, in version 6.1 we changed the server process to run as a 64 bit process which increases the amount of available RAM significantly. This has drastically reduced the database-loading related issues we were seeing, especially during startup. I cannot say definitively whether this change would also improve SQLServer integrated installations, however.
ScreenConnect Team
digdug  
#6 Posted : Tuesday, May 2, 2017 2:46:03 PM(UTC)
digdug


Rank: Newbie

Joined: 3/9/2015(UTC)
Posts: 15
Chile
Location: Santiago

Basically we are looking to delete everything in the database related to sessions and Activity that exceeds 90 days of age. What I had done originally was configure the Database cleanup so that all of the first six categories removed data after 90 days. I left the last two relating to captures disabled because we archive capture recordings externally through a separate process.

Since the cleanup process did not seem to work against SQL server, I was looking for SQL statements that could perform the same task.

Runar  
#7 Posted : Thursday, January 11, 2018 7:34:55 AM(UTC)
Runar


Rank: Newbie

Joined: 9/7/2016(UTC)
Posts: 16
Norway

Thanks: 1 times
Has anyone successfully used the database maintenance when using an external database (MSSQL)? We're looking at doing the same as digdug where we want to delete all activity that's older than x days.
shawnkhall  
#8 Posted : Friday, January 12, 2018 4:04:20 AM(UTC)
shawnkhall


Rank: Advanced Member

Medals: Level 1: Random Act of Kindness! Received One Thanks!

Joined: 2/6/2014(UTC)
Posts: 206
Man
United States

Thanks: 5 times
Was thanked: 12 time(s) in 10 post(s)
Hi, @Runar,

I'm using a custom extension to pipe all events and devices into a MySQL database for safekeeping and have changed the 'maintenance' behavior to purge the local/sqlite database at a much shorter frequency (keeping notes and chat info). This way I have the data long-term (in MySQL which is extremely easy to manage), but the SC install is very responsive because it only needs to keep data for a short period.
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.