logo

The ConnectWise Control forum has moved to ConnectWise University! This forum has been locked and is in read-only mode. Click here for instructions on how to access the new forum.

Welcome Guest! You can not login or register.

Notification

Icon
Error
MS SQL database maintenance and random server lockups - cleanup sessionconnection and sessionconnectionevent tables
Options
Go to last post Go to first unread
Previous Topic Next Topic
JoshK  
#1 Posted : Wednesday, April 4, 2018 1:22:29 AM(UTC)
JoshK


Rank: Newbie

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

Joined: 12/14/2013(UTC)
Posts: 7
Location: USA

Was thanked: 1 time(s) in 1 post(s)
We have been experiencing random loss of access to our ScreenConnect server that requires a restart. The restart takes an hour before Screenconnect is usable again. We think we need to clean up our database, but it is running MS SQL.

We use MS SQL database so that we can email automated reports to clients showing which techs connected to machines. We've been using Screenconnect for a few years this way and used SQL Lite for a few years before that, but performance was too slow with SQLLite with 4000 PC's.

Recently, our Screenconnect system has been hanging up randomly. We click on a machine in the list, but it would give an error. Restarting the server would resolve it, but it took about 30 minutes to load the database before it was usable again. I suspect that heavy Disk I/O is causing the service to randomly lose access to the database and it never tries to reconnect.

Our SQL database is about 3 gigs. Does anyone know if you can use the built in database maintenance to clear out old information from a MS SQL database? I read conflicting reports on the forums about whether this would do anything to MS SQL.

If that is not possible, does anyone have an MS SQL query that will help to clear out old information from the database? We don't need anything older than 45 days.

I have queries below that I think would clear out old information from the sessionconnectionevent and sessionevent tables. I don't have a date field to trigger off of for the sessionconnection table. That table has 1.8 million rows. sessionconnectionevent has 2.4 million rows. I think Screenconnect is adding entries back into the database upon startup if I only delete from 2 tables.


Sanity check the number of rows to delete vs total number of rows in table
SELECT * FROM [ScreenConnect2].[dbo].[SessionConnectionEvent]
WHERE Time < DATEADD(day,-45,GETDATE())

Delete those old records
DELETE FROM dbo.SessionConnectionEvent WHERE Time < DATEADD(day,-45,GETDATE())

Sanity check the number of rows to delete vs total number of rows in table
SELECT * FROM [ScreenConnect2].[dbo].[SessionEvent]
WHERE Time < DATEADD(day,-45,GETDATE()) AND EventType <> 21

Delete those old records, but leave eventtype 21 so old sessions don't reappear.
DELETE FROM dbo.SessionEvent WHERE Time < DATEADD(day,-45,GETDATE()) AND EventType <> 21


When I tried clearing some of these out, the server took forever to startup. It looked like it was scanning the sessionconnection table and then reinserting the entries into the sessionconnectionevent table. Disk I/O performance was pegged at 60 MB/sec reading from the database and writing to tempdb.

I saw SQL like the info below going crazy on the system. It looked like it was cycling through unique sessions ID's and inserting info into the sessionConnectionEvent table for each of them. It sat there for 2 hours and was unusable.

I ended up restoring the database from backup to get it going again.

I'd like to find a way to clear out old information from the database. If there is not a way to do that, is there a way to keep the config and existing machines, but start over on the other tables?

Thanks,
Josh

