Share via

SQL tables which contains data which is not so useful to keep for a long time in a SharePoint Farm

  • Article

In my experience I have seen tables which are part of the content db's and also SSP db's which are actually not useful.  But these tables grow really fast and fill the hard disk space.  In this post, I would be talking about four of those tables; two from the content db and two from the SSP database and also the supported mechanism to clear those.

Tables from the content db

1.  EventCache

2.  AuditData

EventCache table contains all the changes that happens in the particular web application.  This is used by different features; alerts, search, content deployment etc.  Alert actually triggers the email based on the change which gets added to this table.  There is a setting related to Change Log in the Central admin-->Application Management-->Web application General Settings (https://CentralAdminSite/_admin/vsgeneralsettings.aspx).  The default value is 15 days and you can set to Never as well.  It is good to have the default value and the data would get cleared automatically.

AuditData table contains all the auditing related information once it is enabled in the web application.  There is no UI to control the data as such and if you enable it across all the objects in a web application, the table will become huge in a day or two depending on the number of users accessing the site.  I have seen situations where the table grows to 17 Gigs and this creates major performance impact.  At the same time, lot of companies need to keep the data for some kind of compliance purposes.  The best way is to move the data from the table to another SQL Database and then delete the entries in the table using the STSADM -o trimauditlog command.  This command is available only after installing the Infrastructure updates.  The other supported way to play around this table is thorugh Object Model.  Check this blog for more info.

Tables in the SSP Database

1.  UserProfileEventLog

This is similar to the EventCache table of a content db.  This table contains all the changes that happens to the user profile store.  If you add a user profile, this will add this entry and if you delete a profile a new row is added.  Imagine if you delete 40K users from the profile store and reimport in a troubleshooting scenario. The table will contain 80K rows and can impact your performance and also eats up the disk space where the MDF and LDF is stored.  I have seen many customers running into this scenario.  There is an STSADM  comand (ProfileChangeLog) to confiugre the number of days for which the data to be present in the table.  But if the table already contains huge data, then there may be issues when the timer job runs to clear this table.  You may need to install this hotfix which actually solves the problem if the size of the table is huge.  The timer job only runs at 10:00 PM and there is no way to control this.

2.  AspStateTempSessions

This table is used to store the Asp.Net session state.  InfoPath Forms Services, Usage analysis, Project server, workflow etc uses this table. In addition, it’s possible that custom developed content/pages are created that rely on ASP.Net Session State. By default, there should be a SQL job created namely - <SSPdatabaseName>_Job_DeleteExpiredSessions which will run event one minute to clear the expired sessions from the table. But I have seen customers where this job doesn't exist at all and the table grows really big. Also the SQL Agent service should be running to trigger this job.