Sitecore Clean Up Databases Tool Fails

We recently ran into an issue with a custom agent that we wrote for a client.  The agents job is to automatically sync images in a network location with those in the Sitecore media library.  The agent runs on a set schedule and was working great until the DBAs noticed that our Sitecore databases were consuming massive amounts of disk space.  Upon further examination, both the 'master' and 'web' databases were in excess of 700 GB.

The Problem

The master and web databases were growing at an exponential rate and the times of growth were tied to when our custom agent was processing.  We were quickly running out of disk space on our database servers.  I looked at the size of the images contained in the sync folder and knew that it should only be about 10GB.  It looked like Sitecore was maintaining old blob records.  I knew that Sitecore provided a "Clean Up Databases" (Sitecore 6.6) utility under "Control Panel\Database" to help with exactly this type of problem.

"Clean Up Databases" allows you to select the database(s) you would like to 'clean' and performs the following actions: (thanks to @DanSolovay and http://bit.ly/1fMv2mF)

1. Removes items that have parents, but the parents are not in the item tree.
2. Removes invalid language data.
3. Removes fields for non existing items.
4. Removes orphaned items.
5. Removes unused blob records.
6. Removes fields from orphaned items removed in step 4.
7. Rebuilds the Descendants table (which stores parent/child relationships).
8. Clears all caches.

I was interested in was removing the unused blob records that were apparently sticking around.  I ran the utility and let it go.  It ran and ran and ran.  I continued to check on the utility and could see that the size of the blobs table was slowing returning to a 'normal' size.  After 6 hours, I saw that an exception was thrown and that SQL Server then backed-out of the transaction, returning the database to the pre-clean up size.  After opening up the logs, I could see that the error was:

Exception: System.Data.SqlClient.SqlException

Message: The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

It turns out I was trying to delete so much data that the utility used up all the available locks for SQL Server.

The Solution

I figured that I needed some way to remove the unused blobs in smaller batches so that we could acquire the locks and then release them as data is deleted.  I contacted Sitecore Support to come up with a way to do just that.  With their insight into what was happening behind the scenes, we came up with a script that can be run manually to clean up the blobs table.

DECLARE @UsableBlobs table(
    ID uniqueidentifier
    );

INSERT INTO 
    @UsableBlobs    
select convert(uniqueidentifier,[Value]) as EmpID from [Fields]
where [Value] != '' 
and (FieldId='{40E50ED9-BA07-4702-992E-A912738D32DC}' or FieldId='{DBBE7D99-1388-4357-BB34-AD71EDF18ED3}') 
delete top (XXXXX) from [Blobs] 
where [BlobId] not in (select * from @UsableBlobs)

Using a temp table, the script looks for all valid blobs and stores them.  It then finds all the blobs not in the temp table and deletes them.  Simply replace the "XXXXX" in order to control how many records are deleted at once.  I started with one record to prove the concept and then went to 1000 and eventually was deleting 200k records at once with no issues.

I was able to clean up the databases manually and recover the space for the database servers.  We modified our scheduling of the agent to run less frequently and additionally created a 'clean-up' agent that ensures that we don't run into this issue again.

It should be noted that this script shouldn't replace the "Clean Up Databases" utility, but can be used in cases where things have already gotten out of hand.  When possible, use the built-in Sitecore utilities to manage the databases.

Also note: Please make backups of your data before running any scripts you find on the Internet.

Additional Reading

My Stack Overflow question regarding this issue: http://bit.ly/1dp3109

Dan Solovay's Stack Overflow answer about the "Clean Up Databases" Utility: http://bit.ly/1fMv2mF

Creating an HTTP Referrer Condition

Having the ability to tailor the site experience to your users is one of the major selling points of Sitecore.  One way to customize this user experience is to change content based on the HTTP referrer.

You can see Sitecore's out-of-the-box conditions in the content tree under "\sitecore\System\Settings\Rules\Conditional Renderings\Conditions".  While Sitecore has a folder called "Referrers" here that contains conditions that involve the referrer, there is not one included that compares the name of the referrer. I was surprised to see that Sitecore didn't already have a built-in condition for this, but it doesn't matter too much as Sitecore is easily extensible.  Let's create a new condition to check against.

Creating the Condition

Creating our new condition involves two steps:

1. Writing code that can check the referrer.
2. Wiring up the condition to our code that we created in step #1.

We need to write the code for the condition.  Either create a new solution or open up an existing solution that you would like to add your condition to.  I have opened a 'common' solution that contains common code that can be used across multiple sites.

using Sitecore.Rules;
using Sitecore.Rules.Conditions;
using System.Web;

namespace Client.Common.Conditions
{
    public class HttpReferrerContains : WhenCondition where T : RuleContext
    {
        public string HttpReferrer { get; set; }

        protected override bool Execute(T ruleContext)
        {
            if (!string.IsNullOrEmpty(HttpReferrer))
            {
                if (HttpContext.Current.Request.UrlReferrer != null && HttpContext.Current.Request.UrlReferrer.ToString().ToLower().Contains (HttpReferrer.ToLower()))
                {
                    return true;
                }
            }
            return false;
        }
    }
}

So what's going on here?  We are creating a new class that implements "WhenCondition" and "RuleContext."  We specify a public string (HttpReferrer) that will be passed into this class from Sitecore.  We override the base "Execute" method so that we can perform our own check to see if the referrer matches the value that we are looking for as passed in from Sitecore.  If the current UrlReferrer matches the value that we passed in from Sitecore, we return a "true" and we return "false" otherwise.

Now we move on to wiring up the condition inside of Sitecore.  Let's go down to the "\sitecore\System\Settings\Rules\Conditional Renderings\Conditions" folder mentioned earlier.  Right-click on the "Referrers" subfolder and insert a new condition.

The two fields we are interested in here are "Text" and "Type."  The "Text" field is where you specify how the condition will appear to the content author.

The first portion of this field is the text that is displayed to the content author.  Inside the square brackets, the field consists of 4 parameters:

1. 1st parameter: Name of the parameter being passed into your custom code.
2. 2nd parameter: Name of a macro item.  Here, you can choose an item from "\sitecore\System\Settings\Rules\Common\Macros" and have that macro pop up when the content author clicks the 'clickable' word.
3. 3rd parameter: Path passed to the macro parameter. (2nd parameter)
4. 4th parameter: The 'clickable' word.

Our example generates the following box:

When the content author clicks on "specific value", they are prompted to enter a value to compare.  Also note that "where" is clickable as well and can be switched to "except where."

In the "Type" field, we add a reference to the assembly we created/added to in step #1.

And that's it!  Now, when you go to personalize a component on the page, you can do so based on the HTTP referrer.

Additional Reading

See how Pieter created a condition and used a slightly different approach where you store the referrers as Sitecore items in the content tree as opposed to allowing free-text entry into the condition by the content author: http://newguid.net/sitecore/2011/sitecore-rules-engine-how-to-create-a-custom-condition/ 

Publishing Your XML Sitemaps to Your Content Delivery Servers

My most-recent post talked about how you can utilize the Sitecore Sitemap XML module in order to automatically build robots.txt and XML sitemap files in a mulit-site, hardened environment so that the search engines can see your sitemap(s).  This works great, but a new issue arises if you have separate Content Managment (CM) and Content Delivery (CD) servers.

The Problem

When your content is published, the Sitemap XML module waits for the "publish:end" event to start building the robots.txt and XML sitemap(s).  It does so and stores the files in the file system.  The issue with this is that the processing and creation of files all happens on the CM server, which is behind a firewall.  The CD servers are the web-facing servers that will be serving the sitemap and robots.txt files.  We need the files on the CD servers.

The Solution

I went through a number of iterations in my head about how I could get the robots.txt and XML sitemap files to the CD servers.  I thought about scheduled jobs in Sitecore.  I thought about using an external application that is used for file replication.  I thought about a custom application inside of Sitecore so that a content author could manually kick off a deploy.

After running some of these ideas by a fellow Arke employee and Sitecore MVP, Andy Uzick, I came to realize that I was over thinking my solution by far.  This sort of problem had been solved long ago and is *WAY* simpler than I had originally thought.

The solution is to change the "SitemapXML.config" to subscribe to the "publish:end:remote" event instead of the "publish:end" event.  This event fires after a successful publish and let's remote servers perform the actions specified.  In our case, this lets the SitemapXML module know to build the robots.txt and sitemap(s) files.  Just ensure that you have the SitemapXML.DLL and SitemapXML.config files present on all your CD servers and you're good-to-go!

Very simple solution and now I don't have to manually copy files between servers!

Using the Sitemap XML Module in a Hardened, Multi-site Environment

We are using the Sitecore Sitemap XML module (2.0) for one of our clients.  Well, we're using it for a number of our clients, but let's just talk about the one client for now.

The module is pretty neat in that it generates a robots.txt and sitemap.xml files for your sites automatically on publish.  It also has the ability to automatically submit the generated sitemap(s) to a number of specified search engines.

The Problem

The module allows for multi-site instances through the configuration file.  You simply specify the Sitecore sites that should be included and the module will generate the necessary robots and sitemap file entries.  So if the module is already allowing for multi-site instances, what's the problem then?  The problem is that if you have followed the security hardening guide for your Sitecore environment, (you did, didn't you??)  you have prevented Sitecore from serving XML files.  The search engine crawlers are expecting the sitemap in an XML format and since Sitecore has been hardened, it refuses to serve the sitemap files.

The Solution

What we need to do is allow Sitecore to serve the sitemap XML files, but not serve up any other XML files that we want to keep secure. (license.xml file comes to mind. . . )

The first step is to tell sitecore to ignore requests to the sitemap files.  I have a naming convention in place in which all sitemaps on the client's instance are configured as "sitemap_<site_name>.xml."  With this standard in place, I updated "IgnoreUrlPrefixes" setting in the web.config as follows:

<setting name="IgnoreUrlPrefixes" value="/sitecore/default.aspx|/trace.axd|/webresource.axd|...|/sitemap_*.xml" />

This let's Sitecore know that it should not try to resolve to an item in the content tree if a request comes in for an XML sitemap.

The second step is to allow Sitecore to serve the XML file.  My client is using IIS 7, so I updated the handlers in the "system.webServer" node in the web.config.  I added a handler that only lets the sitemap XML files be served:

<add verb="GET" path="sitemap_*.xml" type="System.Web.StaticFileHandler" name="allow xml sitemap" />

I now have a multi-site solution that will automatically generate and manage the robots.txt and XML sitemaps that can be properly indexed by the search engines while still remaining secure.

Editing DMS Reports to be Multi-Site Friendly

My client is using a single instance of Sitecore with multiple sites.  While reveiwing Sitecore's built-in reports for DMS, I noticed something that was going to make it difficult to produce reports that could be broken down by site.

Specifically, when looking at the "Latest Visits" report and when drilling down to the "Visit Detail (Session)" report, I noticed that I can't differentiate between the separate sites.  I would only see the visits as "/home" or "/contact-us."  It is impossible to know which site on the instance the visitor visited.

The Solution

In order to see which site was visited, we need to edit the default report provided by Sitecore.  In this example, we'll look at the "Visit Detail (Session)" report.  This can be accomplished in two steps:

1. Edit the SQL Query for the report
2. Edit the Report

Edit the SQL Query for the report

The SQL query for the "Visit Detail" report is located at \sitecore\System\Settings\Analytics\Reports SQL Queries\Visit Pages.  We need to update this query to include the site that was visited.  Luckily, DMS logs this information and stores it in the "Visits" table, in the "MultiSite" column.  We can add this "MultiSite" column to our query and once updated, the (SQL) query should look like this:

SELECT [Duration], [Url], [PageId], [PageId] as [PageIdObject], [Visits].[MultiSite] as [Site]
FROM [Pages] INNER JOIN [Visits] ON [Pages].[VisitorId] = [Visits].[VisitorId] AND [Pages].[VisitId] = [Visits].[VisitId]
WHERE 
  [Visits].[VisitId] = @VisitId
ORDER BY [DateTime], [VisitPageIndex]

