SharePoint Experts, Information Architects, Expert Witness

SICG provides a broad array of business and technology consulting from architecture to design to deployment of global systems with a focus on surfacing data in the enterprise. We focus on the "How", not just the possible. Contact me direct: or call 704-873-8846 x704.

Search This Blog

Tuesday, August 7, 2012

SharePoint and SQL Server Databases and Log Sizes

Seems SharePoint 2010 (and 2013) seem to over allocate database space and/or logs...

1) Sooner or later you will come across a message in the Central Administration site that will tell you one or more databases have a lot of unused space. Sometimes there is a 'Repair Automatically' button enabled, sometimes not.

2) You begin to notice that the some of the database log files are something like 9 times the size of the database.

Both of these issues can be the same or different problems - in either case, you need to shrink them otherwise performance suffers.

The quick and dirty way to shrink a database is to use the DBCC ShrinkDatabase or DBCC ShrinkFile methods (via a query window, DBCC ShrinkDatabase(<dbname>)).

For the Microsoft outline, see here:

One of the reasons for the size can be either that the database is in Debug mode (thus logging everything) - seemingly the default setting for many and another is that the database log file itself is over allocated (another SharePoint default setting). Using SQL Management Studio, expand the databases, right click on the database name in question and select Properties.

One of the common issues is that the SQL Recovery Model is set to Full - this means full backup recovery. When in this mode, you cannot change the File Sizes - this has to be changed to Simple, the file shrunk then put back to Full if desired.

To view or change the recovery model

1. After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

2. Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

3. Right-click the database, and then click Properties, which opens the Database Properties dialog box.

4. In the Select a page pane, click Options.

5. The current recovery model is displayed in the Recovery model list box.

6. Optionally, to change the recovery model select a different model list. The choices are Full, Bulk-logged, or Simple.

7. Click OK.

Use the SQL window to run the DBCC ShrinkFile(<logfile>) command to shrink the file

Right click on the database name, select Files (on the left) and set the default size to be slightly larger than the database

Change the Recovery mode back to Full or leave it as Simple.

NOTE: The risk of using Simple mode is that you will not have debugging information for SQL - however, from a practical point of view, if you have regular backups it is VERY unlikely that Debug information would be of any use in a SharePoint environment.

Just in case you need more help - see the following:

Method 1:

Method 2:

Method 3:

No comments: