Wednesday, May 11, 2011

SharePoint 2007: Temp DB and Transaction Logs - Excessive Growth

Every night, around 4 AM, the SharePoint temp DB and logs would grow very very large. Size increased by about 60 GB every night, then shrank again. I couldn't figure out why the excessive growth was happening to one of the content datases and the temp database. All research pointed to the search crawler writing to the temp files. These are the steps I took:
  • I disabled all crawls
  • I disabled all usage analysis jobs
  • I disabled any job running at night.
This had no effect. The databases were still growing out of control every night starting at 4 AM even though I had no jobs scheduled at that time. But SOMETHING must still be running...
I isolated the issue to one content database that was also growing every night. There was one site in the database that went through a fairly major cleanup and had deleted 60 GB of content in one day from one folder just over 30 days prior.
I looked in the 2nd level recycle recycle bin and the folder was still there - deleted over 30 days previously. The recycle bin should have deleted it during cleanup days ago. It had actually been trying, but not successful.
SharePoint was running the job to delete items from the 2nd level recycle bin, starting the job at 4 AM, writing to the temp db and the transaction logs, then timing out. So, it would not finish the deletion. The entire process would start again the next night.
I restored the content from the recycle bin to the site. I went into the library and deleted smaller chunks, instead of one folder. Then, I manually deleted 3-4 GB at a time from the 2nd stage recycle bin during off hours, which flushed the items from the database and lessened impact to users. Problem solved.
I also learned that you can set the schedule of the Recycle bin cleanup via stsadm commands:
To set the schedule of a Recycle Bin cleanup for the Web application use the following syntax:

stsadm -o setproperty -pn job-recycle-bin-cleanup -pv "daily at 15:00:00" -url <site URL>

To view the current setting of the job-recycle-bin-cleanup property, use the following syntax:

stsadm -o getproperty -pn job-recycle-bin-cleanup

No comments:

Post a Comment