I've given my "MultiSite" column an alias of "Site" for clarity.

Note: Even if you're not using Oracle, you might as well update the Oracle script as well in case a database switch is made later down the road:

SELECT "DURATION", "URL", "PAGEID", "PAGEID" as "PAGEIDOBJECT", "VISITS"."MULTISITE" as "SITE"
FROM "PAGES" INNER JOIN "VISITS" ON "PAGES"."VISITORID" = "VISITS"."VISITORID" AND "PAGES"."VISITID" = "VISITS"."VISITID"
WHERE 
  "VISITS"."VISITID" = :VisitId
ORDER BY "DATETIME", "VISITPAGEINDEX"

Edit the Report

Now that the report query has been updated to include which site was visited, we need to update the report to display this information.  While viewing the report, you can click the "Design" button in order to open the Stimulsoft Reports Designer application to edit the report.  I'm sure this might work well, but I wasn't able to get it to work correctly.  I instead went straight to the ".mrt" file in order to edit the report.  The mrt file for the "Visit Detail" report is located at \Website\sitecore\shell\Applications\Analytics\VisitDetail.mrt.  Open it up in your favorite text editor and you will see that it's just XML.

I searched in this file for where the other columns of the report query were being used.  I found the list of columns that were added and I added an additional column for my 'site' column:

<value>Site,System.String</value>

I also incremented the 'count' value for the columns.  The complete section looks like this:

<VisitPages Ref="5" type="Stimulsoft.Report.Dictionary.StiSqlSource" isKey="true">
        <Alias>VisitPages</Alias>
        <Columns isList="true" count="8">
          <value>Url,System.String</value>
          <value>Duration,System.Int32</value>
          <value>Hour,Hour,System.Int32,VisitPages.Duration_x002F_3600</value>
          <value>Minutes,Minutes,System.Int32,VisitPages.Duration_x002F_60</value>
          <value>PageId,System.Guid</value>
          <value>PageIdObject,System.Object</value>
          <value>DurationTimespan,DurationTimespan,System.TimeSpan,new_x0020_TimeSpan_x0028_0_x002C__x0020_0_x002C__x0020_0_x002C__x0020_0_x002C_VisitPages.Duration_x0029_</value>
          <value>Site,System.String</value>
        </Columns>
        <CommandTimeout>0</CommandTimeout>
        <Dictionary isRef="1" />
        <Name>VisitPages</Name>
        <NameInSource>DataConnection</NameInSource>
        <Parameters isList="true" count="1">
          <value>_x0040_VisitId,,14,0</value>
        </Parameters>
        <SqlCommand />
      </VisitPages>

This allows the report to make use of the field, but we now need to add the field to the report.  I then searched for where the text was being built for displaying the page visited.  I found this and added the site name in:

<Text>{string.IsNullOrEmpty(VisitPages.Url)|| VisitPages.Url == "/" ? VisitPages.Site + "/default.aspx" : VisitPages.Site + VisitPages.Url}</Text>

