Unknown SQL exceptions - Event 5586 (SharePoint 2010 Products)

 

Applies to: SharePoint Server 2010, SharePoint Foundation 2010

Alert Name:   Unknown SQL exceptions

Event ID:   5586

Summary:   Microsoft SharePoint Foundation uses Microsoft SQL Server 2008 databases to store configuration settings and most of the content for the Web site. For example, all pages in the site, files in document libraries, files attached to lists, and information in lists are stored in the content database, and security and permission settings along with other configuration settings are stored in the configuration database in SQL Server 2008.

Microsoft SharePoint Foundation uses a service account to communicate with the database on behalf of a user request. This service account can be either a specific user name or password (domain name and password), or a predefined system account, such as Local System or Network Service. When a SQL Server database is created, a value for the maximum database size is set. Each database has a separate database size setting. Note that a Web application might be associated with one or many databases.

Symptoms:   The following symptom might appear:

  • This event appears in the event log: Event ID: 5586 Description: Database full error on SQL Server instance <instance name> in database <database name>. Additional error information from SQL Server is included below. <SQL error message>.

    Note

    The description changes depending on the SQL Error code.

Cause:   One or more of the following might be the cause:

  1. Insufficient SQL Server database permissions

  2. SQL Server database is full

  3. Incorrect MDAC version

  4. SQL Server database not found

  5. Incorrect version of SQL Server

  6. SQL Server collation is not supported

  7. Database is read-only

Note

You must be a member of the Farm Administrators SharePoint group to perform the following tasks.

Resolution:   Grant correct permissions to the database access account

  • To resolve this issue, assign the database access account and then verify the account has correct permission in SQL Server.

    To assign the database access account:

    1. On the SharePoint Central Administration Web site, click Security, and in the General Security section click Configure Service Accounts.

    2. On the Configure Service Accounts page, in the Credential Management section, select the correct Web application pool for your Web application.

    3. In the Select an account for this component section, select the domain account that you want to associate with this Web application pool, or click Register new managed account to associate a new domain account with this application pool.

    4. Click OK to save changes.

    To verify that the account has correct permission in SQL Server:

    1. Connect to the computer on which SQL Server runs by using an account with administrator permissions.

    2. In SQL Server Management Studio, in the Object Explorer navigation pane, expand the Security node, and then expand the Logins node. The name of the database access account indications that it is a SQL logon account; for example, ##MS_PolicyTsqlExecutionLogin##.

    3. If the account exists, expand the Databases node, expand the Security node, and then click Roles.

    4. Expand the Database Roles node, right-click db_owner, and select Properties.

    5. In the Database Roles Properties dialog box, check whether the database access account is in the Members of this role list. If the account is not listed, click Add.

Note

You must be a member of the Farm Administrators SharePoint group to perform the following tasks.

Resolution:   Increase the size of the SQL Server database

  • SharePoint Foundation cannot write to a database that has reached its maximum size. One solution is to increase the maximum size setting for the database that is full, which is named in the event message.

    To increase the database size:

    1. In SQL Server Management Studio, in the Object Explorer navigation pane, expand the server node, expand the Databases node, right-click the database you want, and then click Properties. The database name is provided in the event message.

    2. In the Properties dialog box, in the navigation pane, click Files.

    3. In the Database files dialog box, in the Autogrowth column, click the ellipsis for the database file.

    4. In the Change Autogrowth dialog box, under Maximum File Size, if the Restricted File Growth (MB) option is selected, increase the maximum file size in the box to the right. You can also configure the database to grow without restrictions by selecting the Unrestricted File Growth option.

    5. Click OK to save changes.

Note

You must be a member of the Farm Administrators SharePoint group to perform the following tasks.

Resolution:   Restore database

  • If the SQL Server database is not present or accessible on the computer that is running SQL Server, restore the database from a backup and reconnect it to SharePoint Foundation 2010.

    To restore the database from a backup:

    1. Copy the database backup to the SQL Server host.

    2. Restore the database by using the RESTORE SQL Server command. For more information about the RESTORE command, see https://msdn.microsoft.com/en-us/library/ms186858(SQL.90).aspx.

    To reconnect the database in Central Administration:

    1. On the Central Administration page, click Application Management and in the Databases section, click Manage content databases.

    2. On the Manage Content Databases page, click Add a content database and in the Web Application section select the Web application.

    3. In the Database Name and Authentication section, type the name of the server in the Database Server text box and the database name in the Database Name text box.

    4. Click OK to save the changes.

Resolution:   Install correct SQL Server version

  • The computer that hosts the database server role must have Microsoft SQL Server 2005 Service Pack 3 Cumulative Update (CU) 3 or SQL Server 2008 Service Pack 1 CU 2 installed. You can either install or upgrade the server to the correct version of SQL Server.

Note

You must have db_owner permissions to the database to perform the following task.

Resolution:   Select the correct SQL Server collation

  1. Connect to the computer on which SQL Server is running by using an account with db_owner permissions to the database.

  2. In SQL Server Management Studio, in the Object Explorer navigation pane, expand the Databases node. Right-click the specific database that was indicated in event 4972, and then click Properties.

  3. On the General tab, the collation is listed in the Maintenance section.

  4. To change the collation, open the Options page.

  5. Select the correct collation from the Collation box.

Resolution:   Change database to Read / Write

  • Change the database so that it can be both read from and written to (read/write) by performing the following steps to increase the size of the database.

    Note

    You must have db_owner access to the database to perform this action.

    To increase the size of the database:

    1. In SQL Server Management Studio, in the Object Explorer navigation pane, expand the server node, and then expand the Databases node.

    2. Right-click the database you want, and then click Properties. The database name is provided in the event message.

    3. In the Database Properties dialog box, in the navigation pane, click Files.

    4. In the Database files section, in the Autogrowth column, click the ellipsis for the database file.

    5. In the Change Autogrowth dialog box, in the Maximum File Size section, if the Restricted File Growth option is selected, increase the maximum file size in the box to the right. You can also configure the database to grow without restrictions by selecting the Unrestricted File Growth option.

    6. Click OK to save changes.

Verify that any database issues have been resolved

  1. In the SharePoint Management Shell, run the Windows PowerShell command Get-SPSite | Format-Table -Property ID,WebApplication,ContentDatabase to obtain a list of the sites for each Web application to list all sites in the various databases, and locate one site in each database.

  2. Browse to the site.