Shared Service Provider Database is excessively large

I
ran across this not to long ago. I found that there is a log table in that
database the records all changes to the UserPorfiles and keeps that history for
5 days. That is configurable through STSADM. Anyway once I truncated
that table and ran the shrink DB, it cleared up about 20GB. So take a
look at the Database and look for a table called UserProfileEventLog and see
how big it is. You can also right click on the DB and go to reports and
run the Disk Usage by top tables report. This will show you which table
are taking up all the space.



if the UserProfileEventLog Table is really big then you
can just truncate that table to decrease the size. They are just log
entries. You can also configure how many days of info that this save with
STSADM -o profilechangelog. Like I said before it defaults to 5
days. You might change that to 1 or 2.


UPDATE*********************************

Another client had a different table that was excessively large. The table in question this time was the ASPStateTempSessions table. According to some of the information I found this is also caused be the table not being truncated properly. This info did point to Project Server 2007 but seemed to apply to SharePoint as well since that's basically what Project Server 2007 is.
it http://support.microsoft.com/kb/317604


After installation of Office Project Server 2007, you need to remove expired session state data periodically from your Shared Services Provider database. This data is located in the ASPStateTempSessions table. This table needs to be truncated to ensure that expired sessions are deleted from the table. If this is not done, over time the accumulation of data in the database will affect server performance.
To remove expired data from the ASPStateTempSessions table:
Run the SQL Server Agent service to monitor the database.
Periodically truncate your ASPStateTempSessions table to remove expired session state data. You can run the following SQL script to delete data from the table:

use
truncate table ASPStateTempSessions

http://technet.microsoft.com/en-us/library/cc197479(TechNet.10).aspx