Shrinking SQL 2008 Databases for SharePoint

So I have to confess I take no credit for this post and there are plenty of blogs out there that explain how to shrink SQL DB’s however searching for Shrinking SQL databases for SharePoint shows little matches so I thought I’d make easier for SharePoint folk to be able to find this quick an easy reference as normally you’d be looking for this solution when you’re environments fell over and need a quick fix.

Before proceeding I just want to warn you that this should not be carried out on production environments without consulting your SQL DBA team. This blog entry for those who are in trouble and need a quick fix for development environments where backups and DR are not necessarily crucial so transaction logs do not need to be considered to restore to a point in time. It’s also worth mentioning that any SQL maintenance that you carry out should be done so in accordance with the guidelines outlined in the KB link http://support.microsoft.com/kb/841057/en-us.

So to the command. From SQL management studio run the command as shown below changing the database name to the one you need to shrink, in the example below as you can see mine is DEV_ConfigDB.

USE DEV_ConfigDB

GO

ALTER DATABASE DEV_ConfigDB SET RECOVERY SIMPLE

DBCC SHRINKFILE(DEV_ConfigDB_log, 1)

You should now find your log file has shrunk as well as the size of the actual database.

Finally its also worth mentioning that SQL Database Maintenance for SharePoint 2010 whitepaper. This is an in-depth paper written / contributed by members of the SharePoint product team that should satisfy a SQL DBA’s questions on how to run maintenance plans for SharePoint SQL databases.

I hope this is of some use to you.

Will SharePoint work for us?

Of late I’ve been out and about visiting new clients who are either looking at SharePoint for the business or have installed SharePoint and don’t know what to do next. I recall a well known saying in the SharePoint community that was once said to me:

“The answers SharePoint, what's the question?”

This is sums up a lot of my engagement’s I’ve been involved in recently and there is a valid reason for it in as much that SharePoint is part of a lot of clients Microsoft Enterprise Agreement (an EA) so its been paid for so the view is lets use it. This is a like walking into a forecourt full of cars with a blindfold on and randomly pointing at a car that you want to buy only to realise its only got 2 seats when you needed 4 or its a manual gearbox and you can only drive automatics. I think you kind of get the picture of what I’m trying to say here is that if you go in blind you can’t expect to get the results you were looking for.

This is where the problem comes in because SharePoint as a product is a complex platform and understandably clients who have had little exposure to to date struggle to understand how to use it effectively and how to sell it.

SharePoint isn't another Exchange deployment where once its configured it just sends mail (and very well it does too), not to devalue the importance to Exchange implementations but if you’re implementing Exchange you’re requirement is pretty clear right? Email? That's not to say SharePoint doesn't need the same support from IT but its involvement from the business which will make it a success.

So with little knowledge of SharePoint's capabilities its wrongly being pitched as a solution when no evaluation has been carried out on the product to see if it meets specific requirements. SharePoint unfortunately comes in for some unnecessary criticism because its only after SharePoint has been decided as the solution they find out it only meet 60 – 80% of the requirements and at this point time, money and resource has been invested meaning the client either begrudgingly carries on with SharePoint having to request additional budget or bail out.

I also see that SharePoint is still being pitched as a point solution e.g. its an intranet and that's all and not a strategic platform with direction, which brings me onto my next point.

My advice at this stage is you need a strategy, a plan, a vision of where you are going and think bigger picture. Thinking short term will more than likely end in failure as you make the wrong choices it becomes harder and harder to unpick.

So some takeaways from reading this and advice I can give is to not expect internal staff to all of sudden become SharePoint experts, it takes time. In the short-term if things need to be done quickly look at external consultancy options or recruit experienced proven SharePoint people (this takes time I know!). Longer term look at internal staff strengths and identify who’s good at such things as development, leading, administration (especially those keen to learn new things), analysis, project management and then align these skills to how they fit with the roles that are needed to maintain and grow a successful SharePoint implementation.

So the key is what's the strategy? Then once there is a strategy defined evaluate the products capabilities and review where it meets the requirements and where it doesn't. If it doesn't do something well in one area look at the strategy of the business and review other areas and see if it has strengths there and look at its overall performance and make an overall decision.

To conclude with those I have engaged with over the last few weeks / months / years I am in no way criticising their approach, its tough to get it right and that's why investing in planning upfront is the best place to start to avoid wasted money, time and effort whilst expectation has not been met.

As always I welcome your views…

User Profile Information not correct in Site Collection My Settings (userdisp.aspx page)

I came across a strange one the other day. A client called me to say when a user adds an alert it shows an email address that the user had a while ago and does not reflect an update in Exchange / AD. So the easy target was the user profile service however it proved not to be this (for once!).

So doing some digging I found some really strange behaviour. I tested the user in questions alerts and sure enough it was showing the old email address even though all the information for that user was definitely correct.

After further testing I experienced different profile information in different site collections (some correct and up to date and the others not).

So to picture the page I am referring to, here’s a screenshot below (_layouts/userdisp.aspx)

image

This is the page you would normally see in WSS or SharePoint Foundation and you can still access this page from SharePoint Standard or Enterprise selecting My Settings from the drop down.

image

Back to the issue.in hand so noticing different behaviour between site collections, I came across a Microsoft KB article KB2012619 which suggested that if a user is only a reader of the site collection they wont be able to update the information. I thought this was strange however I tested this and strangely it worked. Long story short I then tested again and it didn't so results were mixed. I then discovered the actual timer job which updates each site collection with the user profile information from the service application which is called ‘User Profile Service Application - User Profile to SharePoint Full Synchronization’:

image

This Timer Job was running fine however the specific site collections were not updating.

From articles relating to MOSS 2007 there was suggestions that if the database was not correctly attached to the farm the SSP would report issues. As the farm I was working on was upgraded I had suspicions that maybe not all was well with content database so decided to create a new content database and migrate all the site collections into this database using PowerShell to do so (See http://technet.microsoft.com/en-us/library/cc825328.aspx#Section3).

After moving the site collections to the new content database then re-running the ‘User Profile Service Application - User Profile to SharePoint Full Synchronization’ Timer Job this successfully updated all site collections with the correct information. I tested this further by adding additional profile information in AD then running a full profile synch and finally the Timer Job to update the SharePoint Sites and the new information successfully propagated to each site collection.

So to conclude the user who’s alert page was showing an old email address was now corrected so his was able to receive alerts.