INSERT INTO SessionConnectionEvent
(SessionID, ConnectionID, EventID, EventType, EventAttributes, Time, Data)
SELECT SessionID, ConnectionID, newid(), @A, @B, @C, ''''
FROM SessionConnection
WHERE NOT EXISTS
(
SELECT *
FROM SessionConnectionEvent
WHERE
SessionID = SessionConnection.SessionID AND
ConnectionID = SessionConnection.ConnectionID AND
EventType = @A AND
(EventAttributes & @D) = 0
)',N'@A int,@B int,@C datetime2(7),@D int',@A=11,@B=0,@C='2018-04-03 17:45:36.7902981',@D=1
Back to top

Back to top  
JoshK  
#2 Posted : Saturday, April 7, 2018 3:24:52 PM(UTC)
JoshK


Rank: Newbie

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

Joined: 12/14/2013(UTC)
Posts: 7
Location: USA

Was thanked: 1 time(s) in 1 post(s)
Well, I tried enabling the built in database maintenance to see if it would clean up the Microsoft SQL database tables and it did not have any effect. I have 2.5 million rows in SessionConnectionEvent table and 1.8 million rows in SessionConnection table.

Is there a way to get a copy of the SQL queries used for normal database maintenance so I could run them in a MS SQL Query?

Is there a way to see what SQL query is created when I create a maintenance plan action item?

I was trying to delete anything older than 60 days. I could then do this on a regular basis to keep it cleaned up.

Thanks,
josh
Back to top
Scott  
#3 Posted : Monday, April 16, 2018 1:06:16 PM(UTC)
Scott


Rank: Administration

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

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

Thanks: 3 times
Was thanked: 351 time(s) in 303 post(s)
We pass parameters into the following template to delete Ended sessions:
Code:

DELETE FROM
	Session
		LEFT OUTER JOIN SessionEvent se ON Session.SessionID = se.SessionID AND se.EventType = @A
		LEFT OUTER JOIN SessionConnectionEvent sce ON Session.SessionID = sce.SessionID AND sce.EventType = @A
	WHERE
		SessionType = @B
		AND (se.Time < @C OR sce.Time < @C)


Where @A is SessionEventType.EndedSession, @B is the type of Session (Support, Meet, Access) and @C is the daysAgo.

To delete SessionConnections we use the following:

Code:

DELETE FROM
		SessionConnection
		INNER JOIN Session ON SessionConnection.SessionID = Session.SessionID
		LEFT OUTER JOIN SessionConnectionEvent ce ON SessionConnection.SessionID = ce.SessionID AND SessionConnection.ConnectionID = ce.ConnectionID AND ce.EventType = @A
		LEFT OUTER JOIN SessionConnectionEvent de ON SessionConnection.SessionID = de.SessionID AND SessionConnection.ConnectionID = de.ConnectionID AND de.EventType = @B
		LEFT OUTER JOIN SessionConnectionEvent ee ON SessionConnection.SessionID = ee.SessionID AND SessionConnection.ConnectionID = ee.ConnectionID AND ee.EventType = @C
	WHERE
		SessionType = @D
		AND (ce.ConnectionID IS NULL OR de.Time < @E)
		AND ee.ConnectionID IS NULL
		AND ProcessType IN ({this.connection.GetInClauseInnerSql(connectionTypesToDelete)})"


WHERE @A is SessionEventType.Connected, @B is SessionEventType.Disconnected, @C is SessionEventType.EndedSession, @D is the type of Session (Support, Meet, Access), and @E is the daysAgo value. Also the GetInClauseInnerSql method returns the ProcessType which is either Guest or Host.

To delete Session Events (Activity), we use:
Code:

DELETE FROM
	{tableName}
		INNER JOIN Session ON {tableName}.SessionID = Session.SessionID
	WHERE
		SessionType = @A
		AND Time < @B
		AND EventType IN ({this.connection.GetInClauseInnerSql(eventTypesToDelete)})"


Where tableName is either SessionEvent or SessionConnectionEvent, @A is the SessionType (Support, Meet, Access), @B is the daysAgo, and EventType is derived from the following enum:

Code:

public enum SessionEventType
	{
		[EnumMember(Value = "n")]
		None = 0,

		[EnumMember(Value = "c")]
		Connected = 10,
		[EnumMember(Value = "d")]
		Disconnected = 11,

		[EnumMember(Value = "cs")]
		CreatedSession = 20,
		[EnumMember(Value = "e")]
		EndedSession = 21,

		[EnumMember(Value = "ij")]
		InitiatedJoin = 30,
		[EnumMember(Value = "in")]
		InvitedGuest = 31,
		[EnumMember(Value = "an")]
		AddedNote = 32,

		[EnumMember(Value = "qr")]
		QueuedReinstall = 40,
		[EnumMember(Value = "qu")]
		QueuedUninstall = 41,
		[EnumMember(Value = "qi")]
		QueuedInvalidateLicense = 42,
		[EnumMember(Value = "qw")]
		QueuedWake = 43,
		[EnumMember(Value = "qc")]
		QueuedCommand = 44,
		[EnumMember(Value = "qm")]
		QueuedMessage = 45,
		[EnumMember(Value = "qg")]
		QueuedGuestInfoUpdate = 46,
		[EnumMember(Value = "qt")]
		QueuedTool = 47,
		[EnumMember(Value = "qet")]
		QueuedElevatedTool = 101,
		[EnumMember(Value = "qd")]
		QueuedForceDisconnect = 48,
		[EnumMember(Value = "qa")]
		QueuedInstallAccess = 49,
		[EnumMember(Value = "que")]
		QueuedUninstallAndEnd = 100,

		[EnumMember(Value = "pr")]
		[Obsolete("Use QueuedReinstall and NeedsProcessing attribute")]
		ProcessedReinstall = 50,
		[EnumMember(Value = "pu")]
		[Obsolete("Use QueuedUninstall and NeedsProcessing attribute")]
		ProcessedUninstall = 51,
		[EnumMember(Value = "pi")]
		[Obsolete("Use QueuedInvalidateLicense and NeedsProcessing attribute")]
		ProcessedInvalidateLicense = 52,
		[EnumMember(Value = "pw")]
		[Obsolete("Use QueuedWake and NeedsProcessing attribute")]
		ProcessedWake = 53,
		[EnumMember(Value = "pc")]
		[Obsolete("Use QueuedCommand and NeedsProcessing attribute")]
		ProcessedCommand = 54,
		[EnumMember(Value = "pm")]
		[Obsolete("Use QueuedMessage and NeedsProcessing attribute")]
		ProcessedMessage = 55,
		[EnumMember(Value = "pg")]
		[Obsolete("Use QueuedGuestInfoUpdate and NeedsProcessing attribute")]
		ProcessedGuestInfoUpdate = 56,
		[EnumMember(Value = "pt")]
		[Obsolete("Use QueuedTool and NeedsProcessing attribute")]
		ProcessedTool = 57,
		[EnumMember(Value = "pd")]
		[Obsolete("Use QueuedForceDisconnect and NeedsProcessing attribute")]
		ProcessedForceDisconnect = 58,

		[EnumMember(Value = "mn")]
		ModifiedName = 60,
		[EnumMember(Value = "mp")]
		ModifiedIsPublic = 61,
		[EnumMember(Value = "mc")]
		ModifiedCode = 62,
		[EnumMember(Value = "mh")]
		ModifiedHost = 63,
		[EnumMember(Value = "mu")]
		ModifiedCustomProperty = 64,

		[EnumMember(Value = "rc")]
		RanCommand = 70,
		[EnumMember(Value = "sm")]
		SentMessage = 71,

		[EnumMember(Value = "sp")]
		SentPrintJob = 80,
		[EnumMember(Value = "rp")]
		ReceivedPrintJob = 81,
		[EnumMember(Value = "ct")]
		CopiedText = 82,
		[EnumMember(Value = "cf")]
		CopiedFiles = 83,
		[EnumMember(Value = "df")]
		DraggedFiles = 84,
		[EnumMember(Value = "rf")]
		RanFiles = 85,
		[EnumMember(Value = "tf")]
		SentFiles = 86,
}
ScreenConnect Team
Back to top
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.

Powered by YAF.NET | YAF.NET © 2003-2020, Yet Another Forum.NET