SharePoint content database’s log file increases its size time by time. It makes me unhappy since it take very large space in hard disk event if you configure in Central Admin to reduce for logging.
Microsoft provided information and guideline for maintaining SharePoint database. It describes and provides examples of the database maintenance tasks that recommend when using SharePoint Products and Technologies. Its really useful for administrator to setup schedule tasks to reduce size of SharePoint log file database.
For SharePoint 2007, download at http://technet.microsoft.com/en-us/library/cc262731(office.12).aspx
For SharePoint 2010, download at http://www.microsoft.com/download/en/details.aspx?id=24282
In this post, I would introduce how to manually reduce SharePoint log database by command line and user interface.
1. By SQL 2008 Management Studio UI.
– Open Microsoft SQL 2008 Server Management Studio. Expand Databases.Right click on your database, in my case it’s the WSS_Content database, and click Properties to verify for database files location.
– Check size of your log file
– Back to your database, Right click on your database, in my case it’s the WSS_Content database, go to Properties, Options, and change Recovery model to Simple
– Back to your database, Right Click it, select Tasks, Shrink, Files then select File type as Log, Then select shrink action as you want. Click OK to start Shrink.
– Back to your database, Right click on your database, go to Properties, Options, and change Recovery model to Full
2. By Query Command Line
Open New Query from SQL 2008 Management Studio, Run following command lines:
USE YourDatabase; GO -- set recovery model to FULL ALTER DATABASE YourDatabase SET RECOVERY SIMPLE; GO -- shrink the database file to 1 MB DBCC SHRINKFILE(YourDatabase_log,1); GO -- set back the database recovery model to FULL ALTER DATABASE YourDatabase SET RECOVERY FULL; GO
Syntax for SHRINKFILE method
DBCC SHRINKFILE
(
{ 'file_name' | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
file_name | file_id specifies the file name or ID.
EMPTYFILE Migrates all data from the specified file to other files in the same filegroup. NOTE: Using the EMPTYFILE option is not supported for SharePoint Products and Technologies database files.
target_size is the target size for the file in megabytes, expressed as an integer.
NOTRUNCATE compacts the data in data files by moving allocated pages from the end of a file to unallocated pages in the front of the file.
TRUNCATEONLY releases all free space at the end of the file to the operating system but does not perform any page movement inside the file.
Note: Using the TRUNCATEONLY option is not supported for SharePoint Products and Technologies content databases.
For more information, see DBCC SHRINKFILE (Transact-SQL) (http://go.microsoft.com/fwlink/?LinkId=110853&clcid=0x409).
Hoang Nhut Nguyen 