Tag Archives: database

Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter.

Problem

You configure SQL Server Maintenance Plan and set it to e-mail you when the job is complete. When you check the job history, you notice that it fails to send the e-mail and the following message is logged:

Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter.

Solution

Configure your Database Mail settings.

  • Open SQL Server Management Studio
  • Connect to your SQL instance -> Management
  • Right click on Database Mail -> Configure Database Mail -> Next
  • Select the option to ‘Set up Database Mail by performing the following tasks:’
  • Type a Profile Name and description
  • Under SMTP accounts, click Add
  • Type an Account Name and Description
  • Enter an e-mail address to send from:
  • Enter a display name to send from:
  • Enter your Reply-email address (optional)
  • Enter your SMTP server name
  • Select Windows Authentication using Database Engine service credentials (If you are using Windows authentication). Otherwise, select the option that suits your environment.
  • Click OK to save your settings
  • Under Public profile, set your newly created profile to both Public and default.
  • Click Next and finish the wizard.
  • Now right click on Database Mail again and select Send Test E-mail. Send an e-mail to yourself to ensure that your e-mail notifications are working properly.

 

 

 

 

 

 

 

Comments ( 0 )

Remote Blob Storage Maintainer Configuration

Requirement

When you implement Remote Blob Storage for SharePoint 2010, you also need to configure the RBS Maintainer as a scheduled task on your SharePoint database server in order to periodically clean the blob stores from orphaned objects (files that have been permanently removed from SharePoint do not get automatically deleted from the blob store!).

Solution

Step 1 – Install the Remote Blob Storage tool from Microsoft on your SQL Server

You may download the tool from http://www.microsoft.com/en-us/download/details.aspx?id=26728

Assuming that you have already configured RBS on your SharePoint farm, you will need to run the following command to install the tool.

Open an administrator Command Prompt and type the following in:

msiexec /qn /lvx* rbs_install_log.txt /i RBS_x64.msi DBNAME=”WSS_Content” DBINSTANCE=”DBInstanceName” ADDLOCAL=Client,Docs,Maintainer,ServerScript,FilestreamClient,FilestreamServer

Replace WSS_Content and DBInstanceName to suit your environment as required.

Check the rbs_install_log.txt file and look for the line at near the bottom of the file ‘Product: SQL Server 2008 R2 Remote Blob Store — Installation completed successfully.’

Step 2 – Configure the RBS Maintainer

Assuming you have installed the 64 bit version of the tool, browse to C:\Program Files\Microsoft SQL Remote Blob Storage 10.50\Maintainer and type the following commands:

Note that you only need to do the following if you have configured RBS on more than one database. The first RBS database should already exist in the file and its default ConnectionStringName is RBSMaintainerConnection

Decrypt the configuration file using ASPNET_REGIIS

rename Microsoft.Data.SqlRemoteBlobs.Maintainer.exe.config web.config
c:\windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -pdf connectionStrings .

*Note that the word connectionStrings is case sensitive.

Add the connection strings to all your RBS enabled databases

Add your connection strings following the same format as the existing one. MAke sure you add it under <connectionStrings>here</connectionStrings>

Encrypt the configuration file using ASPNET_REGIIS

c:\windows\Microsoft.NET\Framework\v2.0.50727\-pef connectionStrings . -prov DataProtectionConfigurationProvider
rename web.config Microsoft.Data.SqlRemoteBlobs.Maintainer.exe.config

Step 3 – Set the RBS Maintainer to run as a scheduled task

Go to Start -> Administrative Tools -> Task Scheduler

Create a new Basic Task called ‘RBS Maintenance’ and give it a useful description.

Under actions -> Set it to ‘Start a program’.

Browse to “C:\Program Files\Microsoft SQL Remote Blob Storage 10.50\Maintainer\Microsoft.Data.SqlRemoteBlobs.Maintainer.exe”

Add arguments (optional): -ConnectionStringName RBSMaintainerConnection -Operation GarbageCollection ConsistencyCheck -GarbageCollectionPhases rdo -ConsistencyCheckMode r

Click OK to Save

Set the task to run however often you like, it is preferable to run the RBS Maintainer after-hours.

Testing

The default Garbage collection period is 30 days, this means that only objects that have been orphaned for more than 30 days will be deleted by the RBS Maintainer. For your testing purposes, you may wish to reconfigure this to delete immediately, but be sure to change it back to 30 days once you have finished testing.

To set Garbage collection to delete immediately:

exec mssqlrbs.rbs_sp_set_config_value 'garbage_collection_time_window', 'time 00:00:00';
exec mssqlrbs.rbs_sp_set_config_value 'delete_scan_period', 'time 00:00:00';
exec mssqlrbs.rbs_sp_set_config_value 'orphan_scan_period', 'time 00:00:00';

To set it back to 30 days:

exec mssqlrbs.rbs_sp_set_config_value 'garbage_collection_time_window', 'days 30';
exec mssqlrbs.rbs_sp_set_config_value 'delete_scan_period', 'days 30';
exec mssqlrbs.rbs_sp_set_config_value 'orphan_scan_period', 'days 30';

Note that you must delete the files from the End User Recycle bin for RBS to recognise that the file has actually been deleted.

Another thing to take note of is that the blob files only get deleted after 2 log backups and 2 checkpoints on the database. This is when the garbage collector deems that the files are no longer needed. To test this:

  • Backup your database log file
  • Type the command ‘checkpoint’ -> Execute
  • Backup your database log file
  • Type the command ‘checkpoint’ -> Execute

Browse to your blob store, the blobs should now be deleted from the file system.

Comments ( 1 )

Expired sessions are not being deleted from the ASP.NET Session State database.

Problem

This problem usually happens when you move your SharePoint farm to a different database server using SQL Aliases. When you create the Session State Database in SharePoint, it creates a SQL Agent job called <DB Name>_Job_DeleteExpiredSessions. This job runs every minute to clean out the expired sessions from the database. Without it, your database will fill up very quickly and eventually consume all the disk space on your database server.

Solution

You can either re-create the Session State Database using SharePoint Management Shell or you can script to job and re-create it on your new database server.

Method #1

Run SharePoint Management Studio

Disable-SPStateServiceDatabase

Enable-SPSessionStateService -DatabaseName <String> [-DatabaseServer <String>]

Get-SPSessionStateService

You should see your newly created Session State Service Application on the new database server.

Method #2

Login to your old database server and run SQL Management Studio

Expand SQL Server Agent -> Jobs

Locate the job <Session State Database Name>_Job_DeleteExpiredSessions

Right click -> Script Job as -> Create To -> Clipboard

Login to your new database server and run SQL Management Studio

Click on New Query -> Paste

You should see something like:

SE [msdb]
GO
/****** Object:  Job [SessionStateServiceDB_Job_DeleteExpiredSessions]    Script Date: 01/08/2013 14:56:17 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
..

Click on Execute, this will create the job under the SQL Server Agent.

Right click on SQL Server Agent -> Refresh

Expand Jobs and you should see that the job has copied to your new database server.

Right click on it and check the properties / schedule, etc. All settings should be exactly the same as on the old database server.

 

Comments ( 1 )

Fixed: DPM could not enumerate sql server instances using windows management instrumentation

Problem

We are using DPM 2012 to backup our SharePoint 2010 farm using the SharePoint connector in DPM (not SQL backups). I ran the configuresharepoint.exe -enablesharepointprotection followed by configuresharepoint.exe -resolveallsqlaliases (we are using SQL aliases) on one of our Web Front End Servers as required. I then verified that the SharePoint VSS Writer service was Started and running under the account I had specified using configuresharepoint.exe.

I then proceeded to add the server to our SharePoint Protection Group. When I clicked on server to select the SharePoint farm, DPM threw up the following error:

DPM could not enumerate SQL Server instances using Windows Management Instrumentation on the protected computer dbserver.example.com

We are running Windows Server 2008 R2 and SQL Server 2008 R2 as our database server.

Solution

In order to fix the error:

  1. Login to your database server
  2. Open Command Prompt and browse to C:\Program Files (x86)\Microsoft SQL Server\100\Shared
  3. Run the command: mofcomp sqlmgmproviderxpsp2up.mof

You should see the following message:

Microsoft (R) MOF Compiler Version 6.2.9200.16398
Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
Parsing MOF file: sqlmgmproviderxpsp2up.mof
MOF file has been successfully parsed
Storing data in the repository…
Done!

Now go back to your DPM server and try to add your SharePoint farm. The error should no longer appear.

 

Comments ( 2 )

SQL – Repairing a corrupted index in a database

Problem

We recently moved all our SharePoint databases to a new server. This morning I noticed that the new SQL server was reporting that one of the databases had a corrupted index.

Log Name:      Application
Source:        MSSQL$SP
Date:          2/01/2013 9:05:02 AM
Event ID:      8646
Task Category: Server
Level:         Error
Keywords:      Classic
User:          domain\user
Computer:      db01.server.com
Description:
Unable to find index entry in index ID 3, of table 1618104805, in database ‘Profile DB’. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.
Event Xml:

Running a dbcc checkdb on the database showed that there were many consistency errors in one of the table indexes.

Solution

To repair the database, I used the following T-SQL script. This script will set your database to single user mode, run the repair, and set is back to multi-user mode.

Open SQL Management Studio

Click on New Query and paste the following script, changing ‘db name’ to the name of your database.

Use [db name]
Alter Database [db name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
dbcc checkdb('db name', repair_rebuild)
go
Alter Database [db name] SET MULTI_USER

Once the script completes successfully, run a dbcc checkdb on the database again to see if there are any more errors.

Comments ( 2 )