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.
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.