The SharePoint site collection size more than 50 GB is not opening

Issue:

I got a call from a user that they are not able to go to their team sites. I tried to open the site and its thinking, thinking and thinking for about 20 mins and then IE throws error page not found.

Solution:

I tried another Site collection and it opens well. So my SharePoint environment is fine except this team site.

I gone to central admin and check the quota template and find it has about 49 GB data on that site collection.

We have used one DB per Site Collection for Document management.

I have a SQL DB backed up of the troubled SC and I did restore it to Dev. try to open it and it opened straight away without any trouble.

From this I find it’s not a problem with SharePoint it’s something related to DB size.

I check logs and event viewer and couldn’t find anything.

I gone to the timer services status under Operations and here you go I find Database statistics services hang on initialized states on 45% from last 2 nights for one server out of our four server farm.

Google it and find this article:
http://blogs.technet.com/b/patrick_heyde/archive/2010/05/27/advanced-maintenance-for-sharepoint-databases-defrag-update-index.aspx
I read about the smarter way section and Patrick specify about 50 GB limit and it strikes that it conflicts with our back up schedule which makes this time services on hang on state.
I gone to the timer services definition and stop the services for the Web app having this SC and done the following steps.
1. Stop Windows SharePoint Services Timer on Windows Services
2. Go to ‘C:\Documents and Settings\All Users\Application Data\Microsoft\SharePoint\Config’ on front-end server
3. Clear xml files from the above folder.
4. Open cache.ini file and restart the number into ‘1’
5. Start Windows SharePoint Services Timer on Windows Services

The above steps clear SharePoint Config cache and allow the timer jobs to run properly. The Database statistics timer job is aborted.
Now I turn on the Database statistics timer job then change the timing for this time service from daily to weekly and from 6 am to 4 pm other than the backup scheduled.

Here are the stsadm cmd to change the timings.

To set the schedule for when database statistics are collected for the Web application http://test, use the following syntax:

stsadm -o setproperty -pn job-database-statistics -pv “daily at 15:00:00” -url http://test

To view the current setting of the job-database-statistics property, use the following syntax:

stsadm -o getproperty -pn job-database-statistics -url http://test

Now the site opens straight away.