SharePoint DB an Overview

by Balaji Kandasamy

The SharePoint heart is SQL it acts as the brain behind the scenes. SharePoint installed on a server edition OS uses the Internal Database by default. You can manually configure SharePoint to pick the SQL server on Configuration. To check the instances running on the machine you can use the SQL Configuration manager.

 

The above picture shows two instances Internal Database and SQL Express.

Use SQL management studio for easier access. Use the instance to connect to appropriate instance and expand the databases to view the db attached to the particular instance

The default configured would have three db’s by default

 
_AdminContent  (Central Admin)

    The central administration db will be generally one per farm.

 
Wss_Content (Site Content)

    The content database is created one by default that can hold 15000 sites

This can be created separately for each application created in the farm to have control over the growth of the db.

Find the content db in your farm

Central Admin >>Application management >> Content Databases

 
Wss_Search (Search Index)

This is only applicable if only search index is enabled for the farm.

Note: The default database created will have the following settings auto growth (db would grow as the data increases) and no size limit for the db file. This applies for both the .MDF(SQL Database) and .LDF(SQL Log)

The procedure that can avoid the situations. The following are my personal view and take necessary backup before you proceed.

Pre - Action
  1. Connect to instance with Management studio
  2. Right-Click the db properties

3.Select the Files and you can see two files the db and the log

4.Click the button in the Autogrowth button and you will find the following window.

5. Set the Restricted file growth as plan and that size should be greater than the existing file.

 

Post – Action

The Pre-action would help once a new server is setup. The main case is everyone miss this step and finally once log grows huge they search around for an solution. Check this might help. The below is my view and take precautions before giving a try.

  1. Stop SharePoint services and IIS
  2. Connect to the SQL Instance
  3. Expand the Databases item
  4. select the db right click >>Tasks >> Detach
  5. Rename the log file of the detached db
  6. Attach the db manually using the following query in SQKlMGmt
    7. sp_attach_single_file_db @dbname= 'databasename', @physname= 'C:\Databases\databasefile.mdf'

7. Start SharePoint services and IIS that generates a new log file.