SharePoint 2007: Shrink a database

Question

How do I shrink my sharepoint databases? The log file has grown to be over 8 GB, though the actual data file is less than one.

Answer 1

I suspect you mean you want to shrink the log file, not the database itself (there's no point in shrinking that unless you have deleted tons of content).

In short, you want to run the following commands in SQL Manager or SQL Server Management Studio or the osql or sqlcmd tools (as appropriate to whichever version of the database you are running), as described here:

USE WSS_Content
GO
DBCC SHRINKFILE(WSS_Content_log, 2)
BACKUP LOG WSS_Content WITH TRUNCATE_ONLY
DBCC SHRINKFILE(WSS_Content_log, 2)
GO

This page has a bunch more tips on preventing the log file from growing so big. The easiest one is to set the database recovery model to "Simple".

Answer 2

Your log file is most likely growing to such a large size because the Database has it's recovery mode set to Full Recovery and you have not scheduled for the log to be backed up. In SQL when in Full Recovery the log file will grow forever until it has been backed up then the backed up space can be reused by SQL.

Switching to simple recovery mode will not require you to back up the log file but the drawback is if you need to restore he db you will only be able to do so from the moment you ran the db backup.

While if you keep it in Full Recovery mode an properly back up the log files (say every hour) and back up the db once or twice a day you will be able to restore the db to a specific point in time. ie if the error occurred at 3:00pm you could restore to 2:59pm.