The complete section looks like this:

            <Text21 Ref="58" type="Text" isKey="true">
              <Brush>Transparent</Brush>
              <ClientRectangle>4.83,0,13.97,0.4</ClientRectangle>
              <ComponentStyle>GeneralText</ComponentStyle>
              <Conditions isList="true" count="0" />
              <Font>Arial,10</Font>
              <Margins>0,0,0,0</Margins>
              <Name>Text21</Name>
              <Page isRef="11" />
              <Parent isRef="55" />
              <Text>{string.IsNullOrEmpty(VisitPages.Url)|| VisitPages.Url == "/" ? VisitPages.Site + "/default.aspx" : VisitPages.Site + VisitPages.Url}</Text>
              <TextBrush>Black</TextBrush>
              <TextOptions>HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20,</TextOptions>
              <Type>Expression</Type>
            </Text21>

The Results

Now, when drilling down from the "Latest Visits" report to the "Visit Detail" report, we can see that the (redacted) site name is now present on our report:

Creating a GeoIP Lookup Provider for Sitecore using MaxMind’s GeoLiteCity Database

Sitecore ships with a built-in GeoIP lookup provider that is configured to use MaxMind’s lookup service. This service is provided for free with a “number of free lookups for testing and implementation convenience” (Engagement Analytics 6.6 Configuration Reference) This service works great, but what happens when your ‘number of free’ lookups expires?

GeoIP Options

When your free intro period expires, you essentially have two options if you want to continue to perform GeoIP lookups.

1. Migrate to the MaxMind-Sitecore integrated service. (http://www.maxmind.com/en/sitecore) 
2. Write your own, custom lookup provider.

While the MaxMind-Sitecore integrated service is amazingly simple to implement and run, I wanted to see what else could be used.  

The provider is essentially a ‘black box’ to Sitecore.  It doesn’t matter if it uses a web service, a local database or even a local file to return data lookups.  I decided to try to integrate with a local version of the GeoLiteCity database that is available for free download and use from MaxMind. (http://dev.maxmind.com/geoip/legacy/geolite/)

Creating a new Lookup Provider

Creating a new provider is as simple as creating a class that overrides the “GetInformationByIp” method.  

All we need to do is map the path to the database:

string geoLitePath = Settings.GetSetting(ModuleName + ".DatabaseLocation.GeoLiteCity", "~/app_data/GeoLiteCity.dat");

Pass the IP address to the look up service:

var lookUpService = new LookupService(HostingEnvironment.MapPath(geoLitePath), LookupService.GEOIP_STANDARD);

var location = lookUpService.getLocation(ip);

and populate the “WhoIsInformation” object so that Sitecore can use it:

WhoIsInformation information = new WhoIsInformation();
...
if (location != null)
{
    information.Country = location.countryCode ?? string.Empty;
    information.Region = location.regionName ?? string.Empty;
    ...
}

return information;

Update the Config

After you have created your new provider, update the “Sitecore/lookupManager/Providers” node in the web.config to include a reference to it.  It’s best to do this through a patch include file.

      
        geoLite
        
          />
        
      

And that’s it!  Sitecore makes it easy to change your GeoIP provider to anything else you may want to use. 

You can find complete source code and everything necessary to run this solution in the Sitecore Marketplace.

404 Error in ECM When Creating One Time Message

The Problem

Recently, while working on a Sitecore site with ECM 2.1, I ran into an issue in a single environment where I was receiving a 404 error when trying to create a new "One Time Message."

All other environments (DEV, STAGE) were working with no issues.  The page being requested, (/emailcampaign/create/ad hoc messages) was there, just like in the other environments.

The Solution

After troubleshooting for a while, the problem was found in a patch include file for the SitemapXML module.  This module was only being used in Production and that is why the other environments were working.  The module updates the "encodeNameReplacements" node to replace spaces with dashes in an attempt to help with SEO.  This was intercepting the URL request from ECM, modifying it and this prevented Sitecore from finding the requested URL.  To correct this, I simply commented-out the offending configuration:

    <!--<encodeNameReplacements>
      <replace mode="on" find=" " replaceWith="-" />
    </encodeNameReplacements>-->

Watch out, those include files, they will get you.  Use the "admin/showconfig.aspx" page to really be sure what is being included without you realizing it.