So one day, suddenly I find my development environment out of drive space, never good, but its my dev box and not a production system. So that’s the good news.
Production Environment Fix(Contentdb): So I start poking around and find that the content database is bloated to 17 gigs. That’s a problem since I don’t have any real content in the database. I ran this Script and it pointed me to the EventCache table. I changed the logging by going to Central Administration –> Monitoring –> Configure Diagnostic Logging and changed Number of days to store log files to 1. (this may not have any effect, it seems like a setting for file system logging in the hive) In addition to changing the setting you’ll need to go to Central Administration –> Monitoring –> Review job definitions find a job called Change Log, open it and say Run Now.
Development Environment Fix(Contentdb): Break the SharePoint cardinal rule, Truncate the EventCache table a SQL Query and then Shrink the database. Also, change the setting mentioned above about the number of days to store log files.
So why did this happen, I have two theories. The first, I’ve been developing a Custom Timer Service to automate Site Creation, this is causing a lot of transactions to appear in the table. But, I’m also noticing a lot of transactions with regards to _catalogs/users/, I turned MySite stuff on the other day and allocation of MySites appears to cause some real growth problems as well.
Onto the next day, I notice my dev box is out of space again. At first I thought my previous post didn’t fix a thing and I go check the EventCache table again. Well, its only has 4000 rows in it, so that can’t be the problem. I check the db properties and its not bloated.
Some more looking and I find the WSS_Logging Database is 21 gigs, So what the heck, it wasn’t that big the other day….
Production Environment Fix(wss_logging): Go to Central Administration –> Monitoring –> configure usage health data collection and uncheck enable usage data collection. I wasn’t able to figure out which job to run to shrink the database, so I used the truncate script below. [which isn’t supported in SharePoint, funny thing is it returned the following message Command(s) completed successfully. 🙂 ]
Development Environment Fix(wss_logging): To fix this in development run the SQL Script below and make the change to logging mention above and shrink the database.
DECLARE @TableName AS VARCHAR(MAX)
DECLARE table_cursor CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE '%_Partition%'
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQLText AS NVARCHAR(4000)
SET @SQLText = 'TRUNCATE TABLE ' + @TableName
EXEC sp_executeSQL @SQLText
FETCH NEXT FROM table_cursor INTO @TableName
END
CLOSE table_cursor
DEALLOCATE table_cursor