15 million records in sgnotify eventdetails

If you are having a problem using Vault, post a message here.

Moderator: SourceGear

Post Reply
peter.zalevski
Posts: 10
Joined: Wed Mar 17, 2010 6:44 pm

15 million records in sgnotify eventdetails

Post by peter.zalevski » Sun Jan 31, 2016 6:58 pm

Hi
I just completed our upgrade from vault version 6 to 9 and all went well.
I was reviewing table sizes and noticed that sgnotify.eventdetails has 15 million rows.
We have only 3 users with notifications enabled so I was just wondering if table size is normal.
Thanks
Peter

jclausius
Posts: 3624
Joined: Tue Dec 16, 2003 1:17 pm
Location: SourceGear
Contact:

Re: 15 million records in sgnotify eventdetails

Post by jclausius » Mon Feb 01, 2016 8:38 am

Hi Peter,

That is a lot of data. First off, let's check the cause of the build-up of rows.

Does your environment include application or email notifications? Can you check the Admin web page's Vault Plugin page on a browser from your Vault Server? There will be a handful of notifications. If you are using email or application notifications, please check the URLs for the 'VaultNotifyService.VaultNotifyService' plugins from this page. There is a little 'magnifying glass' icon by each plugin service. If you click that, it should give you a Service Description page. Note, if you do not use any notifications, you can disable all VaultNotifyService plugins.

My guess is there is a URL in those plugins which is causing the build-up of rows.


In regards to the rows themselves, if we do not need to preserve any notifications, there are some queries I can provide that will remove the rows from the database. After we resolve the plugins, please post back, and I'll follow up with some SQL queries we can use to trim back the sgnotify database.
Jeff Clausius
SourceGear

peter.zalevski
Posts: 10
Joined: Wed Mar 17, 2010 6:44 pm

Re: 15 million records in sgnotify eventdetails

Post by peter.zalevski » Mon Feb 01, 2016 4:57 pm

Hi Jeff
The active plugins are
VaultIndexService
VaultNotifyService
VaultShadowFolderService

The URL for VaultNotifyService is http://127.0.0.1/VaultNotifyService/Vau ... rvice.asmx
We have two inactive plugins for VaultNotifyService as well

We were just using a small number of email notifications. i dont believe any of our users have enabled application notifications.
Thanks
Peter

jclausius
Posts: 3624
Joined: Tue Dec 16, 2003 1:17 pm
Location: SourceGear
Contact:

Re: 15 million records in sgnotify eventdetails

Post by jclausius » Mon Feb 01, 2016 5:08 pm

Does clicking the magnifying glass test link correctly bring up the service description page? It is critical that part is working correctly.


In regards to the data itself, if you do not mind losing whatever notifications have bunched up, you can remove them from the database with a couple of SQL commands:

a) To truncate the table, run this query:

TRUNCATE TABLE sgnotify.dbo.eventdetails
GO

b) You may want to release extra space in the database afterword by shrinking the database files.

c) Afterwards, try restarting the Vault Notification App Pool within IIS, and check to see if the notification events have been cleared out: SELECT COUNT(*) FROM sgnotify.dbo.events;

The idea is to get that count to 0.
Jeff Clausius
SourceGear

peter.zalevski
Posts: 10
Joined: Wed Mar 17, 2010 6:44 pm

Re: 15 million records in sgnotify eventdetails

Post by peter.zalevski » Mon Feb 01, 2016 5:14 pm

Correct I see the service description.

I will go ahead and clean up the table. Is there is any concerns with deleting the inactive/duplicated plugins?
They seem to have come about due to our various upgrades over the years, when we have moved the vault server to a new instance/new server names etc.

jclausius
Posts: 3624
Joined: Tue Dec 16, 2003 1:17 pm
Location: SourceGear
Contact:

Re: 15 million records in sgnotify eventdetails

Post by jclausius » Tue Feb 02, 2016 8:28 am

No. The 'cleaner' the plugins page looks the better as that will lead to less confusion down the road.
Jeff Clausius
SourceGear

Post Reply